Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mahmoud
Helper I
Helper I

Split a cell values in a column to multiple columns by value

Hi dears,

I have a column (as you see in the photo down) named Q, this question can store many values. And these values can be change in other time to have more option ( linke some one may select an anwser for this question other options between 6 to 11).

 

- I would to split these values to columns but each value have to set in same column.  I tried split column but it did not split the specific value to specific column. So how I can fix that?

- I would the new value to creat a new column autmaticly or since I know all options, is it possible to create these columns then if any one selected these values it will go to that column automaticly?. if not, what is the best solution for this scenario?

 

Thanks for your support!

values to columns.png

 

2 ACCEPTED SOLUTIONS

A solution in Power Query would be to:

  • add an Index column,
  • split the Q column in a new column,
  • expand this new column,
  • add a new column with prefix "V",
  • pivot (I included an exotic sort (on the numeric number part of the prefixed value),
    just because it can and because it is Saturday today),
  • sort back to the original sort
  • remove the Index column

This video takes you through all the steps:

 

 

let
    Source = Table1,
    Indexed = Table.AddIndexColumn(Source, "Index", 0, 1),
    Splitted = Table.AddColumn(Indexed, "Splitted", each Text.Split([Q], " ")),
    Expanded = Table.ExpandListColumn(Splitted, "Splitted"),
    Prefixed = Table.AddColumn(Expanded, "Inserted Prefix", each "V" & [Splitted], type text),
    Pivoted = Table.Pivot(Prefixed, 
                          List.Sort(List.Distinct(Prefixed[#"Inserted Prefix"]),
                                    (x,y) => Value.Compare(Number.From(Text.Middle(x,1)),
                                                           Number.From(Text.Middle(y,1)))),
                          "Inserted Prefix",
                          "Splitted"),
    OriginalSort = Table.Sort(Pivoted,{{"Index", Order.Ascending}}),
    RemovedIndex = Table.RemoveColumns(OriginalSort,{"Index"})
in
    RemovedIndex
Specializing in Power Query Formula Language (M)

View solution in original post

"I have 2 questions"  followed by a list of 5...

 

Anyhow, if you have mixed data, then I would suggest to sort the data before pivotting.

 

Now the pivot step looks like:

Split a cell values in a column to multiple columns by value.png

 

 and the entire query code:

let
    Source = Table1,
    Indexed = Table.AddIndexColumn(Source, "Index", 0, 1),
    Splitted = Table.AddColumn(Indexed, "Splitted", each Text.Split([Q], " ")),
    Expanded = Table.ExpandListColumn(Splitted, "Splitted"),
    AddedSortColumn = Table.Buffer(Table.AddColumn(Expanded, "SortColumn", each try Number.From([Splitted]) otherwise [Splitted])),
    Sorted = Table.Sort(AddedSortColumn,{{"SortColumn", Order.Ascending}}),
    RemovedSortColumn = Table.RemoveColumns(Sorted,{"SortColumn"}),
    Prefixed = Table.AddColumn(RemovedSortColumn, "Inserted Prefix", each "V" & [Splitted], type text),
    Pivoted = Table.Pivot(Prefixed, List.Distinct(Prefixed[#"Inserted Prefix"]), "Inserted Prefix", "Splitted"),
    OriginalSort = Table.Sort(Pivoted,{{"Index", Order.Ascending}}),
    RemovedIndex = Table.RemoveColumns(OriginalSort,{"Index"})
in
    RemovedIndex

The solution is dynamic as it adds all required columns, also if future data require more/less columns.

 

 

So with your latest example added, I got the following result with column "Vother"  automatically added:

 

Split a cell values in a column to multiple columns by value - result.png

Specializing in Power Query Formula Language (M)

View solution in original post

8 REPLIES 8
Phil_Seamark
Employee
Employee

Hi @mahmoud

 

How many options will you have?  Your sample data only shows numbers up to 12.  If that is the case, you could hand build DAX formulas to create calculated columns and only populated if it finds a key value in the Q column.

 

Something along the lines of

 

V12 = 
Var myVal = "12"
RETURN IF(FIND(" " & myVal & " "," " & 'Table1'[Q] & " ",,0)>0,myVal,blank())

which you can repeat

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

Thanks for your suggestion, I have 30 questions and each question has many options (as a total options 244). So that means I have to create 244 columns. 🙂

 

The "V" letter used to explain that the options values woulded to be the columns names.

 

I wish there is another way to get that. :), may Power BI add these future to coming release.

 

Best Regards

Mahmoud

@mahmoud did you overlook my post?

Specializing in Power Query Formula Language (M)

Hi @MarcelBeug

Thanks for your help, I am trying your method, I cerate a seprat table as you did it is work.

But when I come to real data, it is stoped at Pivot Column step.

 

It give me an error 

DataFormat.Error: We couldn't convert to Number.
Details:
    3 9 other

 

I have two questions:

- if the column Q has a value such as " 3 9 other" will affect the process, because I am working with text values.

- is it possible to record or show images using Power Query interface (full screen to see if I forget step or did mistakes)

- with this number of questions, I have to repeat the procrss for each column? 

- if my column name has (/) shall i remove this / from all columns names?

- if some selected the option 7 in the future, shall i have to repeate the process or it will automaticly add this new values as a new column?

 

Thanks for your support!

Best Regards

Mahmoud

 

Error.pngTried to use the index column as values columnTried to use the index column as values column

"I have 2 questions"  followed by a list of 5...

 

Anyhow, if you have mixed data, then I would suggest to sort the data before pivotting.

 

Now the pivot step looks like:

Split a cell values in a column to multiple columns by value.png

 

 and the entire query code:

let
    Source = Table1,
    Indexed = Table.AddIndexColumn(Source, "Index", 0, 1),
    Splitted = Table.AddColumn(Indexed, "Splitted", each Text.Split([Q], " ")),
    Expanded = Table.ExpandListColumn(Splitted, "Splitted"),
    AddedSortColumn = Table.Buffer(Table.AddColumn(Expanded, "SortColumn", each try Number.From([Splitted]) otherwise [Splitted])),
    Sorted = Table.Sort(AddedSortColumn,{{"SortColumn", Order.Ascending}}),
    RemovedSortColumn = Table.RemoveColumns(Sorted,{"SortColumn"}),
    Prefixed = Table.AddColumn(RemovedSortColumn, "Inserted Prefix", each "V" & [Splitted], type text),
    Pivoted = Table.Pivot(Prefixed, List.Distinct(Prefixed[#"Inserted Prefix"]), "Inserted Prefix", "Splitted"),
    OriginalSort = Table.Sort(Pivoted,{{"Index", Order.Ascending}}),
    RemovedIndex = Table.RemoveColumns(OriginalSort,{"Index"})
in
    RemovedIndex

The solution is dynamic as it adds all required columns, also if future data require more/less columns.

 

 

So with your latest example added, I got the following result with column "Vother"  automatically added:

 

Split a cell values in a column to multiple columns by value - result.png

Specializing in Power Query Formula Language (M)

Hello there! @MarcelBeug

Thank you so much for your solution. I do have a special question regarding this topic. I have a column "Tags" where each cell going down the column has a long dictionary of items if you will. An example cell would look like: {"Cost Center": "0000", "Application": "PowerBI", "Environment": "DEV"}. However each cell has a different number of tags (one without a cost center, etc). 

 

My goal is to create a Cost Center column with all the values underneath, a Application Column with all the values underneath, etc. 

If it is simpler, I have a list of tag headers that I am looking for specifically to make a specific number of columns as opposed to going through each item in each cell (there is a lot of junk text in some of these). So if there is an easier way to extract just the values I need and create columns I would greatly appreciate it! 

 

I would also like to remove the "" as well. 

@MarcelBeugthank you very much!, I am sorry to type I have two questions then wrote five. Smiley Wink
I was wroting the questions, during that I remombered other scenario but I forget to correct the number of questions Smiley LOL

I will try again to get same results. I will check the values.

 

Best Regards

Mahmoud

 

A solution in Power Query would be to:

  • add an Index column,
  • split the Q column in a new column,
  • expand this new column,
  • add a new column with prefix "V",
  • pivot (I included an exotic sort (on the numeric number part of the prefixed value),
    just because it can and because it is Saturday today),
  • sort back to the original sort
  • remove the Index column

This video takes you through all the steps:

 

 

let
    Source = Table1,
    Indexed = Table.AddIndexColumn(Source, "Index", 0, 1),
    Splitted = Table.AddColumn(Indexed, "Splitted", each Text.Split([Q], " ")),
    Expanded = Table.ExpandListColumn(Splitted, "Splitted"),
    Prefixed = Table.AddColumn(Expanded, "Inserted Prefix", each "V" & [Splitted], type text),
    Pivoted = Table.Pivot(Prefixed, 
                          List.Sort(List.Distinct(Prefixed[#"Inserted Prefix"]),
                                    (x,y) => Value.Compare(Number.From(Text.Middle(x,1)),
                                                           Number.From(Text.Middle(y,1)))),
                          "Inserted Prefix",
                          "Splitted"),
    OriginalSort = Table.Sort(Pivoted,{{"Index", Order.Ascending}}),
    RemovedIndex = Table.RemoveColumns(OriginalSort,{"Index"})
in
    RemovedIndex
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.