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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
gavinhuet
New Member

Changing the underlying dataset of a report

Hi,

 

Am I right in thinking that changing a report's dataset from one dataset to another is not supported in the service version of Power BI, even if the only difference between the datasets is additional columns?

 

Regards

Gavin

 

1 ACCEPTED SOLUTION
ankitpatira
Community Champion
Community Champion

@gavinhuet That is right. Changing dataset of a report is not possible through service. Only way is to use pbix file in power bi desktop change source and then re-publish.

View solution in original post

16 REPLIES 16
SuperBIstar
Frequent Visitor

@gavinheut I believe you can do this using the Power BI Api.

Stepan has a great article on how to acomplish the feat.
https://www.linkedin.com/pulse/rebinding-power-bi-reports-different-dataset-%C5%A1t%C4%9Bp%C3%A1n-re...

MatPS
Frequent Visitor

If you're referring to a dataset associated with a datamart, we were also having issues with one of our PBI datamarts and this is how we worked around it.

 

It would sometimes take hours to refresh instead of minutes and if one user pinned a visual to a dashboard, another user with correct permissions would just see a little grey graph icon where the visual should be. We only use the PBI Service so using PBI Desktop was not an option. There's no option to do this yet in the Service but we came up with a bit of a hack to allow us to work around it - it's not for the faint of heart though. It will change the URL(s) for the report(s) too.

 

So if you save a copy of your report(s) to a different Workspace and then delete the original PBI datamart it will wipe out anything under the data mart (including all your measures, date hierarchies, data model connections and associated reports!) but only in the same Workspace - it does not currently do so in different Workspaces.

 

So we recreated the PBI datamart with all the measures, model and hierarchy first and made sure it matched. Then we deleted the old datamart and the PBI reports went into a disconnected state which gives you the settings option to reconnect to a new dataset that doesn't show at any other time. Selecting the dataset associated with the new (identical to the previous) datamart restored the connections. In theory you could set up a Deployment pipeline to a different Workspace and take a copy of the original datamart but we haven't tried that yet.

 

Once the Report is in the "no dataset associated" state you can't view or copy it until it's reconnected to a dataset. So we created a dummy dataset from scratch with a couple of columns in the same Workspace and connected it to that so we could open the Report and save a copy in the same Workarea as the datamart and repeated the process of deleting the dummy dataset. This deletes the report in the same Workspace and leaves the copied back Report in the "no dataset associated" state which allows you to reconnect to your new datamart so that everything works.

 

As mentioned, this method if not for the faint of heart but may save you time in some circumstances. Worth doing a practice run with non-critical datamarts and datasets before attempting any of this.

Greg_Deckler
Super User
Super User

Probably correct, you do not have much control over the data model in general in the Service. Was this a PBIX file you loaded up to the Service or something that you built entirely in the Service?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

It was a local CSV file - the most basic of test.

Look like I would need to use the desktop app and amend the PBIX file manually to change the dataset of a report.

What happens if you just replace the CSV file? I was thinking that you were trying to change its Source. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

If you update the CSV file and only the data has changed then all is good.

If you change the data structure e.g. add or remove a column, even if you do not use that column, then you lose the data and an error with the dataset is reported. The reports are then busted until you update the CSV file with the original data structure.

Yeah, that sounds about right. Guess I haven't messed around with that way of doing things in a while, I almost always use Desktop these days because of those kinds of issues.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
ankitpatira
Community Champion
Community Champion

@gavinhuet That is right. Changing dataset of a report is not possible through service. Only way is to use pbix file in power bi desktop change source and then re-publish.

If that is the case then the Deployment pipeline provided by Power BI service is not of any use. I am using the pipeline and directly deploying the report to production env. I am using a published dataset which are different for dev and production. The report in production is by default taking the dataset published in Dev env. I want to change it to Production dataset. How to do that?

Anonymous
Not applicable

I have found when republishing that this then creates a new version of the same report though when switching from a interanl report dataset to a PowerBI connected dataset. Is ther eanyway to avoid this?

i found a way to swap a dataset to a report (i mean - after i set a remote dataset, which resides under  a PBI service workspace, i would like to set this report to other remote dataset) - in order to do so, need to open Home>Transform data > Select 'Data source settings' option.

a  data hub table is open.

select the dataset that you would like to engage to the current report to. (locate the correct dataset, you can see also its workspace name in the data hub table )

good luck !

I had the same problem when attempting to move from a dataset in the report workspace to a shared dataset in a centralized workspace using the new shared dataset functionality.

 

After downloading the report, changing the connection, and re-publishing to the original workspace, I end up with two copies of the same report with the same name Smiley Sad

Anonymous
Not applicable

Hi, I have the same problem, did you find a way around it?

Hi x3, have you found a solution? I have the same problem. I want to keep the link of the original report, so the users do not see any change

I'm having the SAME issue.  I would like to know how to get around creating two versions of the same report and dataset once I deploy to Production.  I need a solution because otherwise it will change the report link in the App.  Can we have a solution to this??  Any advise?

@ankitpatira

can I revive this dialog by asking a further question:

 

If the long term goal is to be in PBI Service with a Live Connection to one's Azure.....  then can one still begin with Desktop/Import?

 

It is my understanding one can begin with Desktop/Import and then install a Gateway once you go live with it so that it refreshes periodically.  No question on this.

 

But it is not clear to me if there is a transition ability to a Live Azure from Desktop/Import-

 

If there is: would welcome a link to an explanation of the mechanics of that

If there is not: then the initial report design would need to be done in the final Power BI Service resting place - or is there a way to transition between PBI Services?

 

 

www.CahabaData.com

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