Forum Discussion

Regular Visitor

1 year ago

Solved

HELP WITH PIVOTING MULTIPLE COLUMNS

Hello, I have some mock data below containing made-up contact details for clients. Clients can have more than one email, mobile or Alternative Phone number. In Power Query the data looks like this:

I would like to transform the data so that each PERSONID and all of their contact details is shown across only one row, like this:

Is anyone able to tell me how to do this within PQ please?

Many thanks in advance for any help 🙂

Mark

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:)

v-dineshya
1 year ago
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
pivot.pbix22 KB

7 Replies

MasonMA

Super User

1 year ago

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:)

AlienSx

Super User

1 year ago

let
    Source = your_data, 
    columns = List.Buffer(Table.ColumnNames(Source)),
    fx_lists = List.Transform(
        List.Skip(columns), 
        (name) => {name, (x as table) as list => List.RemoveNulls(List.Distinct(Table.Column(x, name)))}),
    group = Table.Buffer(Table.Group(Source, List.First(columns), fx_lists)),
    acc = List.Accumulate(
        List.Skip(columns), 
        group, 
        (s, c) => Table.SplitColumn(s, c, (x) => x, List.Max(List.Transform(Table.Column(s, c), List.Count))))
in
    acc

v-dineshya
Community Support
1 year ago
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
pivot.pbix22 KB
- v-dineshya
  Community Support
  1 year ago
  Hi markdean ,
  
  We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
  
  Regards,
  
  Dinesh
  - v-dineshya
    Community Support
    1 year ago
    Hi @markdean ,
    
    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
    
    Regards,
    
    Dinesh
SundarRaj
Super User
1 year ago
Hi markdean,
Subject: Help with Pivoting Columns

Here's a PQ solution you could look at, and I'll also attach the link of the file for your reference,

https://docs.google.com/spreadsheets/d/18tNEYjJm29OPybtXPk8IMc2yB_fn12FC/edit?usp=sharing&ouid=106315737148035640011&rtpof=true&sd=true

Forum Discussion

HELP WITH PIVOTING MULTIPLE COLUMNS

7 Replies

Related content

How to pivot Multiple Columns

Pivot multiple columns

Pivot with Multiple Columns?

Pivoting Multiple Columns More Efficiently

Pivot or Unpivot Multiple Columns