PowerBI & Survey Report

[Anonymous]

I have two issues with PowerBI and connecting to SharePoint List data.

1. When connecting to SharePoint List data, PowerBI pulls the Column names into the dataset but Instead of using the "DisplayName" or "Friendly Name" it uses the internal name and it makes the report look ugly. The columns contain numbers and special characters. 

 - Is there a way to have PowerBi use the DisplayNames for all columns?

-I saw this article and it helped me query the DisplayNames but Im really not sure how I can relate this query with my current dataset.

2. This particular report is survey data and the client is looking for a way to have a stack bar graph for each question with its results in one graph. I know PowerBI does it out of the box but you have to "drill down" through the hirearchy one by one for that to work.  like so:

This example only shows one by one....

I am looking to have a all the question results on the Y axis from 1-65 questions and on the X-axis the percent of Yes,No and N/A. This will obviously make the tile long vertically since it contains alot of questions but I envision it will provide a scroll bar so that the enduser can look through all the question responses? 

-Is there a way to achieve something like that? 

I saw this other article which talks about the same layout I am looking for but everytime I select "unpivot selected columns" it takes a really long time to re-load the data and then all columns say "error" in yellow. 

Here is the error detail:

Here is what Im looking to achieve:

which is exactly what Microsoft Forms and a regular SharePoint Survey App offers in terms of each response:

MS forms:

SharePoint Survey App:

but these solutions do not provide things like categories and other type of data that needs to be captured for my dashboard report to filter by and other client requirements. 

I feel like I must be doing something wrong here... has PowerBI really not throught of handleling Survey response data yet? 

Any guidence is greatly appreciated! 

[v-yulgu-msft]

Hi @Anonymous,

---

 1. When connecting to SharePoint List data, PowerBI pulls the Column names into the dataset but Instead of using the "DisplayName" or "Friendly Name" it uses the internal name and it makes the report look ugly. The columns contain numbers and special characters. 

 - Is there a way to have PowerBi use the DisplayNames for all columns?

-I saw this article and it helped me query the DisplayNames but Im really not sure how I can relate this query with my current dataset.

---

Please refer to this similar thread: SharePoint Lists - how to get Display names

I am looking to have a all the question results on the Y axis from 1-65 questions and on the X-axis the percent of Yes,No and N/A. This will obviously make the tile long vertically since it contains alot of questions but I envision it will provide a scroll bar so that the enduser can look through all the question responses? 

-Is there a way to achieve something like that? 

I saw this other article which talks about the same layout I am looking for but everytime I select "unpivot selected columns" it takes a really long time to re-load the data and then all columns say "error" in yellow. 

Here is the error detail:

I think "Unpivot columns" is the correct solution. How did you "Unpivot"? Please provide more detailed steps. What is the error prompt? Please paste the error message here to make it more clear. Besides, please post some sample data (how the data table like after loaded to desktop) so that I can try to reproduce this scenario on my side.

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft

The error message is:

DataSource.Error: We couldn't parse OData response result. Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value.
Details:
DataSourceKind=SharePoint
DataSourcePath=https://mytentnat.sharepoint.com/sites/qa/_api/Web/Lists(guid'542c4b4b-f41d-472b-a401-8e0768adc2d8')...

The steps I took to "unpivot" the data is as follows: 

From the PowerBI Desktop I click on "Edit query":

First step, click on edit query

Then I highlight all my questions 1-65:

Second step: highlight all questions

I then right click on one of the highlighed ones and then select "unpivot only selected columns" from the menu:

third step: select unpivot only selected columns

Then it takes about 20-30 minutes to process and I get a window with 1k+ errors and all the columns cells change to "error" in yellow:

Here is how the data looks:

Data sample

Here is the same data loaded on PowerBI:

I realize maybe these iamges are too long and the data wont display propertly so here is a sample excel file I can share with you
: https://drive.google.com/file/d/1ikJB-l1Y2eOhaXNK-jnzrzpz66ChHXZY/view?usp=sharing

and finally... a closer look at the data:

I appreciate the help! 

[pvanderlinden]

I working through the exact same issue. Ever find a solution?