Custom Column w/ value from Column, Repeating

[TheMacroNoob]

Hello experts,

I've had this issue in the past and I never figured out how to solve it. I have a value that is always going to be in "Column2", Row 2.

How do I create a Custom Column that fetches the value containing "Time Period" from "Column2" and repeats it for all rows in the table? Or, if easier, grabs the value in row 2 of "Column2" and repeats that value for every row in the table?

 

[johnny51981]

Here ya go:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}}),
    #"Added Time Period" = Table.AddColumn(#"Changed Type", "Time Period", each if Text.Contains([Column2],"Time Period", Comparer.OrdinalIgnoreCase) then Text.AfterDelimiter([Column2],"Time Period: ") else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Time Period",{"Time Period"})
in
    #"Filled Down"

 

[TheMacroNoob]

Here ya go:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}}),
    #"Added Time Period" = Table.AddColumn(#"Changed Type", "Time Period", each if Text.Contains([Column2],"Time Period", Comparer.OrdinalIgnoreCase) then Text.AfterDelimiter([Column2],"Time Period: ") else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Time Period",{"Time Period"})
in
    #"Filled Down"

I totally forgot about the FillDown feature!

Unfortunately I am getting this error:

Expression.Error: We cannot convert the value null to type Logical.
Details:
Value=
Type=[Type]

So the filldown breaks after the first null. Any ideas why that might be occurring?

 

[johnny51981]

Oh, I think you might need to include a if the cell starts as null then null. So update it to this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}}),
    #"Added Time Period" = Table.AddColumn(#"Changed Type", "Time Period", each if [Column2] = null then null else if Text.Contains([Column2],"Time Period", Comparer.OrdinalIgnoreCase) then Text.AfterDelimiter([Column2],"Time Period: ") else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Time Period",{"Time Period"})
in
    #"Filled Down"

 

[TheMacroNoob]

Oh, I think you might need to include a if the cell starts as null then null. So update it to this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type text}}),
    #"Added Time Period" = Table.AddColumn(#"Changed Type", "Time Period", each if [Column2] = null then null else if Text.Contains([Column2],"Time Period", Comparer.OrdinalIgnoreCase) then Text.AfterDelimiter([Column2],"Time Period: ") else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Time Period",{"Time Period"})
in
    #"Filled Down"

That works very well, thank you very much for your insight! Super excited to use this moving forward.

Tangent to whoever happens across this in the future:
My next problem was promoting headers after removing top n rows.... Since I wanted to apply transformation to all files within a folder, the column would be named a specific file's date and then break.

To circumvent this, I found a solution that shifts the column down, through magic:

    #"Added Time Period" = Table.AddColumn(Sheet2, "Date", each if [Column2] = null then null else if Text.Contains([Column2],"Time Period: ") then Text.AfterDelimiter([Column2],"- ") else null, type date),
    #"Filled Down" = Table.FillDown(#"Added Time Period",{"Date"}),
    #"Remove Top Rows" = Table.Skip(#"Filled Down", each [Column4] <> "ID"),
    #"Source Without Date" = Table.RemoveColumns(#"Remove Top Rows",{"Date"}),
    ShiftedList = {"Date"} & List.RemoveLastN(Table.Column(#"Remove Top Rows", "Date"),1),
    TableToList = Table.ToColumns(#"Source Without Date") & {ShiftedList},
    TableFromList = Table.FromColumns(TableToList, Table.ColumnNames(#"Source Without Date") & {"Date1"}),
    #"Promoted Headers" = Table.PromoteHeaders(TableFromList, [PromoteAllScalars=true]),

If you happen to know a simpler way this could have been done, great, but otherwise, thanks again for your assistance!