Forum Discussion

irfan_abdrhman's avatar
irfan_abdrhman
Helper II
2 years ago
Solved

Merging certain rows ( Merge Repetitive) and turning rows of another column into multiple columns

Hello Community,

View Sheets Here.

I have an issue here where I have the complete Data of the exported Data which I have merged in the early stage to generate all the information. However, I am unsure how to use the Data in order that the Data will be represented as the sheets on "Ideal -" where Summary will be the overall combined Data accordingly to the "Curve" but the breakdown which are the Revised Budget, Work Completed, Actual Cost, and Forecast Final Cost are accordingly to the "Name". Please Advice. I will share the Screenshots as well.

Raw Data :


Desired Outcome :


Do send a message as well, if I am being unclear about anything. Hope to have a reply asap as this is urgent. Thank you Community!




Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago

    Here is the general approach:

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Merge and turn into column.xlsx"), null, true),
    Table_1_Table = Source{[Item="Table_1",Kind="Table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Table_1_Table,{"Curve", "Periods"}),
    #"Parsed JSON" = Table.TransformColumns(#"Removed Other Columns",{{"Periods", Json.Document}}),
    #"Expanded Periods" = Table.ExpandRecordColumn(#"Parsed JSON", "Periods", {"2023-07-01", "2023-08-01", "2023-09-01", "2023-10-01", "2023-11-01", "2023-12-01", "2023-02-01"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Periods", {"Curve"}, "Period", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Period", type date}, {"Value", Currency.Type}})
in
    #"Changed Type"

     

    Adjust as needed.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Here is the general approach:

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Merge and turn into column.xlsx"), null, true),
    Table_1_Table = Source{[Item="Table_1",Kind="Table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Table_1_Table,{"Curve", "Periods"}),
    #"Parsed JSON" = Table.TransformColumns(#"Removed Other Columns",{{"Periods", Json.Document}}),
    #"Expanded Periods" = Table.ExpandRecordColumn(#"Parsed JSON", "Periods", {"2023-07-01", "2023-08-01", "2023-09-01", "2023-10-01", "2023-11-01", "2023-12-01", "2023-02-01"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Periods", {"Curve"}, "Period", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Period", type date}, {"Value", Currency.Type}})
in
    #"Changed Type"

     

    Adjust as needed.

  • irfan_abdrhman's avatar
    irfan_abdrhman
    Helper II
    2 years ago

    Sorry to bother lbendlin , it does work but it has duplicated data, to avoid this issue, I want the result to have the Number and Name columns as well. Hope this is possible, thank you! But it does come up interesting similar to what i wanted but at the result if it can generate the mapped Number and Name would be perfect. Please do update if this is possible and how to do this, I appreciate your assistance in this matter.