Forum Discussion
Helper IISplitting a column from a PowerBI formula
Hi all,
I have this formula here:
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!
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
5 Replies
- Fowmy
Super User
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 
speedrampsWe 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 👍
- 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. 👍
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.
rohit1991Hi 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" )
