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"