Forum Discussion
Facing error when changing data source from excel to SharePoint
Hi - I am trying to handover the PBI dashboard published in client workspace. They want to change data source from excel in my C drive to a SharePoint location. I used these steps https://community.powerbi.com/t5/Desktop/Change-Data-Source-from-Local-Desktop-to-Sharepoint/m-p/505172 to do so. However, I am facing errors related to credentials etc. I am simply not able to get past this error. Please help. How do I make this happen.
10 Replies
PoonamS when you are linking your file from sharepoint, you have to specify the path in a format which powerbi can connect.
pl. go through below ariticle which will give you details in loading sharepoint excel to powerbi.
Hi - thanks for much for replying. I am adding URL in the format with .xls file extension at the end. I am getting stuck after putting in my credentials. That's when I get this error that delete your credentials and try again. 😞 Do you know what could be causing the problem. Excel sheet is in same workspace with the same org account
Hi,
Bit late to the party, but I found a nice solution - to change data source from local network Excel file to a SharePoint Excel file which seems to bypass all the credential issues (I was having the same problems):
1. Open the dashboard in Power BI Desktop and go to Power Query Editor
2. Add a new data source New Source > More... > SharePoint Folder. Use only the base URL for your SharePoint site (top level only). This will bring up a list of all files in your SharePoint.
3. Transform Data - this will create the base query (don't bother renaming it)
4. Filter folders if necessary to find your Excel file. Once you find it, click on Binary then click
Table next to the tab you want.5. Click Use First Row as Headers in the ribbon.
6. Go to the Advanced Editor and copy all the code you just created in previous steps
7. Now go to your original query that points to the Excel file on the local network.
8. Go to Advanced Editor for that query, select all and paste the previously copied code over the top.
9. Click OK. Your original query is now pointing to the SharePoint file and no links will be broken.
Voila! 😊 hope this helps someone...
The screenshot in the original post is the error I get. Look at this second view in loading error:
