Onedrive Personal to Power Query

[Kristian71]

It seems like MS have done a change when you want to connect Power BI desktop to an Excel on Onedrive Personal.
I have a 365 family subscription and want to connect an Excel on my Onedrive to PBI and publish it.

However it seems that it's a problem since MS has changed the Embed code to not include the Auth and SRC values.

I have tried to use: https://onedrive.live.com/?id=4C.......,

But this url does not include an Auth or src value.

I have tried to use: https://1drv.ms/x/c/4c....

from the Embed value generated, but it also does not include Auth or src.

I have tried to use: Source =Json.Document(
Web.Contents(https://onedrive.live.com/personal/4c..../_layouts/15/Doc.aspx?resid=4C....&cid=4c...&migratedtospo=...

But here it will not accept my credentials.

I could create a cloud connection using Microsoft365 as connection type with OAuth 2.0 authentication, it connects with success.

Anyone having the process to connect?

I know I can create a Google Sheet and use that connector, I've tested and it works fine, would just like to keep my files on OD.

[hugoventurini]

Hi Kristian

Please, try this tutorial, and give a feedback.

Guia completo de Integração Power Query - Excel, Power BI e DataFlows - com OneDrive Personal, OneDr...

[Kristian71]

Hi Hugo
That was very usefull, although not succesfull.
As mentioned, it is from a personal onedrive, and when I paste the zipped url and it expands, the string is slightly different as shown below.
I have replaces some values with xxxxx to keep the url private.
As you can see there is no authkey.
I have tried to share in different ways but no authkey.
I have tried to use the 'redeem' value as authkey but no success.

https://onedrive.live.com/:x:/g/personal/4C0BxxxxxB05310B/EQxxxxx97AsgxxxxxQAAAAABxjlTwbxxxxxwtyuWjKxjfA?resid=4C0BExxxxx05310B!1xxx3&ithint=file%2Cxlsx&e=lXxxxx&migratedtospo=true&redeem=aHxxxxx6Ly8xZHJ2Lm1zL3gvxxxxxzBiZWNiZDFiMDUzMTBiL0VRc3xxxxx5N0FzZ2dFejlMUUFBQUFBQxxxxxR3YmZTQ2tUd3R5dVdqS3hxxxE_ZT1sWFpDMUw

Would you have any further recommendations?
Really a good document you created.

[v-prasare]

@Kristian71 As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by our super users for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Antunes]

I am experiencing the same difficulty.
The embed code for files in Onedrive personal has changed, and the previous rule:

"https://onedrive.live.com/download?resid=AAXXXX&authkey=AAXXXXXXX&em=x&app=Excel"

no longer applies.

[v-prasare]

@Kristian71, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by our super users for your issue worked? or let us know if you need any further assistance here?

@speedramps, Thanks for your promt response here.

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Kristian71]

So The point is that I want to publish to the service, so I need to connect from desktop to OneDrive online, so the connection can be utilized in the service.

installing a personal gateway and create a connection to my pc is also not an option.

[speedramps]

Yes ... as you say, it is your bad.
You specifically asked to "connect Power BI desktop to an Excel on Onedrive Personal."

You were given the correct answer.

Please have the courtesy to accept the correct solution and raise a new post wuith your new question.

You will get quicker and better answers if you ask the right questions,

and volunteer helpers might not feel they are time wasting.

I am happy to help when the new post is fortcoming but I operate a one question per ticket policy and politely ask members to take care and attention when asking questions.

Don't feel bad ... you will be gettng two answer for the price of none 😀

Other helpers may help you with this one. 

Best wishes 👍

[Kristian71]

Well, going down that path, it wasn't really an answer to my question, as I specifically ask into OneDrive and the authorization and source. 
It`s all about getting The connection to The online OneDrive .

[v-prasare]

Use the Web Connector in Power BI Desktop to connect directly to the OneDrive Personal link. the OneDrive for Business connector in Power BI should work to connect your OneDrive Personal Excel files in the cloud directly.

let me know if this helps you resolve query?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Kristian71]

Thanks v-prasare, but no luck.

When I use the web connector it will not connect, it remain stuck in the connection window:

Searching for a dedicated onedrive connector, none is available:

I've tried the web connector as well using a direct link to the Excel, but that is usually not required, and I would normally add it either as a parameter and/or a alternate url in M.

[v-prasare]

Hi @Kristian71,

Hope your doing well

I suggest raising a support ticket for better guidance on above issue your facing

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks,

Prashanth Are

MS Fabric community support

[v-prasare]

@Kristian71, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by our super users for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[speedramps]

Try this ...

In Excel save your file on Onedrive as Excel Workbook (*.xlsx)

In Power Bi desktop click  on Get data > Excel workbook

and navigate to the file.  Easy

If you Publish to PBIX to Power BI Service then you make have an issue, but that was not your question.

You asked how to access an Excel file from Power BI Desktop 

[Kristian71]

Correct that I did not specifically mentioned pbi service but i did mention i was able to create a connection from the service, my bad.

So it is all about being able to schedule a refresh in the service up against my personal OneDrive.