Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Transform and aggregate data

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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