Split several columns at once into rows

[RickL]

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

[v-yuezhe-msft · Accepted solution]

@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] &";"&[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

[RickL]

Thanks Lydia