Split several columns at once into rows

Question

I have a dataset with multiple concatenated fields and I need to split them into rows based on the delimiter (some cases a semicolon, others a comma).

Report format (input)

ID	Aggregated_Ethnicity	Aggregated_Ethnicity_Group
1	New Zealand European/Pakeha	European
2	New Zealand European/Pakeha;New Zealand Maori	European; Maori
3	British/Irish;New Zealand European/Pakeha	European
4	Indian;New Zealand European/Pakeha;Other	Asian; European; Other

Output I want

ID	Aggregated_Ethnicity	Aggregated_Ethnicity_Group
1	New Zealand European/Pakeha	European
2	New Zealand European/Pakeha	European
2	New Zealand Maori	Maori
3	British/Irish	European
3	New Zealand European/Pakeha	European
4	Indian	Asian
4	New Zealand European/Pakeha	European
4	Other	Other

anonymous · Accepted Answer

RickL,Add a cutsom column in your table.
if Text.Contains([Aggregated_Ethnicity], ";")=true and  Text.Contains([Aggregated_Ethnicity_Group], ";")=false then [Aggregated_Ethnicity_Group] &amp;";"&amp;[Aggregated_Ethnicity_Group]else [Aggregated_Ethnicity_Group]
Then duplicate your table, and split columns and merge tables. For more details, please review attached PBIX file.Regards,Lydia

Forum Discussion

Split several columns at once into rows

2 Replies

Related content

Slicer for filtering several columns

Splitting Column values

Split several columns at once

Split a column into two separate columns

Split Columns