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"
=#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!
