how to change my data source to a Sharepoint Data Source on Power BI

Question

I developed a report with the PowerBI desktop and used an Excel file as a data source. The file was local.

Now I have placed the excel file Sharpoint folder so that it can be modified by the whole team. I want to change the data source and pointer to the file on the Sharepoint so that my report is updated from this new location.

I can't find a solution. please help me.

I can connect to Sharpoint and select my excel file, but the problem is that I have already developed my entire report which had as source the same excel file but locally. Now I have moved it to Sharpoint and I just want to change the data source of my report. I would like to point out that my excel file contains 7 sheets that represent my different tables used for the report. I want my report updated from the rexcel file that is on Sharpoint.

Answer 1

Try to connect to your root folder in SharePoint. Do this:

In Power BI, click Get Data > SharePoint Folder > Connect.

Then enter your SharePoint root folder path and click OK.

The click the Edit button.

Filter the Extension column to only show excel files.

Filter the Name column to only show the Excel file you want to use.

Right-Click on the Content column's title and then click on Remove Other Columns.

Click on the button with the double down arrows in the Content column's title.

Select the objects to be extracted from the workbook and click OK.

Example:

You'll see new queries in the left pane:

Unless you already had one named Query1, you will likely now have one named Query1. Click on that new Query, then click on Home > Advanced Editor. Copy everything between let and in (not including let and in). (That is to say, select everything and press Ctrl+C to copy it to your clipboard so you'll be able to paste it in a moment.)

Example:

Click Cancel.

Click on your original query...the one with your original source that you want to change...then click on Home > Advanced Editor. Select the entire source line and, if you have a line like this one -- Maintenance_Window_Table = Source{[Item="Maintenance_Window",Kind="Table"]}[Data], -- select it too. (This second line is a navigation line, which navigates to the table of interest.) If you also have a #"Changed Type" line, select it as well.

Example:

Paste what you copied earlier over these lines that you've just selected by pressing Ctrl+V.

*Add a comma at the end of what you just pasted. (See the red circle in the picture below) *

Also, in the line that follows what you just pasted, change the reference to the last line of what you just replaced to the name of the last line of what you just pasted, if they are different. The name is what is on the very left, to the left of the equal sign. It is probably #"Changed Type". (See the yellow highlights in the picture below.) If that name appears below what you just pasted, you will either need to change that name here and in the pasted text, or wherever it appears below. I would change it here and in the pasted text above. You could just add a 1 at the end, before the last quote. Otherwise you'll have a naming conflict.

Example:

Click Done.

That should do it.