I’ve been using Power BI since it was released back in 2015, and I’ve found that when talking with other PowerBI users there is always a little confusion about how to share Power BI reports. My experience has been that most people are first introduced to the service and go directly to PowerBI.com, login and start playing around. They explore a little and find a cool data source to connect to, such as a SQL database or some good old google analytics data. Typically they will begin making a dashboard and really get into making visuals on a report.
While this is an excellent way to get started because this removes the complexity of having to model your data, it does come with some challenges that leads to issues later on.
- There comes a point in time that the user usually needs some additional data. The “this is good data but, if I could only join the data with another source….” question almost always comes up.
- These reports are typically made in your personal workspace which is not a good solution for sharing a report with another user. I’ll explain later.
- How do I source control this document? What if I want to go back in time and restore my changes?
In order to answer these questions, I’ll show you what I use to get around these issues.
To start off, and most importantly!, try to refrain from building your dashboards in the PowerBI.com service. Yes, you can do it, but it is not recommended. I don’t recommend this because any report created in your personal workspace can only be edited by you and no one else. Also, if your account is deleted or you loose access to your account no other PowerBI user can modify your original report. It’s the classic, win the lottery, or hit by a bus case, depending if your an optimist or pessimist. What you should do instead, is download and open up the PowerBI desktop application. This will save you ton’s of headache later when you want to join multiple data sources or want to have a saved copy of your Report.
After you download the desktop application you will load the data using the Get Data button found on the Home ribbon.
Almost, all data sources found in the Get Data window are on the PowerBI.com service. You will notice this changes over time, with each monthly release of PowerBI desktop.
FYI, I have noticed that changes happen faster in the PowerBI.com service as it appears as though there are weekly or bi-weekly updates. But the Desktop version is slightly slower to get changes as builds are only released once a month.
Next you will load data into the Power BI desktop. If you want to learn more about modeling you can browse through these tutorials on data modeling. Modeling happens in the query editor with the M language, and on the report pages using DAX.
Now we will create a very simple PBIX file to publish to the PowerBI.com service.
On the Home ribbon click the Enter Data button and enter the following:
This will create a simple data table. Click Load to bring the data into PowerBI Desktop.
Create a visual by clicking on the Stacked Column Chart and adding the following columns to the visual.
Congratulations, we have completed a very simple report and you have loaded data inside the desktop. In addition to loading data PowerBI is equipped with a robust modeling tool called the Query Editor. The very topic of modeling your data is a huge. It covers the Query Editor, making DAX expression and has been covered in numerous books. This this post will not address modeling your data. As a side note, here are some really good sources of information for learning more about DAX.
Check out SQL BI.com from Marco Russo is a real genius and an excellent teacher. His site is amazing.
Another good source of modeling tips and all kinds of other goodies the Radacad Blog from Reza Rad.
Alright, let publish this bad boy. First save the Report. Do this by clicking File then click Save. Enter a simple name such as Sample Report, click Save.
To publish the report to the PowerBI.com Service click the Publish button found on the Home ribbon.
If you have not already signed into the PowerBI.com service you will be prompted to sign in.
If you already have a more than one workspace in the PowerBI.com service a second window will pop up asking you to choose a workspace location. Choose a workspace location to publish the report.
Couple of important notes about workspaces.
- A workspace can have admin users or members. The admin user can add and remove individuals to the workspace. A member can be configured to either, edit reports, or only view reports.
- The workspace can have many admins if necessary which is helpful when collaboratively working on reports with a team.
- I have found that when working on many different reporting projects a workspace helps target a specific audience or provides an organized method to share only reports that would be relevant.
- You cannot create a workspace in this Publish to Power BI window. This has to be done from the service.
Since I am not sharing this report I will publish to My workspace. Click Select to complete the publishing. Once the report is done loading you will have a window that notifies you that the process has completed. A link is provided to check out your report directly on the service. You can dismiss the message by clicking Got it.
Click Got it to dismiss the pop-up window.
While remaining in PowerBI desktop, save the file one more time but use SAVE AS and save the file with the name of Sample Report V2.
Now we will create a new workspace for a report that we might want to share.
Go to PowerBI.com and login. On the left navigation bar click the arrow to expand the workspaces.
A fly-out menu will appear and you can click Create App Workspace.
Fill out your workspace name and configure your settings. Be sure to enter your email address that you used to log into the PowerBI.com service in the workspace members area.
At the bottom of the menu click Save when you have completely configured the settings. Opening up the workspaces fly-out menu will now reveal your new workspace.
Click on the ellipsis of the new workspace PowerBI-test.
Notice when you click the ellipsis, that there are only two options, Edit and Leave. If you give it about 10 minutes or so, a third option will show up, Files. The reason we have to wait is because behind the scenes this workspace is tied to 0365 Groups. Thus, we have to wait for all the services to be fully provisioned before we can proceed. We will want to use the files option of our workspace. Now would be a good time to take a little potty break, pet your dog, get a snack… and come back in 5.
Yumm, I love Oreos…
Now that we have waited a bit, Refresh your browser page. This is important, if you don’t do this you won’t be able to observe the change. Click the ellipsis again and now we have a new item called Files.
Click on Files and you will be re-directed to a custom SharePoint 0365 page for this workspace’s files.
Now we can upload the version two of our PBIX file. Click the upload button on the navigation bar.
After uploading your file it should look like the following:
Go back to PowerBI.com, make sure you are still in the created workspace, then click Get Data at the bottom of the left navigation bar. Then click Get in the Files window.
Then a screen will pop-up showing you which source you can use to get data. Click the title labeled OneDrive.
Upon clicking this we are taken to the OneDrive location that we created with the workspace. In here we will have the Sample Report V2.pbix file. Click the name of the file Sample Report V2.pbix. Then click Connect to load the file.
Note: If you want to make changes to the pbix file you have to overwrite the file in the OneDrive location. Don’t worry, SharePoint does have version control on the files. This will keep the files location nice and clean. It also removes the need for you to version or date each and every file. To learn more about versioning files in SharePoint hit this link.
Now when we click on the workspace PowerBI-test we can see that the dataset, report, and dashboard has all loaded.
There are some advantages of loading your pbix file this way.
- loading a file from OneDrive will automatically refresh the report every hour from the pbix file.
- OneDrive provides version control for your file.
- If any team members want to modify the original file the members of the workspace can access the file from the SharePoint page.
Click on the Ellipsis of the Sample Report V2 under the DATASETS section and select Schedule Refresh from the fly-out window. You will notice just under schedule refresh is a new setting called OneDrive refresh.
Click OneDrive Refresh, and now you can see there is a toggle for keeping the file refreshed every hour, which is enabled by default.
Whew, I think that about wraps it up, start with the desktop to make your files, be sure to create workspaces to share content, and use the workspace files to store and source control your pbix files.
I hope it was helpful.. Make sure you share this blog with another person if you found it valuable.
Hi Mike.
Great article.
One question … would OneDrive be available for users that do not have Office365 associated with their AAD account?
Everything that was talked about in this article is 100% within the Power BI service. When you create a Workspace, a back-end sharepoint site in O365 is automatically created. Thus, as long as you have a PowerBI pro license the O365 files is included. I do not believe that you are required to have an O365 account. Great question by the way. Sorry if that point was not clear.
What happens to the office365 group sharepoint site if you remove the workspace as an admin? Will the office365 group and all the files be deleted along with it?
Yes, it is removed eventually. It does not instantly delete the workspace right away however. I believe the workspace is marked for deletion and then periodically workspace marked for deletion are removed. However, I do not know the schedule on the deletion cycle.
I have followed these instructions, and my dashboards work great. However, when a new CSV file is loaded to the document library (and that URL is the path to my CSV data source in my PBIX), my dashboard never refreshes – it should refresh hourly, but never does. I do have the ‘OneDrive refresh’ turned on. Any ideas?
The one drive refresh is only refreshing the power bi file. The box file that was stored on the share point site. To have the csv file to refresh the report need to be scheduled to refresh. In the left nav bar you can click on the datasets section. Then right click on the dataset that supports your report. From the right click menu sel ct schedule refresh. You will be taken to the dataset settings. Toggle the setting to have the report refresh. This will refresh the data load.
Thank you for that info, that worked.
I had a question about saving and editing files from onedrive. So I have the files saved there. If I want to edit, I have to download the file then resave it back to Onedrive, correct? My problem is that our Onedrive storage is on an admin account so when I go to save as, it doesn’t show the onedrive location. Any help on this?
The OneDrive location that I am referring to in this article is the OneDrive (O365 workspace) that is created by default when a workspace is created. You should be able to access this space and access loading files there. Additionally, it is important to note this only works in OneDrive online.
As a second thought, there is an easier way to share files via the Workspace. Once you have opened up the workspace and navigated to the files section, you will be able to SYNC the folder location with your local computer. How to accomplish this is found in this article: https://support.office.com/en-us/article/sync-sharepoint-files-with-the-onedrive-for-business-sync-client-groove-exe-59b1de2b-519e-4d3a-8f45-51647cf291cd
This will allow you to sync the folder removing the need to download modify and upload files back to onedrive.
Great tutorial, say your source is an Excel file. Should you upload it to the same Workspace next to the pbix file and then set the source path? Or what’s the best solution?
This is possible, but by doing so you will need an extra step in the ETL process to filter out he PBIX file by name. Instead I recommend using a DATA folder where all the input data can be stored. This way you don’t need an extra step to filter out files that are not related to the data ingestion.
I was curious about the step where we Publish from PowerBi Desktop. Why is that necessary? Can we just upload the .pbix file to OneDrive and skip that?
Thanks for the awesome tutorial and website.
For this tutorial, that step is not technically needed, it was just added so people know how to load files directly from the desktop program. You are 100% correct. All you need to do is load the files directly into SharePoint, Then load the files from the PowerBI.com service.
Can the source be any SQL servers? i.e. Azure or postgres then uploading the power bi file to one drive and then following the steps will work?
Yup sure can, the storage of the PBIX file is different than what your speaking of, The data connection.
We always use this feature, but a couple of times we seem to have lost the connection between Power BI and OneDrive. We update the pbix file in OneDrive with a new version and, 8 hours and more later, no update of the report in Power BI Service. Are there any known issues in this area? Is there some way to debug this and repair this? We have had to resort to reloading the pbix file into the Power BI service. We then have to recreate all the dashboard tiles with this newly loaded report.
Interesting, I have not observed this behavior. Be sure you are using the one-drive location attached to the Work-space, and not a personal one drive. Additionally when your uploading new reports make sure you are overwriting the old report. The Share-point Documents will save all revisions of the file so you don’t have to worry about loosing work or data when you replace the file. I will be on the watch for this behavior. Usually when I have seen reports breaking it is because someone changed the name, or move a file between folders or something.
Hi I follow this example and after waiting for 10 minutes. I refreshed my browser page and clicked on the ellipsis of the PowerBI-test and I can see: Members Calendar Conversations —-Separator—- Edit Workspace an Leave workspace, why is that? I can’t continue with the rest of the example… Can you offer a suggestion?
The files section should appear. There is some set up that is happening behind the scenes that Microsoft is doing to create the workspace. You might want to wait a bit longer and try again. As of August of 2018 there is a new workspace experience, which does not create a complete workspace in the background. If you are using the create workspace under the new experience you will not be able to see the Files section.
The lingo tool is returning an error message when I try loading the yaml file.
It says: “ex when trying to parse yaml = Unexpected characters near “: {State: Generated, Weight: 0.97}”. (line 3781: ”record ID #’: {State: Generated, Weight: 0.97}’)”
I just checked this functionality. I am not able to replicate the problem. There is likely a syntax error introduced on line 3781. Try opening up the Yaml in visual studio code navigate to line 3781 and see if you can determine the syntax error.
Hi Mike,
Thanks for sharing this article. Very helpful. I will create a set of reports for users in our organization. I am going to use Power BI desktop and publish to a shared workspace. My question is: should I use my own work account (in Active Directory), or apply for a general account in Active Directory for this task? Will the other people have trouble to access/edit the reports in case my account is deleted?
Thanks,
Great question. To make sure you publish reports in a common location, We recommend the following steps:
1. Create a workspace
2. Add yourself as an Admin
3. Open up Workspace files section (discussed in this article)
4. Place files in the “workspace” (essentially it is a SharePoint site)
5. Then in powerBI.com service use get files and select the file you put on sharepoint
When you need to make changes you will want to just overwrite the file in SharePoint. SharePoint will automatically make versions of the file. PowerBI.com the service will periodically go to the SharePoint files an pull the latest version of the file and refresh the report.
If you want other people to be apart of the Workspace, you will add them during creation or after using the settings found on the workspace ellipsis. This way if you account dis-appears for some reason, your team can still access your files & load to the PowerBI.com service.
Thanks a lot, Mike! That is exactly what I was looking for!
I have synced a local copy of a sharepoint site that I have on my laptop.
I currently have 2 people that update 2 excel files that are stored in the sharepoint directory.
My problem is my Power BI template is looking to the following location C:\Users\mark.x.drury\SharePoint\T 1\General\12 – Telemetry Data
My colleague has also synced the sharepoint folder and her path differs at the beginning.
The Power BI Desktop Template currently sits in the site on the sharepoint site.
I am trying to allow my colleague to refresh the Power BI Desktop file or make it so that it would auto refresh without then having to adjust all the paths of the file locations from C:\Users\mark.x.drury\SharePoint\T 1\General\12 – Telemetry Data to her local location.
I hope you can make sense of the query and any help gladly appreciated.
Yes, that is possible if you point to the Sharepoint location of the file… Instead of point the loading data to a local C drive location. Follow this article on how to load files from Sharepoint: https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/
This enables a common location to point the data ingestion… Note: you and your co-worker must have access to the same sharepoint location. This also enables a team of people to share the PBIX and all refresh the data from SharePoint.
Great Article Mike! Do i really have to use the Power BI workspace created O365 group in Sharepoint to upload files? or can i use any O365 group (own created) to upload files and retrieve them in power bi service?
You can use your own. I just found it easier to manage when you use the PowerBI generated one. Also, if you are going to automatically publish files by using the Get files, you will have to use the Linked O365 Group.
Hi! Great article! How can I change the OneDrive folder I have connected to? Or disconnect from the OneDrive folder I have already, to connect to another one?
Thanks!
You would want to edit the M code in the query editor in order to do this. Additionally you can set a parameter for the location. Setting up parameters are described in this article:https://powerbi.tips/2017/03/using-parameters-to-enable-sharing/
Hi Mike,
Great article! I’ve been working with Power BI for a while now, but didn’t know this option.
One question though: why choose OneDrive in the Get files section, and not SharePoint – Team Sites (apart from OneDrive being more intuitive as you do not need to know the URL of your SharePoint team site)? Or should the result in the end be the same?
I tried SharePoint – Team Sites of course, but it didn’t work. Probably because my organization closed that option, as the message I get is the following (even if I try it with the root URL instead of the actual folder location of my pbix):
“The URL is not valid or you do not have permission to the site. Please check the URL and make sure you have permissions to the site.”
One Drive and SharePoint at it’s core are essentially the same. In this article at the time there were only Version 1 workspaces available and by default when you created a Power BI workspace, you also get a SharePoint site. This made it easy to have a one to one relationship between SharePoint an Power BI. With the addition of Power BI workspaces V2, the automatic SharePoint workspace creation has been decoupled. You can still use SharePoint but it is not automatically created now.
To your latter point about the SharePoint error. There are some permissions issues when accessing files from SharePoint. You would have to be at a certain level I think Owner level for it to work without error. When working with V1 workspaces, all these permissions were pre-configured. Now with V2, you can attach a SharePoint Files location to the workspace but you have to adjust the permissions to minimum of member level for users to pickup files from SharePoint.