Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Greetings Everyone,
I have currently table like below.
I need a duplicate rows need to be inserted in the table. Steps to obtain those rows.(Feel free to change the method)
1. Filter (Type = Actual)
2. Filter (Date = Max(Date) for Retailer-Product column)
3. Replace Actual as Forecast in Type Column
The result table:
Final Output should merge of both input and intermeadiate table which is shown below.
Note: last date(Type = Actual) will not same for all the Retailer-Product combination.
Please help me way to achieve this in Power BI.
Thanks in advance.
Please help @amitchandak , @lbendlin , @ray_ux , @tamerj1 , @SpartaBI
Solved! Go to Solution.
maybe you can try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc87CoQwFIXhvaQWzLkmUUtnwE2IhYidIPjY/0SmuHlxi0DCf4ov06QGVSn4Q5qoRv1eh/V+ll3N1b8SV4rrxz8brk1eDVcT169/Wq42r46ry1WBuRXNnWjuRTO0iAZENUhk42WMx7mty3WX5DDpIMHDlgah36WD9AdtaRD+oYsG8w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Retailer = _t, Product = _t, Date = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Retailer", type text}}),
Custom1 = Table.SelectRows(#"Changed Type", each [Type]="Actual"),
#"Grouped Rows" = Table.Group(Custom1, {"Retailer", "Product", "Type"}, {{"Date", each List.Max([Date]), type nullable text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Retailer", "Product", "Date", "Type"}),
Custom2 = Table.FromRows(List.Transform(Table.ToRows(#"Reordered Columns"),each List.ReplaceValue(_,"Actual","Forecast",Replacer.ReplaceText
)),Table.ColumnNames(Source))&#"Changed Type"
in
Custom2
pls see the attachment below
Proud to be a Super User!
maybe you can try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc87CoQwFIXhvaQWzLkmUUtnwE2IhYidIPjY/0SmuHlxi0DCf4ov06QGVSn4Q5qoRv1eh/V+ll3N1b8SV4rrxz8brk1eDVcT169/Wq42r46ry1WBuRXNnWjuRTO0iAZENUhk42WMx7mty3WX5DDpIMHDlgah36WD9AdtaRD+oYsG8w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Retailer = _t, Product = _t, Date = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Retailer", type text}}),
Custom1 = Table.SelectRows(#"Changed Type", each [Type]="Actual"),
#"Grouped Rows" = Table.Group(Custom1, {"Retailer", "Product", "Type"}, {{"Date", each List.Max([Date]), type nullable text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Retailer", "Product", "Date", "Type"}),
Custom2 = Table.FromRows(List.Transform(Table.ToRows(#"Reordered Columns"),each List.ReplaceValue(_,"Actual","Forecast",Replacer.ReplaceText
)),Table.ColumnNames(Source))&#"Changed Type"
in
Custom2
pls see the attachment below
Proud to be a Super User!
Greetings,
I loved your solution. It's really awesome.
Could you please let me know where I paste the code in power BI, So that I can replcate in my pbi as well.
To replicate, I tried to understand the custom2 code. I didn't understand that part. I know that's where the magic happens to get the required solution. Could you explain what's happening in the custom2?
you can paste the codes in advaced editor. However, you need to modify the codes.
custom 2 is the last step, which replaced the actual with forecast and combine the new table with the original table.
Proud to be a Super User!
User | Count |
---|---|
85 | |
77 | |
73 | |
70 | |
55 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |