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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nelleke-NL
Helper II
Helper II

splitting a column with answers from multiplechoice question

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

 

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

vyueyunzhmsft_0-1671352974045.png

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

vyueyunzhmsft_1-1671353166222.png

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

vyueyunzhmsft_2-1671353216256.png

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

vyueyunzhmsft_3-1671353247962.png

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

vyueyunzhmsft_4-1671353396583.png

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

View solution in original post

11 REPLIES 11
Nelleke-NL
Helper II
Helper II

sorry not the correct reply

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1670898131968.png

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

vyueyunzhmsft_1-1670898324529.png

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

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.

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:

vyueyunzhmsft_0-1671153617550.png

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:

vyueyunzhmsft_1-1671153644136.png

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

 

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

NellekeNL_0-1671213443646.png

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"

 

NellekeNL_2-1671213973354.png

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

 

 

 

 

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:

vyueyunzhmsft_0-1671352974045.png

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

vyueyunzhmsft_1-1671353166222.png

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

vyueyunzhmsft_2-1671353216256.png

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

vyueyunzhmsft_3-1671353247962.png

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

vyueyunzhmsft_4-1671353396583.png

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

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
Super User
Super User

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors