Forum Discussion
Create multiple address rows from a single row that has many addresses
Hi,
I have a data source that has a ton of columns and I am looking to standardise similar groups of columns into a single set that ends up with multiple rows instead. By way of example, for whatever reason, the origin dataset has these columns:
| column | example value |
| BillingStreetAddress1 | 27 Napier Avenue |
| BillingStreetAddress2 | |
| BillingStreetCity | Takapua |
| BillingStreetState | Auckland |
| BillingStreetPostCode | 0622 |
| BillingStreetCountry | NZ |
| ShippingStreetAddress1 | 14 Epson Road |
| ShippingStreetAddress2 | |
| ShippingStreetCity | Torbay |
| ShippingStreetState | Auckland |
| ShippingtreetPostCode | 0630 |
| ShippingStreetCountry | NZ |
I would prefer to represent them just have these columns below and have two rows instead and set the value of address_purpose to either Billing or Shipping:
| address_line1 | address_line2 | city | state | post_code | country | purpose |
| 27 Napier Avenue | Takapuna | Auckland | 0622 | NZ | billing | |
| 14 Epson Road | Torbay | Auckland | 0630 | NZ | shipping |
I am using desktop Power BI and Power Query.
Thanks, Matt
Hi, Anonymous
Start by using custom splitting in Power Query.
Result:
Copy a table and continue in Table 2. Delete Column1 in the image above and use Transpose. Then Use the first row as the header.
Make another copy of the table. Table 2 deletes the last 6 columns, and Table 3 deletes the first 6 columns.
Change the column names to Consistent. Use Append to merge Table 2 and Table 3 into a new table.
Go back to Power Bi Desktop and use Dax to get the purpose column.
Purpose = CALCULATE(MAX('Table'[column.1]),FILTER('Table',[example value]=EARLIER(Append1[Address1])))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Anonymous , Option 1 pivot data in power query
https://radacad.com/pivot-and-unpivot-with-power-bi
or create matrix visual and take column on column
- danextian
Hi Anonymous
This can be done in Power Query by pivoting. Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY+xCsIwFAB/JWTuYKPoXItrEetk6fA0Dw0tSUhehP69raAYEuc7OK7r+F6No9L3lhwiVVI69L7kBRc71oBV6Fj1RB2Q90VeFrOcwlrRNIMzDGADpLwlIJyFKtyGEbRMjaPxVBu5SFshMgUTNLkl0lzetH0oa3Mr5YYdrDeanQzI/+p3JKafE+OuMGV49iRWflfWq1wjeulf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column = _t, #"example value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"column", type text}, {"example value", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "purpose", each if Text.Contains([column], "Billing") then "Billing" else if Text.Contains([column], "Shipping") then "Shipping" else null, type text), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.Replace([column],[purpose],"")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"column"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"purpose", "Custom", "example value"}), #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Custom]), "Custom", "example value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"StreetAddress1", "address_line1"}, {"StreetAddress2", "address_line2"}, {"StreetCity", "City"}, {"StreetState", "State"}, {"StreetPostCode", "post_code"}, {"StreetCountry", "country"}}) in #"Renamed Columns" 
v-zhangtiCommunity Support
Hi, Anonymous
Start by using custom splitting in Power Query.
Result:
Copy a table and continue in Table 2. Delete Column1 in the image above and use Transpose. Then Use the first row as the header.
Make another copy of the table. Table 2 deletes the last 6 columns, and Table 3 deletes the first 6 columns.
Change the column names to Consistent. Use Append to merge Table 2 and Table 3 into a new table.
Go back to Power Bi Desktop and use Dax to get the purpose column.
Purpose = CALCULATE(MAX('Table'[column.1]),FILTER('Table',[example value]=EARLIER(Append1[Address1])))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
