Replace values with condition

[joc]

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 hope, I'm understandable... It's more easier with tables :

I have this table :

And I would like this table :

Thank in advance for the help,

Joc

[Vvelarde · Accepted solution]

@joc

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

[joc]

Thank you for the help !

[MarcelBeug]

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"