fill down with a condition on the dynamic column names (powerquery)

Question

Here is my code so far

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id No", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"Date", type datetime}, {"Hour(s)", type number}, {"Activity Code", type text}, {"Activity", type text}, {"Crew", type text}, {"Duty", type text}, {"Subcontractor", type text}, {"Account Group (Description)", type text}, {"Status", type text}, {"Cost Code", type any}, {"Group", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Status] <> "ABSENCE WITHOUT PERMISSION"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Status", "Cost Code", "Group", "Activity Code", "Activity", "Subcontractor", "Account Group (Description)"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}, {"Id No", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type text}}, "en-US")[Date]), "Date", "Hour(s)", List.Sum)
in
    #"Pivoted Column"

i have column names dynamically changing for each month.

what i am trying to do is "to fill down the null rows under the dates which are not Sunday with AB

I can manually add a column for each not sunday manually to do it but next month the dates and so the names and sundays will change.

the sample file is here

*** while writing i had an idea to insert "AB" before pivoting it, i will try...

EDIT

After this idea, here is my new code :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Id No", "Name", "Surname", "Crew", "Duty", "Date", "Status"}, {{"Sum Hours", each List.Sum([#"Hour(s)"]), type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Custom", each if [Status] = "ABSENCE WITHOUT PERMISSION" then "AB" else [Sum Hours]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "Hour(s)"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Sum Hours", "Status"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Id No", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type text}}, "en-US")[Date]), "Date", "Hour(s)", List.Sum),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Pivoted Column", {{"11/1/2019", "AB"}, {"11/2/2019", "AB"}, {"11/3/2019", "AB"}, {"11/4/2019", "AB"}, {"11/5/2019", "AB"}, {"11/6/2019", "AB"}, {"11/7/2019", "AB"}, {"11/8/2019", "AB"}, {"11/9/2019", "AB"}, {"11/10/2019", "AB"}, {"11/11/2019", "AB"}, {"11/12/2019", "AB"}, {"11/13/2019", "AB"}, {"11/14/2019", "AB"}, {"11/15/2019", "AB"}, {"11/16/2019", "AB"}, {"11/17/2019", "AB"}, {"11/18/2019", "AB"}, {"11/19/2019", "AB"}, {"11/20/2019", "AB"}, {"11/21/2019", "AB"}, {"11/22/2019", "AB"}, {"11/23/2019", "AB"}, {"11/24/2019", "AB"}, {"11/25/2019", "AB"}, {"11/26/2019", "AB"}, {"11/27/2019", "AB"}, {"11/28/2019", "AB"}, {"11/29/2019", "AB"}, {"11/30/2019", "AB"}})
in
    #"Replaced Errors"

but I get error because of "AB" and therefore i have to replace the errors, and the last part of the code comes with static dates on the Table.ReplaceErrorValues

Is there any syntax to write allcolumns instead of the column names one by one ? something like this :

#"Replaced Errors" = Table.ReplaceErrorValues(#"Pivoted Column", {ALLCOLUMNS, "AB"})

FINAL CODE DONE !

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Id No", "Name", "Surname", "Crew", "Duty", "Date", "Status"}, {{"Sum Hours", each List.Sum([#"Hour(s)"]), type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Custom", each if [Status] = "ABSENCE WITHOUT PERMISSION" and Date.DayOfWeekName([Date],"en-US") <> "Sunday" then "AB" else [Sum Hours]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "Hour(s)"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Sum Hours", "Status"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Id No", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type text}}, "en-US")[Date]), "Date", "Hour(s)", List.Sum),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Pivoted Column", 
                      List.Transform(Table.ColumnNames(#"Pivoted Column"), each {_, "AB"}))
in
    #"Replaced Errors"

Aleksei Zhigulin Aleksei Zhigulin · Accepted Answer · 2019-12-04T12:09:22

is there any syntax to write "allcolumns" instead of the column names one by one ?

Yes, sure, you may use Table.ColumnNames function. For your example:

= Table.ReplaceErrorValues(#"Pivoted Column", 
                      List.Transform(Table.ColumnNames(#"Pivoted Column"), each {_, "AB"}))

fill down with a condition on the dynamic column names (powerquery)

1 Answers