Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PowerBI & Survey Report

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:

 

Image and video hosting by TinyPic

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:

 

Image and video hosting by TinyPic

 


Here is what Im looking to achieve:

Image and video hosting by TinyPic



Image and video hosting by TinyPic

 

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

 

MS forms:

 

Image and video hosting by TinyPic

 

 

SharePoint Survey App:

 

Image and video hosting by TinyPic

 


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! 

3 REPLIES 3
v-yulgu-msft
Employee
Employee

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:

 

Image and video hosting by TinyPic

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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 queryFirst step, click on edit query

Then I highlight all my questions 1-65:

 

Second step: highlight all questionsSecond 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 columnsthird 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:

 

fourth.png

 

Here is how the data looks:

 

Data sampleData sample

 

Here is the same data loaded on PowerBI:

 

Loaded in PowerBi Desktop.png

 

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:

 

Closer look at data.png

 

I appreciate the help! 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.