Splitting a column from a PowerBI formula

[ManchevB]

Hi all, 

I have this formula here:

7.SkillAvailabilityByCountry =

VAR AllCountries = VALUES('Employees'[CountryName])

RETURN

    ADDCOLUMNS (

        'Skills Data',

        "Country",

            CONCATENATEX (

                CALCULATETABLE (

                    VALUES('Employees'[CountryName]),

                    'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])

                ),

                'Employees'[CountryName],

                ", "

            ),

        "CountriesWithoutSkill",

            CONCATENATEX (

                EXCEPT (

                    AllCountries,

                    CALCULATETABLE (

                        VALUES('Employees'[CountryName]),

                        'Skills Data'[SkillName] = EARLIER('Skills Data'[SkillName])

                    )

                ),

                'Employees'[CountryName],

                ", "

            )

    )

that generates me this table:

What I am looking to do is split the column CountriesWithoutSkill in a new column in the table, so I get each of the country that don't have the respective skill in a separate row.

I'm afraid Transform Data wouldn't work here as the whole table was generated by a formula. 

Any help would be greatly appreciated.

Thank you!

[rohit1991]

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"
)

[KieranPatrickWo]

I agree with @Fowmy . DAX should not be used for ETL or calculations, DAX should only be used for over-ridding the filter context, e.g. time intelligence.

[Fowmy · Accepted solution]

@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

[speedramps]

We want to help but please copy and paste sql data into a text table or hand type the data and share it.
You can also share data via OneDrive, Gooledrice or Dropbox.

Dont expect busy helpers to hand type your test data for you. 🙄

We can then immediately import teh data and build a solution.

I appreciate you might be busy and want a rush answer, but we are busy volunteers  😀

You will get quicker and better answers if you take care and effort into asking questions with example data. 👍

[speedramps]

We want to help you but your description is too vague. Please write it again clearly.

Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That is a bit crazy. ‌‌
Also DON'T proudly explain what you have done ok, just explain the problem, Otherwisie it is confusing,

Please DO give a simple non technical functional description of what you want, then let us suggest the solution. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confusion.

Rename columns to user friendly names. Avoid your business or system jargon that we may not understand.
 
Also provide the example desired output, with a clear step-by-step description of calculations the process flow.
Remember not to share private data ... we don't want you to get into trouble. ‌‌
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions with example data.

Look forward to helping you when the above information is forthcoming 👍