Pivot multiple columns +

[Kaso]

Hi there,

i can't solve the following problem..

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

It would be great if someone could help me with this, since i can't find a solution

 

[MarcelBeug]

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.

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"

 

[MarcelBeug]

A dynamic solution, independent of available "Type" values, using List.Accumulate to expand each Record column:

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

 

[Kaso]

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?

 

[MarcelBeug]

That would be my solution in post #3 or you mean something else you need to clarify further for me to understand.

 

[Kaso]

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^^