Pivot multiple columns +

K

Kaso

New member
Joined
Apr 26, 2017
Messages
24
Reaction score
0
Points
0
Excel Version(s)
2013, 2016, 365
Hi there,

i can't solve the following problem..

I have this table before.JPG and want it to look like this after.JPG only using Power Query.

It would be great if someone could help me with this, since i can't find a solution
 
One approach would be to combine the name, phone number and data updated columns in records, then unpivot and expand the columns with nested records.
Below the steps, based on the example data.
Note that this is based on your example data; if you want a more dynamic solution allowing for more "Type" values, then a more complex solution will be required.
The desired output is not possible due to duplicate column names. I expanded the record columns with the option to keep the original column names. At the end I removed the ".Name" part from the applicable columns.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Building_ID", Int64.Type}, {"Type", type text}, {"Name", type text}, {"Phone number", type text}, {"Data updated", type date}}),
    SelectedColumns1 = Table.SelectColumns(Typed,{"Building_ID", "Type"}),
    Records1 = Table.ToRecords(SelectedColumns1),
    SelectedColumns2 = Table.SelectColumns(Typed,{"Name", "Phone number", "Data updated"}),
    Records2 = Table.ToRecords(SelectedColumns2),
    Tabled = Table.FromColumns({Records1,Records2}),
    ExpandedRecords1 = Table.ExpandRecordColumn(Tabled, "Column1", {"Building_ID", "Type"}, {"Building_ID", "Type"}),
    Pivoted = Table.Pivot(ExpandedRecords1, List.Distinct(ExpandedRecords1[Type]), "Type", "Column2"),
    #"Expanded Landlord" = Table.ExpandRecordColumn(Pivoted, "Landlord", {"Name", "Phone number", "Data updated"}, {"Landlord.Name", "Landlord.Phone number", "Landlord.Data updated"}),
    #"Expanded Renter" = Table.ExpandRecordColumn(#"Expanded Landlord", "Renter", {"Name", "Phone number", "Data updated"}, {"Renter.Name", "Renter.Phone number", "Renter.Data updated"}),
    #"Expanded Facility Manager" = Table.ExpandRecordColumn(#"Expanded Renter", "Facility Manager", {"Name", "Phone number", "Data updated"}, {"Facility Manager.Name", "Facility Manager.Phone number", "Facility Manager.Data updated"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Facility Manager",{{"Landlord.Name", "Landlord"}, {"Renter.Name", "Renter"}, {"Facility Manager.Name", "Facility Manager"}})
in
    #"Renamed Columns"
 
A dynamic solution, independent of available "Type" values, using List.Accumulate to expand each Record column:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Building_ID", Int64.Type}, {"Type", type text}, {"Name", type text}, {"Phone number", type text}, {"Data updated", type date}}),
    ColumnNames = {"Name", "Phone number", "Data updated"},
    SelectedColumns1 = Table.SelectColumns(Typed,{"Building_ID", "Type"}),
    Records1 = Table.ToRecords(SelectedColumns1),
    SelectedColumns2 = Table.SelectColumns(Typed,ColumnNames),
    Records2 = Table.ToRecords(SelectedColumns2),
    Tabled = Table.FromColumns({Records1,Records2}),
    ExpandedRecords1 = Table.ExpandRecordColumn(Tabled, "Column1", {"Building_ID", "Type"}, {"Building_ID", "Type"}),
    Pivoted = Table.Buffer(Table.Pivot(ExpandedRecords1, List.Distinct(ExpandedRecords1[Type]), "Type", "Column2")),
    Types = List.Skip(Table.ColumnNames(Pivoted)),
    ExpandAllRecords = List.Accumulate(Types,Pivoted,(t,n) => Table.ExpandRecordColumn(t,n,ColumnNames,List.Transform(ColumnNames, each n & (if _ = "Name" then "" else "."&_))))
in
    ExpandAllRecords
 
Thank you, you did really help me with that! :)
I do just have one more question.. Do you have an idea how i could make the ColumnNames you defined dynamic, so that new columns would get included?
 
That would be my solution in post #3 or you mean something else you need to clarify further for me to understand.
 
Nevermind i wanted something that i do actually not need.

Sorry if my sentences are not clear enough, english is'nt my native tongue :)
Again thank you for your help and man you've been fast with that^^
 
You must log in or register to reply here.
Back
Top