Smartsheet Form - Data is Horizontal. Help!

[Medmanage101]

After I created my Smartsheet Form (a Yes/No Questionaire with 120 questions), I noticed my data appears horizontally across the page.  Power BI does not know what to do with this information as it counts the number of questions, not the answers.  If I use Excel to transpose the data to "up and down" on the page, Power BI works fine.  Can Power BI change all my data to run vertically on the spreadsheet if the original Smartsheet has horizontal data?

ie.  It looks like this now:    Is red a color?   Yes   Is blue a color?  Yes       

     I need the data to change to:  Is red a color?  Yes

                                                      Is blue a color?  Yes

[parry2k · Accepted solution]

@Medmanage101 In Power Query, select site and object column, right-click, unpivot other columns, and you will attribute and value which you rename further.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Medmanage101]

Here is how Smartsheet Form returns my respones now.  Questions are headers when using FORMs:

I need the data to look like this so POWER BI can count my YES, NO answers for each Object.  Like this:

 Excel does this, but I don't want to transpose data every time.  I'm hoping POWER BI will do it.

[parry2k]

@Medmanage101 as far as I understood, you need to unpivot your data in Power Query and that is what you are looking for.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak]

@Medmanage101 The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can share some screenshots ?
Appreciate your Kudos.