Google Form Visualization

[Anonymous]

New to Power BI, please help

Hi Everyone,

I made a google form with a dropdown list that connects to the answer's dedicated section. The responses came back with answers of only the intended section filled. The inputs are checkbox (Yes/No/Other) and I'm trying to create a stacked bar chart of the responses count.

I need a way to not hard code the tables since the google form is linked. The data sample, transformed table, and target chart are provided.

Data Sample

Transformed Table (Manual)

Along the way, I would also like to add slicers for the different types of items. If you have a simpler approach to get to the target chart please post it 🙂 

I did use excel and used vlookup and countif, but I can't find a way to replicate them in Power BI.

Thank you for your help.

[Anonymous]

New to Power BI, please help

Hi Everyone,

I made a google form with a dropdown list that connects to the answer's dedicated section. The responses came back with answers of only the intended section filled. The inputs are checkbox (Yes/No/Other) and I'm trying to create a stacked bar chart of the responses count.

I need a way to not hard code the tables since the google form is linked. The data sample, transformed table, and target chart are provided.

Data Sample

Transformed Table (Manual)

Along the way, I would also like to add slicers for the different types of items. If you have a simpler approach to get to the target chart please post it 🙂

I did use excel and used vlookup and countif, but I can't find a way to replicate them in Power BI.

Thank you for your help.

[Stachu]

The transfromation would be somthing like this (you can paste it to Advanced Editor in the Transform Data interface):

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Type"}, "Question", "Answer"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Type", "Question", "Answer"}, {{"Total", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

you just need to replace the Source step with your table

[AllisonKennedy · Accepted solution]

@Anonymous  It might be nice to keep one table that has one row per respondent? See if this webinar on survey data gives you any inspiration: https://www.excelwithallison.com/MCT/news#h.p_NkgcNxVFFYaS

I didn't get too complex in this one, but I do often do what you have done by creating a column for 'question' and column for 'response'. I just think you're missing the Dim and Fact table concept to be able to create exactly the chart you want. https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html 

[Anonymous]

Hi! Thank you for the webinar link! It really helps in creating the charts I want, but I have a question regarding the fact tables. Since the tables are unpivoted for each question,  will the new entries be in the format of the fact tables when I refresh the data? Because the PBIX is directly linked to the survey's Google Spreadsheet.

[AllisonKennedy]

@Anonymous  You're very welcome, glad it helps! 🙂 

Everything you do in Power Query is saved, so since the pbix is linked directly to the Google Spreadsheet, every time you refresh the data, Power BI will follow every step you created in Power Query. It starts by going to the Google Spreadsheet and grabbing all the rows, then transforms, unpivots, references, adds index column, etc. So the final result is yes, the new entries will be in the format of the fact tables when you refresh the data. Such a fun tool! Let me know if you have more questions around that.

[Anonymous]

Thank you so much for the explanation!! I've been reading a lot about Power Query so this really makes sense 🙂 

[Anonymous]

Thank you for the response!!