Transform and aggregate data

[Anonymous]

Hi all

This one has me stumped. What I want to do, is create a table with a customer case number, the customer's name and their forms of contact (phone, email and such like). It isn't working and it's because of the structure of my data, which looks like the below.

My case numbers are unique, my customer IDs are unique and everything relates well in PowerBI, but the way the data is formatted in table 3 is stuffing things up. 

What I want is for each customerID to be its own row, with the contact types to be columns of their own., like the below, if possible.

The problem I have is that there are numerous customers with multiples of the same contact type, like 117 John A in my example above, so pivoting gives me errors. Short of asking my IT team to fix the format of the data set, is there anything I can do in PowerBI to fix it?

(Also, I should point out that the real tables are just under a million rows large.)

[Anonymous]

Hi @Anonymous ,

You can refer to following sample to transform your query table based on grouping and splitcolumn functions:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0V9JRCsjIz0sF0qampoZGxiZKsTrYZUyRZFxzEzNzgHRWfkZeokM6iKeXnJ8LVWGBqtfM3MISKmOJrDcxN7U42SEjvwRNOxZFaDYYGaDaYGxiagaVMUSVMbC0MMcuY2IMFEHIwC3MzE11yC8tycnPz4ZYGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Contact Type" = _t, Detail = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"Contact Type", type text}, {"Detail", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer ID"}, {{"Contents", each Table.Group(Table.RemoveColumns(_,{"Customer ID"}), {"Contact Type"}, {{"Content", each Text.Combine(_[Detail],","), type table}}), type table}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Contact Type", "Content"}, {"Contact Type", "Contents"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Contents", List.Distinct(#"Expanded Contents"[#"Contact Type"]), "Contact Type", "Contents"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Phone", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Phone1", "Phone2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Email", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Email1", "Email2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Phone1", Int64.Type}, {"Phone2", Int64.Type}, {"Email1", type text}, {"Email2", type text}})
in
    #"Changed Type1"

Regards,

Xiaoxin Sheng