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

[mahmoud]

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!

[MarcelBeug · Accepted solution]

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

[Phil_Seamark]

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

[mahmoud]

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

[MarcelBeug]

@mahmoud did you overlook my post?

[mahmoud]

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

Tried to use the index column as values column

[MarcelBeug · Accepted solution]

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

 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:

[Bepachec]

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. 

[mahmoud]

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

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

Best Regards

Mahmoud

[MarcelBeug · Accepted solution]

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