Pivot tables, "Attribute" with duplicates

[Anonymous]

Hello Experts,

I am running into an issue where I have "attribute" column which has Addressses therefore the data is same and hence i cannot convert them into columns like Street, Street.1, City, City.1, State, State.1 and so on.
Can anyone help how can I achieve that... scenario is, an ID can have 2 addresses.

What I am looking to get (as a single row): Id Street City State Postcode Country Street.1 City.1 State.1 Postcode.1 Country.1

Thank you

[Anonymous]

Thank you very much! 
It worked.. 😄
@mahoneypat 
@jennratten 

[mahoneypat · Accepted solution]

Glad to hear it.  Please mark one or both as the solution.

Pat

[mahoneypat]

Here's another way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouKUpNLQEyDI0UfBMz8xSCS5RidSByzpkllUDKL7VcITK/KBsuHlySWJIKkoiECwXkF5ck5jjnp6SCjTI2MUWYkl+aV1IEMig02BHJDJi9BgrORfnFxQpBKegW++QXKzjmpafmpBZj2O3siN1ukNVmuOyOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Attribute = _t, Value = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"AllRows", each _, type table [ID=nullable text, Attribute=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Transposed", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns([AllRows], {"Attribute", "Value"})))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded Transposed" = Table.ExpandTableColumn(#"Removed Columns", "Transposed", {"Street", "City", "State", "PostalCode", "Country", "Street_1", "City_2", "State_3", "PostalCode_4", "Country_5"}, {"Street1", "City1", "State1", "PostalCode1", "Country1", "Street2", "City2", "State2", "PostalCode2", "Country2"})
in
    #"Expanded Transposed"

Pat

[jennratten]

Hello - I think this will do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsgQCJR2l4JKi1NQSIMMUCBQc8yqLIQKxOnAlzpkllSAqIzM5MT0fWSa4JLEkFUh7+iCLBuQXlyTmOOengKQMQQDFtPzSvJIikIGhwY6ohkFdYmhkbKIQkpFZjNMpfqnlCpH5RdnY3OIXidMtRiCAxy2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Attribute = _t, Value = _t]),
    // Get new headers
    #"Grouped Rows1" = Table.Group(Source, {"Attribute"}, {{"Table", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn ( [Table], "Index", 1, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Attribute", "Index"}, {"Attribute", "Index"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Custom", "AttributeNew", each Text.Combine({[Attribute], Text.From([Index], "en-US")}, "."), type text),
    NewColumnNames = #"Inserted Merged Column"[AttributeNew],
    // Pivot and split values
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Attribute", each _, type table [ID=text, Attribute=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(  Table.SelectColumns([Attribute],"Value"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), NewColumnNames)
in
    #"Split Column by Delimiter"