Forum Discussion

Jayaselvan's avatar
Jayaselvan
Icon for Helper II rankHelper II
4 years ago
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 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 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    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

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Jayaselvan
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        4 years ago

        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.