splitting a column with answers from multiplechoice question

[Nelleke-NL]

Hey I am an older Dutch woman and do not speak English. But I'm sure it will work through translation.
In power BI I have a file with data. One column gives the results of a multiple choice question. For example 1;2;5 or 3;5;7 etc.
I can't find how to split that. I want totals of 1, 2, etc. in my report. Does anyone have a link or description for me?

 Updating Media

[Nelleke-NL]

sorry not the correct reply

[Ashish_Mathur]

Hi,

If it is me you are replying to, then you should not get multiple columns at all.  You should get multiple rows instead.  Anyways, you should be able to use the "Unpivot Other Columns" feature.

Hope this helps.

[Nelleke-NL]

Sorry, multiple rows is not working for me at this moment. Then I have to create a seperat table.

But I'm still working on the best solutions for me.

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the column and select Split Column > By delimiter > Semi colon.  Expand Advanced and select Rows.  Click on OK.

Hope this helps.

[v-yueyunzh-msft]

Hi , @Nelleke-NL 

According to your description, you have one column  like this "1;2;4 or 3;4;5;6;7", and you want to split them and get the total count of them , RIght?

Here are the steps you can refer to :
(1)This is my test data:

(2)We can put this M code in the "Advanced Editor" in Power Query Editor to refert to :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjBU0lEytDayNrY2sTZVitUBChmBhUytzSBcYyDXCMI0ATGRlZoi6wZpiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Page = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Page", type text}, {"Column1", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{"Column1",(x)=>   List.Count(Text.Split(x,";")) }     )
in
    Custom1

We just need to use the Text.Split() function and if you want to get the count add the List.Count() function to it and we can meet your need:

For more information, you can refer to :

Text.Split - PowerQuery M | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Nelleke-NL]

I finally understand what I see in your example. You count the number of answers, so row 1 is 5 answers. But I mean, how many time, how much coubts of answer 1 3 times so 3 in report, answer 2 count is 4, answer 3 count is 3, answer 4 count is 3.

[v-yueyunzh-msft]

Hi, @Nelleke-NL 

Thanks for your quick response ! Do you mean you want to get the appearance count of the answer. Right?

Here are the steps you can refer to :

(1)My test data is the same as above.

The test data is this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjBU0lEytDayNrY2sTZVitUBChmBhUytzSBcYyDXCMI0ATGRlZoi6wZpiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Page = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Page", type text}, {"Column1", type text}})
in
    #"Changed Type"

(2)We can create a blank query and enter this:

let
    Source = Table.FromColumns({List.Distinct(List.Combine(List.Transform(Query1[Column1],(x)=> Text.Split(x,";"))))}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", (x)=>List.Count(List.Select(List.Combine(List.Transform(Query1[Column1],(x)=> Text.Split(x,";"))),(y)=>y=x[Column1]))              )
in
    #"Added Custom"

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Nelleke-NL]

I can't figure it out, my version is Dutch. I'm trying to adding an example from the original file. When I download the original file,  I can choose answers in text or the serial number of the answer. How I get this sample here? I have a pbix I get response the file type pbix is not supported

let
Bron = Excel.Workbook(File.Contents("D:\Nelleke\Documents\Downloads\dossiers.xlsx"), null, true),
Export_Sheet = Bron{[Item="Export",Kind="Sheet"]}[Data],
#"Type gewijzigd" = Table.TransformColumnTypes(Export_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Headers met verhoogd niveau" = Table.PromoteHeaders(#"Type gewijzigd", [PromoteAllScalars=true]),
#"Type gewijzigd1" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Naam", type text}, {"Type", type text}}),
#"Kolom gedupliceerd" = Table.DuplicateColumn(#"Type gewijzigd1", "Type", "Type - Kopie"),
#"Kolom splitsen op scheidingsteken" = Table.SplitColumn(#"Kolom gedupliceerd", "Type - Kopie", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Type - Kopie.1", "Type - Kopie.2", "Type - Kopie.3", "Type - Kopie.4"}),
#"Type gewijzigd2" = Table.TransformColumnTypes(#"Kolom splitsen op scheidingsteken",{{"Type - Kopie.1", type text}, {"Type - Kopie.2", type text}, {"Type - Kopie.3", type text}, {"Type - Kopie.4", type text}})
in
#"Type gewijzigd2"

It's in dutch... What is the best and easy way to share my report?

[v-yueyunzh-msft · Accepted solution]

Hi, @Nelleke-NL 

Thanks for your quick response!

According to your screenshot, you split the column to many columns. For your need , i think teh besy way is not like this.

This is my test data:

(1)We do not split to more columns just one , we can click "Custom Column" and enter:

(2)Then we can remove the [Type] column:

(3)Then we can "expand to new rows":

(4)Then we can apply the data  to the Power Bi Desktop, then we can put the field on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Nelleke-NL]

Thanks I'm so happy that now I have fixed it. This is exact the solution. I did not understand in first sight but now it's working!  Loved it.😀👌

[djurecicK2]

Hi @Nelleke-NL , 

 Please try:

https://learn.microsoft.com/nl-nl/power-query/split-columns-delimiter