Forum Discussion
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 excel file
My data looks like this:
| DRIVER # | SITE | VEHICLE-1 | VEHICLE-1 % ALLOC | VEHICLE-2 | VEHICLE-2 % ALLOC | VEHICLE-3 | VEHICLE-3 % ALLOC | VEHICLE-4 | VEHICLE-4 % ALLOC | VEHICLE-5 | VEHICLE-5 % ALLOC |
| 101 | Construction Site 1 | Truck 1 | 1 | ||||||||
| 102 | Construction Site 1 | Truck 1 | 0.5 | Truck 2 | 0.4 | Car 1 | 0.1 | ||||
| 103 | Construction Site 2 | Truck 1 | 0.4 | Excavator 1 | 0.5 | Excavator 2 | 0.1 | ||||
| 104 | HQ | Car 1 | 0.1 | Car 2 | 0.1 | Car 3 | 0.1 | Pickup 1 | 0.4 | Pickup 2 | 0.3 |
| 105 | Construction Site 2 | Excavator 2 | 0.8 | Pickup 2 | 0.2 |
Columns have equipment and their cost allocation and I want to have my equipments to be in a row with each the driver and the allocation:
| Vehicule | Driver | Allocation |
| Truck 1 | 101 | 1 |
| Truck 1 | 102 | 0.5 |
| Truck 1 | 103 | 0.4 |
| Truck 2 | 102 | 0.4 |
| Excavator 1 | 103 | 0.5 |
| Excavator 2 | 103 | 0.1 |
| Excavator 2 | 105 | 0.8 |
| Car 1 | 102 | 0.1 |
| Car 1 | 104 | 0.1 |
| Car 2 | 104 | 0.1 |
| Car 3 | 104 | 0.01 |
| Pickup 1 | 104 | 0.4 |
| Pickup 2 | 104 | 0.3 |
| Pickup 2 | 105 | 0.2 |
Thanks in advance for your help
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"
5 Replies
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.
=#table({"Vehicle","Driver","Allocation"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(List.Skip(List.RemoveLastN(_,each _=null),2),2),(x,y)=>{x{0}}&y))
Thank you. I'll try and get back to you!
