topic Re: Help with transpose/unpivot in Power Query

Help with transpose/unpivot

zsidane — Thu, 01 Sep 2022 21:17:59 GMT

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

Re: Help with transpose/unpivot

artemus — Thu, 01 Sep 2022 23:34:30 GMT

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"

Re: Help with transpose/unpivot

wdx223_Daniel — Fri, 02 Sep 2022 00:31:15 GMT

=#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))

Re: Help with transpose/unpivot

zsidane — Fri, 02 Sep 2022 16:30:50 GMT

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.

Re: Help with transpose/unpivot

zsidane — Fri, 02 Sep 2022 16:31:50 GMT

Thank you. I'll try and get back to you!

Re: Help with transpose/unpivot

artemus — Fri, 02 Sep 2022 19:17:17 GMT

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.