Forum Discussion

RickL's avatar
RickL
Frequent Visitor
8 years ago
Solved

Split several columns at once into rows

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_Et...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    RickL
    Frequent Visitor
    8 years ago

    Thanks Lydia