Forum Discussion
Row transform nested values in one column and their corresponding nested values in another column
Faced with the challenge of transforming the following input table using Power Query.
The expected, output table is listed below.
What would be the steps to achieve such transformation.
Thank you
Raw input dat table to import into Power BI
| Departments | Cities | Regions | Offices | Countries |
| Sales | New Deli|Madrid|Brussels | National Capital Region,Castilla Leon,Brussels Capital Region | 1,2,3 | India,Spain,Belgium |
| Marketing | San Francisco|Phoenix|Boulder|Boston|Paris | CA,AZ,CO,MA,Île-de-France | a,b,c,d,e | US,US,US,US,FR |
Output Table:
talbi ,
You can use a combination of the following:
Split Columns by Delimiter. For the Cities column you would use "|", and for the others use ",".
Once Split, you can use the Unpivot Columns to get the Columns into Rows.
Then use Filters or other Flags to Remove the unnecessary rows. Unfortunately, ran out of time to complete the entire manipulation, but hoping this at least gets you started.
Have attached pbix what I started.
EDIT: Found a better way. Attached is a revised pbix file. Please refer to PQ (2). Have only done it for Sales, but hope you can see the pattern to replicate the process.
Kind Regards,
1 Reply
talbi ,
You can use a combination of the following:
Split Columns by Delimiter. For the Cities column you would use "|", and for the others use ",".
Once Split, you can use the Unpivot Columns to get the Columns into Rows.
Then use Filters or other Flags to Remove the unnecessary rows. Unfortunately, ran out of time to complete the entire manipulation, but hoping this at least gets you started.
Have attached pbix what I started.
EDIT: Found a better way. Attached is a revised pbix file. Please refer to PQ (2). Have only done it for Sales, but hope you can see the pattern to replicate the process.
Kind Regards,