HELP WITH PIVOTING MULTIPLE COLUMNS

markdean 

Hi, you may use below key steps

Grouped = Table.Group(Source, {"PERSONID"}, {
 
        {"All_Emails", each Text.Combine([EMAIL], ", "), type text}, 
        {"Unique_Emails", each Text.Combine(List.Distinct([EMAIL]), ", "), type text},
        {"All_Mobiles", each Text.Combine([MOBILE], ", "), type text}, 
        {"Unique_Mobiles", each Text.Combine(List.Distinct([MOBILE]), ", "), type text},  
        {"All_OtherPhones", each Text.Combine(List.RemoveNulls([OTHERTEL]), ", "), type text},
        {"Unique_OtherPhones", each Text.Combine(List.Distinct(List.RemoveNulls([OTHERTEL])), ", "), type text},     
        {"Email_Count", each List.Count([EMAIL]), Int64.Type},
        {"Mobile_Count", each List.Count([MOBILE]), Int64.Type},
        {"OtherPhone_Count", each List.Count(List.RemoveNulls([OTHERTEL])), Int64.Type}
 }),
 

 Cleaned = Table.TransformColumns(Grouped, {
        {"All_Emails", each if Text.Trim(_) = "" then null else _, type text},
        {"Unique_Emails", each if Text.Trim(_) = "" then null else _, type text},
        {"All_Mobiles", each if Text.Trim(_) = "" then null else _, type text},
        {"Unique_Mobiles", each if Text.Trim(_) = "" then null else _, type text},
        {"All_OtherPhones", each if Text.Trim(_) = "" then null else _, type text},
        {"Unique_OtherPhones", each if Text.Trim(_) = "" then null else _, type text}
 }),
 
 // Reorder columns for better readability
 Reordered = Table.ReorderColumns(Cleaned, {
        "PERSONID",
        "All_Emails",
        "Unique_Emails",
        "Email_Count",
        "All_Mobiles",
        "Unique_Mobiles",
        "Mobile_Count",
        "All_OtherPhones",
        "Unique_OtherPhones",
        "OtherPhone_Count"
 }),
    #"Removed Columns" = Table.RemoveColumns(Reordered,{"All_Emails", "OtherPhone_Count", "Email_Count", "Mobile_Count", "All_Mobiles", "All_OtherPhones"})
in
    #"Removed Columns"

 to return this result

and then Split the Columns based on your preference. 

Hope it helps:)

Hi markdean ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Created sample data based on your screenshot. please refer below snap.

2. In power query editor,  Click on "New source" --> "Blank Qurey", In advanced editor, remove all the code and place the below M code, in advanced editor.

let

Source = Table.FromRows({
{"50232633", "[email protected]", null, null},
{"50232633", "[email protected]", "01234012345", "01234662378"},
{"50232633", null, null, "0123477777"},
{"50270037", "[email protected]", null, null},
{"50270037", "[email protected]", "01234023467", "01234987123"},
{"50270037", null, null, "01234654321"},
{"50310868", "[email protected]", "01234778899", null},
{"50310868", "[email protected]", null, "01234345123"},
{"50310868", null, null, "01234999555"}
}, {"PERSONID", "EMAIL", "MOBILE", "OTHERTEL"}),

Unpivoted = Table.UnpivotOtherColumns(Source, {"PERSONID"}, "ContactType", "ContactValue"),

Filtered = Table.SelectRows(Unpivoted, each [ContactValue] <> null and Text.Trim([ContactValue]) <> ""),

Grouped = Table.Group(Filtered, {"PERSONID", "ContactType"}, {
{"AllRows", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}
}),

Expanded = Table.Combine(
List.Transform(Grouped[AllRows], each _)
),

Renamed = Table.AddColumn(Expanded, "NewColumn", each [ContactType] & Text.From([Index])),

RemovedCols = Table.RemoveColumns(Renamed, {"ContactType", "Index"}),

Pivoted = Table.Pivot(RemovedCols, List.Distinct(RemovedCols[NewColumn]), "NewColumn", "ContactValue"),

Final = Table.SelectColumns(Pivoted, {"PERSONID"} & List.Sort(List.RemoveItems(Table.ColumnNames(Pivoted), {"PERSONID"})))
in
Final

3. Please refer output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh