Forum Discussion

Kpham's avatar
Kpham
Resolver I
6 years ago
Solved

Fill down by group

Dear All,   Anybody can give me advise how I could fill down a value based on a group?      
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Kpham ,

     

    I created the test data.

    Sort the two columns first, then group them and fill them down.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUVKK1UFjGGJhgRlJWBlGWFhgRjJWhjFWoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column1", Order.Ascending}, {"Column2", Order.Descending}}),
    #"Group" = Table.Group(#"Sorted Rows", {"Column1"}, {{"All_Rows", each Table.FillDown(_,{"Column2"}), type table}}),
    #"Expanded All_Rows" = Table.ExpandTableColumn(Group, "All_Rows", {"Column1", "Column2"}, {"All_Rows.Column1", "All_Rows.Column2"})
in
    #"Expanded All_Rows"

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Fowmy's avatar
Fowmy
Super User
6 years ago

Kpham 

Sort Ascending on Delivery

Sort Decending on Refernce Doc.

Fill donw on Refernce Doc.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

Kpham's avatar
Kpham
Resolver I
6 years ago

unfortunately some delivery doesnt have a reference doc. So i will create not existing combinations

 

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi Kpham ,

     

    I created the test data.

    Sort the two columns first, then group them and fill them down.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUVKK1UFjGGJhgRlJWBlGWFhgRjJWhjFWoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column1", Order.Ascending}, {"Column2", Order.Descending}}),
    #"Group" = Table.Group(#"Sorted Rows", {"Column1"}, {{"All_Rows", each Table.FillDown(_,{"Column2"}), type table}}),
    #"Expanded All_Rows" = Table.ExpandTableColumn(Group, "All_Rows", {"Column1", "Column2"}, {"All_Rows.Column1", "All_Rows.Column2"})
in
    #"Expanded All_Rows"

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.