cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gabrielrca Frequent Visitor
Frequent Visitor

Google forms data edit.

I made a google forms with lots of multiple choice questions and some checkboxes. The problem is these checkboxes have the "Others..." option, wich means that people can write things in it. I've exported this into a sheets and got it into the Power query.

 

Now, I want PBI to delete (or ignore) this "others..." options and count each checkbox (separeted in comas inside the column) to make the graphics. Can someone help me? I am recent to PBI.

1 ACCEPTED SOLUTION

Accepted Solutions
lc_finance Senior Member
Senior Member

Re: Google forms data edit.

Hi @gabrielrca ,

 

Regarding crossing with other survey questions, yes it's possible! To do that we need to change from a calculated column to a calculated measure.

 

Here is a screenshot

Survey Analysis.png

And here is the formula for the calculated measure instead of calculated column (it's almost the same, with the addition of SELECTEDVALUE)

contar como conheceu = 
VAR currentAnswer = SELECTEDVALUE(Planilha1[Como conheceu])

RETURN 

 SUMX(
     'Respostas ao formulário 1',
    VAR respondentAnswer = 'Respostas ao formulário 1'[Por quais meios você conheceu a Academia Compasso?]
    RETURN
    IF(ISERROR(SEARCH(currentAnswer,respondentAnswer)),0,1)
 )

And you can download the Power BI example from here.

 

Best of luck for analyzing your survey. If you need any more help, do not hesitate contacting me.

 

Regards,

 

LC

Want to learn Power BI and DAX? Check out my blog at www.finance-bi.com

View solution in original post

9 REPLIES 9
lc_finance Senior Member
Senior Member

Re: Google forms data edit.

Hi @gabrielrca ,

 

 

can you send a screenshot of what your table looks like?

gabrielrca Frequent Visitor
Frequent Visitor

Re: Google forms data edit.

Screenshot_1.png

 

The red arrows are pointing to an "Others..." option and the coma division I said before. It's in portuguese. @lc_finance 

lc_finance Senior Member
Senior Member

Re: Google forms data edit.

Hi @gabrielrca ,

 

Do you have a separate column listing all the predefined answers?

That separate column can be used for the count

 

If yes, let me know and i'll propose a solution

 

Luca

gabrielrca Frequent Visitor
Frequent Visitor

Re: Google forms data edit.

I can create one, there are only 8 possible awnsers. Tks. @lc_finance 

lc_finance Senior Member
Senior Member

Re: Google forms data edit.

Hi @gabrielrca ,

 

 

here is the solution: you create a calculated column that counts the number of times an answer is used by a respondent.

Here is the formula for the column:

 

Count = 
VAR currentAnswer = [Answers]

RETURN 

 SUMX(
     'SurveyTable',
    VAR respondentAnswer = [Respondent Answer]
    RETURN
    IF(ISERROR(SEARCH(currentAnswer,respondentAnswer)),0,1)
 )

Here is a screenshot of what it looks like :

 

Count Responses Survey.png

 

And finally, you can download the sample Power BI file from here.

 

Hope this answers your question!

 

LC

Interested in Power BI templates? Check out my blog at www.finance-bi.com

gabrielrca Frequent Visitor
Frequent Visitor

Re: Google forms data edit.

Tks for your help. Now I want to cross this data with other columns values. I have to relate them? How do I do it? 

 

One last thing: I got a huge "Blank" column in my graphic. Do you know why this might have happened? @lc_finance 

lc_finance Senior Member
Senior Member

Re: Google forms data edit.

Hi @gabrielrca ,

 

 

can you explain more about 'cross this data with other column values', what would you like to do?

 

Regarding the blank column in your chart, can you share a sample Power BI file? 

That will make it easier to help you.

 

Regards,

 

LC

www.finance-bi.com

gabrielrca Frequent Visitor
Frequent Visitor

Re: Google forms data edit.

Screenshot_2.png

I want to be able to cross all this data. It means that I want to be able to cross, for example, this "Sim" or "Não" lines with ppl that checked "Emagrecimento" at google forms. Another problem that you will see in my BI sample is that the graphics are, for some reason, inverted. It seems that it is counting how many times it IS NOT apearing. @lc_finance 

 

https://gofile.io/?c=bq7zwb  Link to my BI sample

 

lc_finance Senior Member
Senior Member

Re: Google forms data edit.

Hi @gabrielrca ,

 

Regarding crossing with other survey questions, yes it's possible! To do that we need to change from a calculated column to a calculated measure.

 

Here is a screenshot

Survey Analysis.png

And here is the formula for the calculated measure instead of calculated column (it's almost the same, with the addition of SELECTEDVALUE)

contar como conheceu = 
VAR currentAnswer = SELECTEDVALUE(Planilha1[Como conheceu])

RETURN 

 SUMX(
     'Respostas ao formulário 1',
    VAR respondentAnswer = 'Respostas ao formulário 1'[Por quais meios você conheceu a Academia Compasso?]
    RETURN
    IF(ISERROR(SEARCH(currentAnswer,respondentAnswer)),0,1)
 )

And you can download the Power BI example from here.

 

Best of luck for analyzing your survey. If you need any more help, do not hesitate contacting me.

 

Regards,

 

LC

Want to learn Power BI and DAX? Check out my blog at www.finance-bi.com

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,540)