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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
CalebD
Regular Visitor

Stuck: Add 2 Columns from SharePoint into Service Data Model

Hello from Texas,

 

Situation: Inherited a workspace, tasked with re-sourcing 2 existing columns. e.g. replace 'Products'[Color] with 'Products'[Prime Color] into existing visuals in report on service

 

Objective: Add 2 Columns from a Sharepoint Online List 'Products' in a way that propagates to the existing table/data model in the service. This Sharepoint table is already connected/in use in a report in the online service. 

& avoid breaking the report

 

Important Information:

I have the ability to take over the semantic model if necessary

When connecting in a new pbi desktop report via SP Online list 2.0, both old and new columns are present in the table.

I have admin access to the sharepoint list and added the new columns to default view

 

Shortcomings:

Limited sharepoint troubleshooting experience. 

Advanced googling, whitepages, and videos have not helped/been relevant

 

Please:

Ask as many clarifying questions as you like

 

Thank you any & all for your assistance

 

1 ACCEPTED SOLUTION
christinepayton
Super User
Super User

Whether or not you need to update the model depends on how the query was done. Usually one of the first query steps is a "remove other columns" where you choose what to keep - if it was built that way, then you would need to edit that step to pull in the new columns. The query caches, so if you don't see changes you'll want to refresh the query. 

 

By default, any columns added to a list will be available in the query editor. The default behavior is to pull all the back-end columns, so not based on a specific view, but it can be set up that way if someone went out of their way to do it. 

 

Any time you're changing columns, you will temporarily break something, so work on a local copy first - you just have to go back and replace references. You can minimize it somewhat by setting the column names to be the same as the old ones in the model, but usually there's a thing or two you need to go back and fix... 

 

If you're working on a shared dataset, make sure to take a good look at any other reports referencing it. If you want to test things, you can take copies of them and stick them in your personal workspace while you work out the details and make sure everything works, then publish over the old ones in the original workspace when you're done. For the child reports you can modify which dataset they're linked to, so you can swap the copies to point to the dev model in your personal workspace while you work on it, then publish over the model. 

 

View solution in original post

4 REPLIES 4
christinepayton
Super User
Super User

Whether or not you need to update the model depends on how the query was done. Usually one of the first query steps is a "remove other columns" where you choose what to keep - if it was built that way, then you would need to edit that step to pull in the new columns. The query caches, so if you don't see changes you'll want to refresh the query. 

 

By default, any columns added to a list will be available in the query editor. The default behavior is to pull all the back-end columns, so not based on a specific view, but it can be set up that way if someone went out of their way to do it. 

 

Any time you're changing columns, you will temporarily break something, so work on a local copy first - you just have to go back and replace references. You can minimize it somewhat by setting the column names to be the same as the old ones in the model, but usually there's a thing or two you need to go back and fix... 

 

If you're working on a shared dataset, make sure to take a good look at any other reports referencing it. If you want to test things, you can take copies of them and stick them in your personal workspace while you work out the details and make sure everything works, then publish over the old ones in the original workspace when you're done. For the child reports you can modify which dataset they're linked to, so you can swap the copies to point to the dev model in your personal workspace while you work on it, then publish over the model. 

 

v-zhouwen-msft
Community Support
Community Support

Hi @CalebD ,

Regarding your question, you can first create a new report using Power BI Desktop, connect to the 'SharePoint Online List' and then use Power Query to remove the columns you don't need. Then download the original report from Power BI Service to your desktop and copy the visual objects from the original report to the newly created report. Finally, upload the report to Power BI Service.

Or you can create the semantic model you need in Power BI Desktop and then publish it to Power BI Service to rebind the report's semantic model using the APIs.

Reports - Rebind Report In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Thanks for that info. Unfortunately the report and data model contain too many custom columns/calculations & replationships to make that feasible. I'm unable to create the same relationships between the new table from 'SharePoint Online List' and the rest of the data model. Is there absolutely no way to edit the original table connection to include the additional two columns. These columns exist in the same default view as the rest of the SharePoint List. What is the reason they won't propagate to the the rest of the model after being added to default view? Surely there is a better way to do this, no?

If this were an import from excel for example and a column was added to the source *.xlsx, a data refresh would scan the file and add the column to the table in PBI. Is there not similar behavior for this w/ SharePoint? Does adding columns to the default view of SP normally propagate?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors