Forum Discussion

IanErler's avatar
IanErler
Regular Visitor
6 years ago
Solved

Google Form Graph

Hello, I started with learning how to use Power BI recently. And I'm having trouble reconstruction a certain graph. I've used google forms for my survey and exported the data to a .xls file. There ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi IanErler,

    It seems like you are trying to expand all text string and get the detail count of each item, right? If this is the case, you can take a look at the following link about the similar requirements:
    Occurrence of value (or) count value(text) in a column 

     

    NEW Table =
VAR _path =
    SUBSTITUTE ( CONCATENATEX ( ALL ( 'Table'[Text] ), [Text], "," ), ",", "|" )
VAR _length =
    PATHLENGTH ( _path )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Text", PATHITEM ( _path, [Value] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _pathtable, [Text] ),
        "Count", COUNTROWS ( FILTER ( _pathtable, [Text] = EARLIER ( [Text] ) ) )
    )

     

    Regards,

    Xiaoxin Sheng

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Since it is multi-select response and you are getting a comma-separated list of the responses, you can use the following steps to split them out into new rows:

 

  • Rename the responses column to "Responses" (not required, just easier than the long title of your question)
  • Add a custom column with this formula: = Text.Split([Responses], ", ")
  • Click on the expand in the header for the new List column you created, and choose expand to new rows
  • Write a simple measure to count the values in that column and make your visual

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • IanErler's avatar
    IanErler
    Regular Visitor
    6 years ago

    Hello mahoneypat,

    Thanks for you help! But I am a noob here. There's a measure that count all the values in a columm? I found a funcion for count individual values, like the one below, and created the 8 measures using it.

    The Response columm wth the new one and the measures

     

    But when I tried to make the visual, it was weird. There was too much space between the bars (that I couldn't reduce), the name of the values were unaligned with the bars, I couldn't define the order. Something went wrong.