Forum Discussion
Solved
Duplicate rows in Power Query
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 t...
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 Custom2pls see the attachment below
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
Custom2pls see the attachment below
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.
