Forum Discussion

joc's avatar
joc
Helper I
9 years ago
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 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

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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

     

     

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    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

     

     

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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"