Facing error when changing data source from excel to SharePoint

[PoonamS]

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

[PoonamS]

The screenshot in the original post is the error I get. Look at this second view in loading error: 

[PoonamS]

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

[negi007 · Accepted solution]

@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]

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]

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

[Bella42]

Hi Ana,
From what I can see, the error you are getting is because you now have two steps called #"Filas filtradas". One from the old code and one from the new. Just pop a 2 on the end of the second one, make it #"Filas filtradas2", and change the previous step reference in the subsequent step ("Column quitadas2") to match.
so the new code in that section will be:
#"Filas filtradas2" - Table.SelectRows....
#"Columnas quitadas2 = Table.RemoveColumns(#"Filas filtradas2",{"Column22"...
It should work now. I also keep running into these glitches, it can be so finnicky, but still way better than having to recreate the transformations. 
Good luck, thanks for reaching out, hope it helps 🙂 Claire

[Anonymous]

Thank you so much Claire, you are a master. I didn't see that... however my code is so long so it keeps me doing errors that I am solving step by step. But investing lots of time so my boss told me to find another way to connect the data from Sharepoint without doing all this re editing of the entire code... especially because we have about 7-8 different PBIs to do this migration... I will keep searching for more ways, if it exists!

Thank you so much and best regards,

Ana M

[Bella42]

No worries, glad to help 😊
I migrated a dozen+ PBIs with numerous connections in each. I kept the SQL for the various connections in a word doc (e.g. I have several Excel lookups, and the connection+transformations is always the same), so I could just copy and paste the connection and transformations as a whole. I also did the PBIs all at once, and it became easier to prevent and spot problems. Took me about half a day to get them all done. I changed the query names to include SharePoint (or ShPt) so I knew at a glance which I have updated.
I think you have done the hard yards (working out what the hell is going wrong), and it should get easier, less time consuming, from now on. I haven't found an easier way. Good Luck! Kind regards, Claire

[PoonamS]

ah, I was able to make it work this time for the specific folder I was looking for (bottom most link) - maybe I didn't have access to the previous main folders - my speculation  🙂 Thank you so much. Which answer of yours should I marked as solved 🙂 is there an option to delete the post in case this isn't helpful to anyone

[negi007]

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

https://www.biinsight.com/quick-tips-connecting-to-excel-files-stored-in-sharepoint-online-from-powe...