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