Forum Discussion

PoonamS's avatar
PoonamS
New Member
4 years ago
Solved

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.p...
Need Help
Tips and Tricks
  • negi007's avatar
    negi007
    4 years ago

    PoonamS 

    here you need to select the main link. i think in the dropdown you will get one option like below choose that 

    https;//wlgore.sharepoint.com

     

     

     

Bella42's avatar
Bella42
Advocate IV
4 years ago

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...

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello Bella42,

 

Thanks for your solution. However I'm not getting what I need, or maybe I am doing something wrong. 

In step 8 you suggest to select all the code that we have previously created (and is the code that "cleans" all the data that shows already my finished dashboards) and paste the new code we get after transform the connection with the file located in sharepoint... but what's happening with the old code? I need to maintain this code to preserve the finished dashboards I have when the data source was in local folder... if is the same you did, so you maintain the old code, how did you do it? I'm not sure from where and until where I have to paste the new code... I was trying to replace the old location by the new location in sharepoint but it doesn't work... it gives me errors...

and here you can see the new code and the old one together:

 

Could anyone help to preserve the old code so I don't have to work again on all the Transform data I did it previously?

 

Thank a lot

Ana M