Forum Discussion

ManchevB's avatar
ManchevB
Icon for Helper II rankHelper II
1 year ago
Solved

Splitting a column from a PowerBI formula

Hi all,    I have this formula here:   7.SkillAvailabilityByCountry = VAR AllCountries = VALUES('Employees'[CountryName]) RETURN     ADDCOLUMNS (         'Skills Data',         "Country", ...
Need Help
  • Fowmy's avatar
    Fowmy
    1 year ago

    ManchevB 

    You may try genreating the above table and spliting the column CountriesWithoutSkill into rows using Power Query:

    let
  AllCountries = List.Distinct(Employees[CountryName]),
  Source = #"Skills Data",
  AddCountry = Table.AddColumn(
    Source,
    "Country",
    each
      let
        skill     = [SkillName],
        countries = List.Distinct(Table.SelectRows(Employees, (r) => r[SkillName] = skill)[CountryName])
      in
        Text.Combine(List.Sort(countries), ", ")
  ),
  AddCountriesWithoutSkill = Table.AddColumn(
    AddCountry,
    "CountriesWithoutSkill",
    each
      let
        selected = if [Country] = "" then {} else Text.Split([Country], ", "),
        diff     = List.Difference(AllCountries, selected)
      in
        Text.Combine(List.Sort(diff), ", ")
  ),
  TransformColumn = Table.TransformColumns(AddCountriesWithoutSkill, {{"CountriesWithoutSkill", each Text.Split(_, ", "), type list}}),
  ExpandCountriesWithoutSkill = Table.ExpandListColumn(TransformColumn, "CountriesWithoutSkill")
in
  ExpandCountriesWithoutSkill
rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

Hi ManchevB,

 

The best way to split your "CountriesWithoutSkill" column into separate rows is by using Power Query rather than DAX. Power Query is designed for exactly these kinds of data transformations, and it keeps your data model clean and efficient.

 

Here’s how you can do it: In Power Query, select your "CountriesWithoutSkill" column. Go to the “Home” tab, click on “Split Column” > “By Delimiter.” Choose comma , as the delimiter. Under Advanced Options, select “Split into Rows.” Click OK and you’re done each country will now appear in its own row, with other columns duplicated as needed.


While it’s technically possible to split strings with DAX (using GENERATE, SELECTCOLUMNS, and some creative workarounds), it’s not recommended. DAX isn’t built for row-level data transformations or ETL. Using Power Query here is much simpler, more reliable, and keeps your reports fast and maintainable.

 

If you want to do this with M code instead, here’s a sample (replace Source with your previous step):

 

SplitCountries = Table.ExpandListColumn(
    Table.TransformColumns(
        Source,
        {{"CountriesWithoutSkill", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), each List.Transform(_, Text.Trim)}}
    ),
    "CountriesWithoutSkill"
)