Forum Discussion
Pivot tables, "Attribute" with duplicates
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
Glad to hear it. Please mark one or both as the solution.
Pat
4 Replies
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"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
Thank you very much!
It worked.. 😄
mahoneypat
jennrattenGlad to hear it. Please mark one or both as the solution.
Pat
