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.

5 Replies

  • 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.

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    Kpham 
    You can fill based on the group this way:

    Step 1 : Group By Delivery


    Step 2 :  Add a Custom Column

    Step - 3: Expand the Column "Count"

    Step- 4:  Kepp only expanded columns and the new column (Custom) and Delete All other Columns


    ________________________

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

    I accept KUDOS 🙂

    YouTube, LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello Fowmy, 

       

      I tried this solution for my problem because I have the same.  I would like just to have the fill down for the column enddate of procument when the project has one. So project 004 & 005 should be empty/blank. 

       

       

      I group my data in this way:

       

      Addig Custom Column I did this way. (Please notice that I didn't wrote the queries again and let it in german languge, furtherrmore the name of "enddate of procurment" is originally "[T08 Enddatum Planung 1]

       

       

      But I cannot extend this custom column. Instead i got an errormessage. 

       

       

      What I am doing wrong?

       

      Thanks in advance.