Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Glad to hear it. Please mark one or both as the solution.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Glad to hear it. Please mark one or both as the solution.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |