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...
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.

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.