cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
PBI_Newbie3681
New Member

SharePoint Columns Not Appearing in Power BI Dataset

I am a new user of Power BI but I can pretty much build simple dashboards.

I created a SharePoint list which gets updated by a Power App I also built. In my SharePoint list, I have a column for the employee name taken from another SharePoint list (Active Employees). This allowed me to include additional columns such as the employee ID, line of business, location, and supervisor name.

 

PBI_Newbie3681_0-1695037566021.png

 

Thus, when I add a new entry in the list either through the Power App or directly on the list, entering just the agent name already pre-populates the employee ID, line of business, location, and supervisor name on the list item.

 

PBI_Newbie3681_1-1695037608955.png

 

When I click on Export to Power BI the dataset only includes the agent name but not the employee ID, line of business, location, and supervisor name.

 

PBI_Newbie3681_2-1695037777877.png

 

However, the Export to Excel option shows those details.

 

PBI_Newbie3681_4-1695037883866.png

 

I wanted to do reports based on line of business, location, and supervisor, but these columns are showing up empty in Power BI. They are perfectly fine in Excel.

Here are my questions:

  1. What is wrong with the Power BI data set that it leaves all other columns empty?
  2. What can be done in this case?
  3. If I were to add a new column to my SharePoint list, how does that new column get added to my dataset since I already created a Power BI report out of that data set?

Appreciate the help, thanks in advance!

3 REPLIES 3
JoeBarry
Solution Sage
Solution Sage

Hi @PBI_Newbie3681 

 

When you load the list, there are many other columns that need to be expanded, the columns that are missing, I'm quite sure can be found within these. Just expand all columns until you find what you need, when you find it, delete the steps that you don't need.

 

Thanks

Joe

 

 

Hi JoeBarry, thanks for the response. I'm not sure I follow your steps. I don't have hidden columns to expand. The dataset has zero entries for  the employee ID, line of business, location, and supervisor name despite having entries in the Excel query.

Don't use the "export to Power BI" - instead open Power BI desktop and connect to the list with the SharePoint Online List connector. That will let you expand all the fields. If your list has under 5k items you can use the 2.0 connector, otherwise use the 1.0 connector. I did a video here on using SP list data in Power BI if that's useful - the fields expand a bit differently between 1.0 and 2.0, but the concept is similar: https://youtu.be/LYu3wqb2Nx4?si=_9kmJyBNuvw56Qon 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors