If you want to be able to share your Excel sheets data with everyone else for them to be able to build reports, you have several options. You can either share the access via OneDrive for Business directly, or you can create Power BI Dataflows to load the Excel sheets and use them. I really like the Dataflows approach which makes sense if you already started using them for other data sources.
Content
Excel Online as a Data Source
First, let’s have a look at how we can easily load Excel sheets to our Power BI reports in a way that support scheduled refreshes without the need of using any gateway.
We start with a Excel sheet in our OneDrive folder. If you don’t have one, you can easily upload your file via browser or from Excel.
We need to get a link that will be used for configuring our data source. For that we reopen the file in desktop.
In Excel, we find the URL in File > Info > Copy path.
And this how the URL looks like in our case:
https://mewssystems-my.sharepoint.com/personal/vojta_mewssystems_com/Documents/Demos/Movies.xlsx?web=1And now let’s move to our Power BI Desktop and, in our report, we start adding new data source by clicking Get Data > Web.
And we use our URL to load the data. Notice we removed the ?web=1 from the end.
?web=1 part at the end.We will be asked to specify the access to the data. Since the data set is private, we will use the Organizational account, sign in using our credentials and confirm by clicking Connect.
After that we are presented with the list of sheets in our file and we can pick which should be loaded into our report and confirm by clicking Load.
And we are ready to build our report now.
Building your Dataflow
What if we want to share these data with everyone else through Dataflows? We begin by copying the Power Query definition for the data source. For that, click Edit Queries in the ribbon bar. And in the Power Query Editor, right-click the our data source query and select Advanced Editor.
We will keep this open for later when we need to copy and paste the code.
Now, let’s go to Power BI Service at https://app.powerbi.com. For this part you need to have an account with a Power BI Pro license, so make sure you have it.
Create a new workspace or use existing one, go to the workspace and click Create > Dataflow.
And start adding new entities by clicking Add new entities.
We scroll down the Data sources, find and click Blank query.
Now, we can copy the query from Power BI Desktop, paste it into the textarea within a newly open form and confirm by clicking Next.
We will see our new entity prepared in the dataflow to be created, but it is not ready yet. We need to specify the credentials. We will use our organizational account again. Begin by clicking Edit credentials.
Select Organizational account and click Sign in. After using your account to finish the process in new modal window, click Connect.
We finally see our data loaded in the dataflow and we can click Save & close.
For a while, we will see message about validating queries, so we need to wait.
After that, if we are creating a new dataflow, we are asked to specify the name and, optionally, the description. Confirm by clicking Save.
For the data to actually get loaded, the dataflow needs to be refreshed. You can choose to Refresh now or configure refresh schedule.
In the workspace, we can check the dataflows section for our new dataflow and that it has been refreshed.
Using a dataflow in reports
After the data is refreshed, we can load it into our report by going back to Power BI Desktop and clicking Get Data > Power BI dataflows.
If we haven’t done it yet, we need to sign in with our organizational account
and our new dataflow will be found there. To load it click Load.
When the dataflow has the scheduled refreshes configured, if we make any changes to the Excel sheet in the OneDrive, the updates will be propagated to the dataflows as well and subsequently to the reports.
Summary
We have managed to use Excel sheets in combination with OneDrive and Power BI Dataflows to share our data within the company to build the report easily.
It allows us to maintain the control over the shared data and keep the reports up to date with our changes through refreshes. This way, these data can be managed also by someone who does not need access to the Power BI.
