SOLVED
Home

Excel "GetData" from file or web (OneDrive)

%3CLINGO-SUB%20id%3D%22lingo-sub-857687%22%20slang%3D%22en-US%22%3EExcel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857687%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20import%20data%20from%20a%20shared%20file%20via%20OneDrive%20(into%20another%20shared%20file%20via%20OneDrive).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20two%20different%20options%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Using%20%22GetData%22%20from%20a%20file.%20This%20works%20if%20I%20am%20using%20my%20computer.%20The%20moment%20I%20use%20another%20computer%20with%20access%20to%20the%20same%20Drive%2C%20I%20have%20to%20re-link%20the%20data%20from%20the%20new%20computer%20to%20the%20same%20file.%20I%20even%20changed%20the%20names%20of%20the%20multiple%20computers%20to%20the%20same%20name%2C%20so%20they%20have%20the%20same%20%22filepath%22.%20It%20is%20still%20not%20working%20(without%20re-linking%20the%20same%20workbook%20when%20on%20a%20different%20computer).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Using%20%22GetData%22%20from%20a%20excel%20link%20on%20OneDrive.%20I%20tried%20multiple%20links%2C%20the%20one%20that%20seems%20to%20work%20is%20%22downloading%22%20the%20file%2C%20and%20copying%20the%20download%20link.%20This%20only%20works%20until%20the%20file%20is%20changed%20and%20a%20new%20link%20makes%20the%20old%20download%20link%20obsolete.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EPlease%20advise%20on%20how%20I%20can%20use%20either%20methods%20to%20link%20the%20data%2C%20without%20having%20to%20update%20the%20links%20(or%20files)%20every%20time%20the%20sheet%20is%20opened%20from%20another%20computer%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%20I%20have%20spent%20countless%20hours%20trying%20to%20resolve%20this%20issue%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-857687%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EGetData%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-858930%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-858930%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409782%22%20target%3D%22_blank%22%3E%40emilychang87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3Bhas%20this%20%3CA%20href%3D%22https%3A%2F%2Faccessanalytic.com.au%2Fconnect-to-files-on-onedrive-and-sharepoint%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EVIDEO%3C%2FA%3E%20that%20might%20help%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECuong%3C%2FP%3E%0A%3CP%3EMicrosoft%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859370%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859370%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20you%20please%20relink%3F%20I%20am%20getting%20a%3A%26nbsp%3B%3CSPAN%3E504%20Gateway%20Time-out%20%3CFONT%20size%3D%223%22%3Emessage....%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you!%20%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320924%22%20target%3D%22_blank%22%3E%40cuong%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859524%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859524%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%20can%20you%20help%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859706%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859706%22%20slang%3D%22en-US%22%3EStrange%2C%20try%20this%20direct%20link%20to%20YouTube%20instead%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FgJF2e_43FRY%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FgJF2e_43FRY%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859723%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859723%22%20slang%3D%22en-US%22%3EAnd%20an%20article%20here....%20%3CA%20href%3D%22https%3A%2F%2Fssbipolar.com%2F2018%2F10%2F29%2Fpower-query-connecting-to-excel-workbooks-in-sharepoint-online%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fssbipolar.com%2F2018%2F10%2F29%2Fpower-query-connecting-to-excel-workbooks-in-sharepoint-online%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862191%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20link!%20I%20went%20to%20the%20source%20file%20and%20clicked%20%22copy%20link%20to%20clipboard%22%2C%20used%20the%20link%20to%20fetch%20the%20data%20from%20web%2C%20and%20received%20the%20following%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22Unable%20to%20Connect%3CBR%20%2F%3EWe%20encountered%20an%20error%20while%20trying%20to%20connect.%3C%2FP%3E%3CP%3EDetails%3A%20The%20downloaded%20data%20is%20HTML%2C%20which%20isn't%20the%20expected%20type.%20The%20URL%20may%20be%20wrong%20or%20you%20might%20not%20have%20provided%20the%20right%20credentials%20to%20the%20server.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20my%20link%20looks%20like%20copied%20from%20the%20source%20file%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fd.docs.live.net%2Fbb9887e258ca6c3a%2FGSD%2520(M%2520One%2520Drive)%2FOrder%2520Processing%2FLabel%2520Request%2520Form%2520Pivot.xlsx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fd.docs.live.net%2Fbb9887e258ca6c3a%2FGSD%2520(M%2520One%2520Drive)%2FOrder%2520Processing%2FLabel%2520Request%2520Form%2520Pivot.xlsx%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEmily%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862547%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862547%22%20slang%3D%22en-US%22%3EHi%20Emily%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%E2%80%99re%20using%20OneDrive%20personal%20then%20this%20link%20should%20help%20(%20my%20solution%20was%20for%20OneDrive%20Business)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FPower-Query%2FPowerBI-support-for-OneDrive-Personal-as-data-source%2Ftd-p%2F129125%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FPower-Query%2FPowerBI-support-for-OneDrive-Personal-as-data-source%2Ftd-p%2F129125%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898162%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898162%22%20slang%3D%22en-US%22%3E%3CP%3ESO%20sorry%20for%20the%20late%20response...%20was%20out%20of%20town.%20Your%20solution%20worked!%20Thank%20you%20so%20much!%20You%20saved%20my%20excel%20life%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898770%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898770%22%20slang%3D%22en-US%22%3EGlad%20I%20could%20help%3C%2FLINGO-BODY%3E
emilychang87
New Contributor

Hello! 

 

I am trying to import data from a shared file via OneDrive (into another shared file via OneDrive).

 

I have tried two different options:

 

1) Using "GetData" from a file. This works if I am using my computer. The moment I use another computer with access to the same Drive, I have to re-link the data from the new computer to the same file. I even changed the names of the multiple computers to the same name, so they have the same "filepath". It is still not working (without re-linking the same workbook when on a different computer).

 

2) Using "GetData" from a excel link on OneDrive. I tried multiple links, the one that seems to work is "downloading" the file, and copying the download link. This only works until the file is changed and a new link makes the old download link obsolete.

Please advise on how I can use either methods to link the data, without having to update the links (or files) every time the sheet is opened from another computer, etc.

 

Thank you so much! I have spent countless hours trying to resolve this issue

 

 

9 Replies

Hi @emilychang87 

 

@Wyn Hopkins has this VIDEO that might help

 

Cuong

Microsoft

 

Can you please relink? I am getting a: 504 Gateway Time-out message....

Thank you! @cuong 

Strange, try this direct link to YouTube instead

https://youtu.be/gJF2e_43FRY

@Wyn Hopkins Thanks for the link! I went to the source file and clicked "copy link to clipboard", used the link to fetch the data from web, and received the following error:

 

"Unable to Connect
We encountered an error while trying to connect.

Details: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

 

This is what my link looks like copied from the source file:

https://d.docs.live.net/bb9887e258ca6c3a/GSD%20(M%20One%20Drive)/Order%20Processing/Label%20Request%...

 

Thanks,

 

Emily

Solution
Hi Emily

If you’re using OneDrive personal then this link should help ( my solution was for OneDrive Business)

https://community.powerbi.com/t5/Power-Query/PowerBI-support-for-OneDrive-Personal-as-data-source/td...

SO sorry for the late response... was out of town. Your solution worked! Thank you so much! You saved my excel life  @Wyn Hopkins 

Glad I could help
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies