Change Data Source from Local Desktop to Sharepoint

[sreejitjaani]

Hi,

I have built 25 reports and have hosted the input excel files in my local machine. Now I want to map the already created dashboards with the same excels hosted on the Microsoft Teams sharepoint. How can I do that?

[DarrenLau]

Adding on to the reply.

You can change the location by editing the Data Source Settings :-

Click on each of the files located on your C:/ drive and replace with the corresponding Sharepoint URL.

As per the previous reply, work on a copy of the PBIX in case of issues.

[sreejitjaani]

I am using Microsoft Team sharepoint. When I copy the excel link the sharepoint and try to put it in the new source, I get an error saying below-

"The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

[DarrenLau]

Hmmm, I am syncing via my personal Sharepoint/OneDrive directory (confusingly named, I know - in File Explorer, it's a OneDrive folder, but online it's a Sharepoint URL).

Let me check how it works via Teams Sharepoint and come back to you.

[DarrenLau]

Okay, did some testing and this works -

Teams Sharepoint URL looks like this :-

https://YOURCOMPANYNAME.sharepoint.com/teams/YOURTEAMNAME

The subfolder and path-to-file would add the following:-

/Shared Documents/SubFolder1/Filename1.xlsx

The complete URL would be 

https://YOURCOMPANYNAME.sharepoint.com/teams/YOURTEAMNAME/Shared Documents/SubFolder1/Filename1.xlsx

Query Editor code would look like this:

let
    Source = Excel.Workbook(Web.Contents("https://YOURCOMPANYNAME.sharepoint.com/teams/YOURTEAMNAME/Shared Documents/SubFolder1/Filename1.xlsx"), null, true),
    Teams_Table = Source{[Item="Teams",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Teams_Table,{{"TeamIndex", type text}, {"Team", type text} })
in
    #"Changed Type"

You can edit the query via Query Editor or edit in Data Source Settings replacing the C:/ path with the full URL path.

[sreejitjaani]

So the below is the link from the Team sharepoint-

https://COMPANYNAME.sharepoint.com/:x:/r/sites/FSSDashboard/Shared%20Documents/General/FSS%20Dashboa...

How do I simplify it in the baove format you mentioned.

The folder hierarchy which I can see is - FSS Dashboard --> Documents --> General --> FSS Dashboard Input Reports --> 1. CMSI --> Dispute Lost Points.xlsx

[DarrenLau · Accepted solution]

Are you able to navigate to your Sharepoint/Teams site on a web browser using the following URL format?

https://YOURCOMPANYNAME.sharepoint.com/teams/YOURTEAMNAME

That would be the starting point to determine the correct URL and then you can determine the folder names/structure.

[v-lili6-msft]

hi, @sreejitjaani 

You could try this way:

Download the pbix file, then open it in power bi desktop

• Click on edit queries
• Select the query
• Advanced Editor
• Go to the first step in the Query (always called Source) and change it there

You can change the datasource from here.

Then do apply , and republish it to power bi service.

By the way: you'd better use copied pbix files to do these.

Best Regards,

Lin