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