Connecting to OneDrive excel file

[CloudMonkey]

Hello,

I'm trying to connect to a onedrive excel file via the "web" data source. I followed these instructions:

https://www.youtube.com/watch?v=t4TzHu8THoA

but am getting this error when trying to connect:

The file definately exists, please could you point me in the right direction? 🙂

Thank you for your help,

CM

[v-lionel-msft]

Hi @CloudMonkey ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CloudMonkey]

Thanks for your help, we eventually found the solution 🙂

I have 2 onedrives mapped to my computer local drive, and can see all the files on them. They are the 'personal' onedrive of me and a 'power bi admin' account.

The file I was trying to access was on the 'power bi admin' account. Even though I could access the file on my computer, in order for power bi to be able to access it via a web link I had to share the relevant folder with myself. And now it works 🙂

Thank you for the help 🙂

[v-lionel-msft]

Hi @CloudMonkey ,

Did you try to delete ‘/$Value’ in the advanced editor?

This is my connection query and I import the excel file from OneDrive for Business successfully.

let
    Source = Excel.Workbook(Web.Contents("https://qiuyunus-my.sharepoint.com/personal/xxxx/Documents/FileName.xlsx"), null, true),
    Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"Sales", Int64.Type}, {"Region", type text}})
in
    #"Changed Type"

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CloudMonkey]

Hi @Pragati11 

I've followed these instructions (https://www.youtube.com/watch?v=uxMlV9KKf6E) to edit some existing mcode which gives the following error message, in case that helps?

(after scrolling down:)

[Pragati11]

Hi @CloudMonkey ,

The video from RADACAD is very straight forward.

You are getting Not Found error, that means Power BI cannot find this file to your one-drive location.

Is your One Drive and Power BI credentials point to same account?

Login to your one drive, navigate to the path that you are trying to access in PBI and check whether your file exists there or not?

Thanks,

Pragati

[CloudMonkey]

Hi @Pragati11 yes onedrive and power bi use the same account.

Yes if I go to onedrive I can see the file. If I enter the file url into the browser the excel file opens.

[Pragati11]

Hi @CloudMonkey ,

Create a new Web source in Power BI using the steps defined in the following blog:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

Check if you are able to connect or not. I am able to connect using the steps as mentioned in the above link.

Make sure to add a new data source and test it.

Thanks,

Pragati

[jkadilak]

This was very helpful for me - solved the problem. Thanks!

[CloudMonkey]

Thanks @Pragati11  but I'm still getting the same error when I click on OK on the step below (with my url of course). It's not prompting me for credentials.

[Pragati11]

Hi @CloudMonkey ,

Have you removed te highlighted part from the copied URL?

Can you please open a new fresh Power BI Desktop file and try because as I don't have access to your power bi file, I am not sure where things are going wrong as this is a straight forward way to connect to one drive.

Clear Power Bi cache before doing this.

Thanks,

Pragati

[CloudMonkey]

Hi @Pragati11 , yes the highlighted part was removed from the copied URL. I've tried from a new power bi file after clearing the cache but it still fails with the same error message unfortunately.

[Pragati11]

HI @CloudMonkey ,

This is very weird in that case.

What version of Power BI are you using? Latest April 2021?

Also are you using root folder as highlighted below to acess your one drive file? (I can't see your folder complete location)

I am not able to reproduce this issue at my end as I can successfully connect to One drive from power BI at my end.

I will suggest raising a support ticket here: https://powerbi.microsoft.com/en-us/support/

Thanks,

Pragati

[Pragati11]

Hi @CloudMonkey ,

What is this $value in the file path?

Thanks,

Pragati

[CloudMonkey]

Hi @Pragati11 

I don't know, this is what I entered (i.e. ending in .xlsx). I don't think I've entered $value anywhere.

(Also I don't think I can check what is in the mcode because it's before that stage in the wizard)