How to properly exploit a split column?

Hi Anonymous ,

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 :

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 :

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!