Duplicate rows in Power Query

[Jayaselvan]

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 , @RayWu , @tamerj1 , @SpartaBI 

[ryan_mayu · Accepted solution]

@Jayaselvan 

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

[Jayaselvan]

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?

[ryan_mayu]

@Jayaselvan 

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.