Forum Discussion
Solved
Replace values with condition
Hello everybody, I need your help again for a problem. IF the rows have the same NAME and MONTH and IF one of thoses rows have a date, I would like all of thoses rows have the same date. I ho...
Hi, You can do it in Query Editor:
Sort By Name (Ascending)
Sort By Month (Ascending)
Sort by Month_V2 (Descending)
Next Step is Select Month_V2 Column and Select Transform - Fill Down
My suggestion would be also a Power Query solution, using Group By:
let
Source = Table1,
#"Grouped Rows" = Table.Group(Source, {"NAME", "MONTH"}, {{"AllData", each Table.FillDown(Table.Sort(_,{"MONTH_V2", Order.Descending}),{"MONTH_V2"}), type table}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"MONTH_V2", "VALUE"}, {"MONTH_V2", "VALUE"})
in
#"Expanded AllData"
If you need to end up with the original sort order (which would slow down the query), you need some additional steps:
let
Source = Table1,
#"Added Index" = Table.AddIndexColumn(Source, "Original Sort", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"NAME", "MONTH"}, {{"AllData", each Table.FillDown(Table.Sort(_,{"MONTH_V2", Order.Descending}),{"MONTH_V2"}), type table}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"MONTH_V2", "VALUE", "Original Sort"}, {"MONTH_V2", "VALUE", "Original Sort"}),
#"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"Original Sort", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Original Sort"})
in
#"Removed Columns"