Splitting a Power BI (PBIX) File into a Dataset and Lean Report File

[Music] [Music] hey data fans reed here today i want to show you a way to take a current import mode power bi desktop file and split it into separate files for the model and report now this can be really useful when you realize that the current model can service more than one report and you want to then isolate the model for governance or other purposes i actually showed this technique a few years ago on another channel but i really wanted to revisit the topic again so let's hop into power bi and get started so essentially what i have right now is a standard power bi report it's ones that you've seen and worked on most the time where you have both the report layer but you know you also have the data and the model everything inside of this file now there might be some times where you want to create a lien report meaning let's say this data model actually is going to be servicing three or four different reports and you want to now convert it because you realize that this model is going to get used for a few different things within the company now rather than needing to recreate the data set from scratch and then a new report on top of that to connect to it what you would call a thin report that connects to the published model in power bi you can actually take this file and kind of split it into two and i actually covered a video on this a couple of years ago on a different blog and then more recently i saw earlier this year a blog post over at powerbi.tips that showed you how to do this with powershell i want to show you how to do that without any code and just to be able to split the file relatively easily so to start with we're going to go ahead and split this into the data set first so i'm going to create a new blank tab i'm going to get rid of all of my sheets in here go ahead and just delete them perfect and now we have essentially just the data set no report or pages in here at all simply a data set that will publish so i'll go ahead and save it under the name data set there we are and this gets published to the service with that name and i'll go ahead and publish it to my workspace beautiful now let's go ahead and hop into the power bi service for a second because i want to point out something so in my workspace you'll see that it published the data set and also it published a report layer now the report layer is actually completely empty all that had was the page one that was in there you can't actually publish a power bi desktop file with zero pages it does need to include at least one non-hidden page so in this case we don't actually need the report because it's only going to be a data set in this workspace i'm going to delete that here just remove the report all i actually need is just the data set down here and now we're going to connect that existing report that i started the video with to this data set down here so switch back over to power bi so i have the same report i just started with a little bit ago but now what i want to do is i want to get rid of the model and connect this report already built with its bookmarks visuals everything that's in here and i want to connect that to the service model so the trick around that is to get rid of the model first from this report i'm going to go up to the transform tab open up this query editor here and i'm going to delete all of the items in here every single one all of my queries go away close and apply this and everything will break temporarily but what you need to make sure to do is that every single table needs to come out of your model whether or not it's an enter data table whether or not it's a dax generated table or a power query table that was brought in from an external data source all of them need to be deleted now with that being done what you can do is you have the ability now to come in and say get data power bi data sets and if i look for data set in my service account and i select create look at that it now connected to that data set i can actually see the relationships and everything in here from that published file but it has now brought that in it has connected to it and i have all the fields that are still there because it's the same model it's just now using the connected service version so i was able to split the two of them really easily keep all of my bookmarks and everything else with very little effort no code and allow that nice clean split to save that report split that model and minimize any of that extra effort and now that data set can be used for all sorts of different scenarios in that workspace for reports in other workspaces whatever you want to use it for thank you so much for watching and please don't forget to like comment or share this video plus if this is your first time to my channel or you want to see more of these awesome videos please click that subscribe and notification button so until next time

Channel: Havens Consulting

Views: 26,012

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, Power Pivot, PowerPivot, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power BI DAX, Power BI Excel, Power BI Versus Excel, Power Query Tutorial, Power BI Separate Report from Model

Id: PlrtBm9YN_Q

Length: 4min 28sec (268 seconds)

Published: Tue Aug 11 2020