Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ghislain
Advocate I
Advocate I

PowerBI support for OneDrive Personal as data source

Hi everyone !

 

I'm trying to get data in my powerBI desktop from an excel file which in OneDrive personal but I can't Copy/Paste the link of my file. Can we upload from OneDrive personal ?

 

Thank's in advance

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

Hi @Ghislain,

Yes. Please use the following link  to connect to excel file located at OneDrive for personal from Power BI Desktop.

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

Replace the bold parts with the resid and authkey that are found by right-clicking on the specific file and selecting “Embed”.
1.PNG2.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
tadam98
Regular Visitor

When I follow this procedure in PowerBI Desktop and do Home/NewQuery/Web and place the URL instead of linking to the excel, it calls up excel and opens it in exel.

 

EMBED CODE from EXCEL:

<iframe src="https://onedrive.live.com/embed?cid=46E7F41E91D28B46&resid=46E7F41E91D28B46%21568&authkey=AGH0iThHc_..." width="402" height="346" frameborder="0" scrolling="no"></iframe>

 

INSERTED INTO THE PROPOSED TEMPLATE:

https://onedrive.live.com/download?resid=46E7F41E91D28B46%21568&authkey=AGH0iThHc_3Xq9k&em=2&app=Exc...

 

The generated m_language code is:

let
    Source = Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=46E7F41E91D28B46%21568&authkey=AGH0iThHc_3Xq9k&em=2&app=Exc..."), null, true),
    Q2_2015_Table = Source{[Item="Q2_2015",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Q2_2015_Table,{{"Month", type date}, {"Category", type text}, {"Product", type text}, {"Sales", type number}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not      List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
   #"Removed Blank Rows"

 

In your code, make sure Q2_2015 and Q2_2015_Table are substituted with the name of a table you want to access. In case the table is NOT found PowerBI desktop will ask you to select a table.

 

Note the added step of Removed Blank Rows - apparently when reading excel dara placed on a web drive some blank rows might be added. So this step gets rid of those.

 

Code was competed and tested with http://www.myonlinetraininghub.com/ which taught me what I know.

 

Mickey

 

 

v-yuezhe-msft
Employee
Employee

Hi @Ghislain,

Yes. Please use the following link  to connect to excel file located at OneDrive for personal from Power BI Desktop.

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

Replace the bold parts with the resid and authkey that are found by right-clicking on the specific file and selecting “Embed”.
1.PNG2.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

After digging Google for two days I found this solution works like a charm!

Thank you!

Thank you very much. Very helpful

Anonymous
Not applicable

Hi, 

This doesn't work with me..........i could copy the URL & paste it in power bi (Web Source), but while loading i couldnot find the appropriate columns. After loading i could find data like this..........how can i get appropriate data. please provide steps if possible

 

thanx in advance..

onedrive.PNG

Thank   @v-yuezhe-msft  for your answer ! It works perfectly !!!

Anonymous
Not applicable

hi, I did that way and it seems it could connect to my excel file in onedrive personal however, 

it does not recognize tables... first my excel's table wasn't "table" format so I made them as "Table" format

however, still the same, Power BI does not read data.

 

After connecting, navigator screen shows only..  folder-document.

The document's table view on the right side shows.. only...

Kind        Name  Children   text

Element  HTML   table        null

 

how can I have it read data?

 

 

 

 

 

Anonymous
Not applicable

Hi TDNoz,

   i too facing same issue, how can we understand this data..Can anyone pls help..

onedrive.PNG

I am trying to connect a file from my personal drive based on the above and seems that three is an issue with the credentials which does no permit the Power Bi service to update and get data from the OneDrive personal

From some posts I realized that there some changes on the authentication
As we speak I cannot connect to excel file when I don’t have any issue with CVS files
Is there any update?
Can anyone enlighten us?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors