Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
Anybody can give me advise how I could fill down a value based on a group?
Solved! Go to Solution.
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"
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
unfortunately some delivery doesnt have a reference doc. So i will create not existing combinations
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"
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |