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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Yacine-Eurovia
Frequent Visitor

How to properly exploit a split column?

Hello everyone,

In my Power BI report, I'm using the excel file from a Microsoft Forms form as a data source.
Some questions have multiple choices, which means the cells in these questions columns look like that:

- Choice 1;Choice 2;Choice 3

- Choice 2;Choice 4;Choice 5

- Choice 1;Choice 2; Choice 4

etc.


So I'm splitting the multiple choice columns in multiple columns, using the ";" character as a delimiter.
Then in my clustered bar graph, I'm adding all the columns in the Y axis, and the answers ID in the X axis.
It looks like it works correctly, except the chart is displaying only the results of the first column. All I can do is diplaying the results of the other columns, one by one, by clicking on that button of the chart :

YacineEurovia_0-1661418605633.png

But what I'm trying to do is to make all the results appear on the chart at the same time.

So what could I change to make that work?

Thanks by advance.

1 ACCEPTED SOLUTION
rohit_singh
Solution Sage
Solution Sage

Hi @Yacine-Eurovia ,

You might need to transform the data before adding it to the chart. Please try the following steps :

1) In Power Query, you will need to unpivot the "question" columns. Copy and paste the code below into a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVLJDcMwDFsl8DtAYh2JjyE6QJAB+mn76P6okdpwIPpHECRFCToO593swkK6EE1eEodEK1JboR6f7/P9mnyugBrg7M75cIS2HSheB0ncgDaw/yMZ/TiF5R55+QRFilTsvq03uAJQLXgV0cEukm1k3WVD/4AKPbJWwRtKBEq5+8gMDqjG9TQOAtgk2bkxrfZ9CnWrUn3e3q6obIVCheGTufP8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Heure de début" = _t, #"Heure de fin" = _t, Question = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Heure de début", type text}, {"Heure de fin", type text}, {"Question", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Question", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Question.1", "Question.2", "Question.3", "Question.4", "Question.5"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Heure de début", "Heure de fin", "ID"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "."), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> ""))
in
    #"Filtered Rows"

Your data should now be in this format :

rohit_singh_0-1661436447279.png

 

2) Load the data into report view. Add the "Value" column to the Y-axis and count of ID to the X axis of a clustered bar chart. You will get something like this :

rohit_singh_1-1661436542613.png

 

This is only for a single question but you could tweak your code slightly to make this work for multiple questions as well.


Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

 

View solution in original post

3 REPLIES 3
rohit_singh
Solution Sage
Solution Sage

Hi @Yacine-Eurovia ,

You might need to transform the data before adding it to the chart. Please try the following steps :

1) In Power Query, you will need to unpivot the "question" columns. Copy and paste the code below into a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVLJDcMwDFsl8DtAYh2JjyE6QJAB+mn76P6okdpwIPpHECRFCToO593swkK6EE1eEodEK1JboR6f7/P9mnyugBrg7M75cIS2HSheB0ncgDaw/yMZ/TiF5R55+QRFilTsvq03uAJQLXgV0cEukm1k3WVD/4AKPbJWwRtKBEq5+8gMDqjG9TQOAtgk2bkxrfZ9CnWrUn3e3q6obIVCheGTufP8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Heure de début" = _t, #"Heure de fin" = _t, Question = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Heure de début", type text}, {"Heure de fin", type text}, {"Question", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Question", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Question.1", "Question.2", "Question.3", "Question.4", "Question.5"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Heure de début", "Heure de fin", "ID"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "."), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> ""))
in
    #"Filtered Rows"

Your data should now be in this format :

rohit_singh_0-1661436447279.png

 

2) Load the data into report view. Add the "Value" column to the Y-axis and count of ID to the X axis of a clustered bar chart. You will get something like this :

rohit_singh_1-1661436542613.png

 

This is only for a single question but you could tweak your code slightly to make this work for multiple questions as well.


Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

 

amitchandak
Super User
Super User

@Yacine-Eurovia , Try to use the next option expand 


Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@amitchandakthe next option isn't wroking neither, it is creating new values out by adding the result of 2, 3, 4...columns. It's complicated to describe but it's not displaying the value "choice 1" on the same column of the chart, whatever column of the dataset it comes from as I wish.

 

I can't send a .xlsx file on this forum, but here's a table like the one I'm trying to exploit :

IDHeure de débutHeure de finQuestion

18/25/22 14:38:208/25/22 14:38:26Option 1;Option 2;Option 3;
28/25/22 14:38:278/25/22 14:38:30Option 1;Option 3;Option 5;Option 7;
38/25/22 14:38:328/25/22 14:38:34Option 1;
48/25/22 14:38:358/25/22 14:38:39Option 6;Option 5;
58/25/22 14:38:408/25/22 14:38:45Option 1;Option 4;Option 6;Option 7;
68/25/22 14:38:468/25/22 14:38:48Option 5;
78/25/22 14:38:498/25/22 14:38:53Option 2;Option 7;
88/25/22 14:38:558/25/22 14:38:59Option 2;Option 3;Option 7;
98/25/22 14:39:018/25/22 14:39:03Option 7;
108/25/22 14:39:058/25/22 14:39:08Option 1;Option 2;

 

So it's all about the last column : I'm trying to make count every "Option" chosen by the user, so each expression appear as one row on the chart, but it's not working.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.