Forum Discussion

Ghislain's avatar
Ghislain
Advocate I
9 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

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



    Thanks,
    Lydia Zhang

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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



    Thanks,
    Lydia Zhang

    • bihumano's avatar
      bihumano
      Helper I
      1 year ago

      This procedure is not working anymore. 
      Onedrive changed the url from embed code, removing the resid  &authkey

      Is there any workaround?

      Seems that Google drive is easier to share a xlsx to power bi than onedrive!

      • timbckr's avatar
        timbckr
        New Member
        1 year ago

        Hi i got the same issue,

        have you found a solution?

         

        Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • sgenc's avatar
    sgenc
    New Member
    1 year ago

    It seems that Microsoft pushes customers to use Business products instead of OneDrive personal without giving any information.

  • tadam98's avatar
    tadam98
    Regular Visitor
    9 years ago

    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_3Xq9k&em=2" 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=Excel

     

    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=Excel"), 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