Forum Discussion
Solved
Help with transpose/unpivot
Hello everyone, I need help in order to organise data from column to rows using POWER QUERY (Data is in Excel). EDIT: I aattached an excel file as my mess isn't the best to look at 🙂 See ...
This should work for you
let Source = Excel.CurrentWorkbook(), InputTable = Source{[Name="Table1"]}[Content], #"Removed Columns" = Table.RemoveColumns(InputTable,{"Site"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Driver #"}, "Attribute", "Value"), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "VehiculeId", each Text.BeforeDelimiter([Attribute], " "), type text), #"Grouped Rows" = Table.Group(#"Inserted Text Before Delimiter", {"Driver #", "VehiculeId"}, {{"Vehicule", each List.Max([Value]), type any}, {"Allocation", each List.Min([Value]), type any}}), #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"VehiculeId"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Driver #", "Driver"}}) in #"Renamed Columns"
This should work for you
let
Source = Excel.CurrentWorkbook(),
InputTable = Source{[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(InputTable,{"Site"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Driver #"}, "Attribute", "Value"),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "VehiculeId", each Text.BeforeDelimiter([Attribute], " "), type text),
#"Grouped Rows" = Table.Group(#"Inserted Text Before Delimiter", {"Driver #", "VehiculeId"}, {{"Vehicule", each List.Max([Value]), type any}, {"Allocation", each List.Min([Value]), type any}}),
#"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"VehiculeId"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Driver #", "Driver"}})
in
#"Renamed Columns"
Thank you! It worked. Now I have a different problem...
My set of data has around 4100 "drivers" and +50 types of "vehicule". Once I close and load data, the query takes a lot of time (I see the count of rows at +20 million rows...) then it ends up failing.
Also, another question please: What's the best way to put the code in the formula bar in power query? The only way I managed to get it running is by pasting it in the source (1st step). Ideally, I'dd like to do a couple steps before (like removing columns I don't need).
Again, thanks for your help.
I would have to see what the error is to understand why it is failing.
For the formula, you can use the advanced editor and paste it in there.