'Data could not be retrieved from the database' error when using power query to get data from xlsx

%3CLINGO-SUB%20id%3D%22lingo-sub-90732%22%20slang%3D%22en-US%22%3ERe%3A%20'Data%20could%20not%20be%20retrieved%20from%20the%20database'%20error%20when%20using%20power%20query%20to%20get%20data%20from%20xl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-90732%22%20slang%3D%22en-US%22%3EThanks%20for%20letting%20me%20know%2C%20Qiong.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-90658%22%20slang%3D%22en-US%22%3ERe%3A%20'Data%20could%20not%20be%20retrieved%20from%20the%20database'%20error%20when%20using%20power%20query%20to%20get%20data%20from%20xl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-90658%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20was%20working%20before%20the%20'Unpivot'%20step%20and%20can%20be%20load%20into%20tables.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20managed%20to%20find%20out%20by%20repeating%20the%20same%20process%20in%20Power%20BI%20that%20it%20was%20due%20to%20calculation%20errors%20in%20the%20original%20xls%20files.%20Power%20BI%20gives%20a%20more%20prescriptive%20warnings%20than%20Power%20Query%26nbsp%3B2010.%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-89919%22%20slang%3D%22en-US%22%3ERe%3A%20'Data%20could%20not%20be%20retrieved%20from%20the%20database'%20error%20when%20using%20power%20query%20to%20get%20data%20from%20xl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-89919%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20happens%20if%20you%20remove%20the%20%22unpivot%22%20step%3F%20Does%20it%20work%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20any%20case%2C%20would%20you%20mind%20sharing%20the%20workbook%2C%20please%3F%20Just%20make%20sure%20it%20doens't%20contain%20any%20sensitive%20or%20confidential%20information.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy%3C%2FP%3E%0A%3CP%3E-%20Ecel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-84619%22%20slang%3D%22en-US%22%3E'Data%20could%20not%20be%20retrieved%20from%20the%20database'%20error%20when%20using%20power%20query%20to%20get%20data%20from%20xlsx%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84619%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20using%20'Power%20Query'%20addin%20in%20Excel%202010%20to%20get%20data%20from%20an%20old%20.xlsx%20file.%20It%20was%20working%20perfectly%20fine%20and%20loading%20data%20into%20table%20in%20current%20workbook%20until%20I%20'Unpivoted'%20some%20columns.%20I%20don't%20understand%20why%20'Unpivot%20Other%20Columns'%20operations%20can%20stop%20data%26nbsp%3Bload%20and%20throw%20me%20an%20'Data%20could%20not%20be%20retrieved%20from%20the%20database'%26nbsp%3Berror.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-84619%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1751878%22%20slang%3D%22en-US%22%3ERe%3A%20'Data%20could%20not%20be%20retrieved%20from%20the%20database'%20error%20when%20using%20power%20query%20to%20get%20data%20from%20xl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1751878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Guy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20just%20hit%20this%20same%20problem%20where%20I%20get%20the%20%22Data%20could%20not%20be%20retrieved%20from%20the%20database%22%20error%20when%20including%20an%20unpivot%20in%20my%20power%20query.%26nbsp%3B%20Do%20you%20know%20if%20this%20flaw%20has%20been%20fixed%20in%20the%20several%20years%20since%20Qiong%20brought%20it%20up%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EAlex%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1783426%22%20slang%3D%22en-US%22%3ERe%3A%20'Data%20could%20not%20be%20retrieved%20from%20the%20database'%20error%20when%20using%20power%20query%20to%20get%20data%20from%20xl%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1783426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F822664%22%20target%3D%22_blank%22%3E%40alexcabramson%3C%2FA%3E%26nbsp%3B%2C%20it%20should%20have%20been%20fixed.%20I%20encourage%20you%20to%20update%20to%20the%20latest%20version%20and%20try%20if%20it%20works.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGuy%3C%2FP%3E%0A%3CP%3E-%20Excel%20Team%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I was using 'Power Query' addin in Excel 2010 to get data from an old .xlsx file. It was working perfectly fine and loading data into table in current workbook until I 'Unpivoted' some columns. I don't understand why 'Unpivot Other Columns' operations can stop data load and throw me an 'Data could not be retrieved from the database' error.

5 Replies
Highlighted

Hi there,

 

What happens if you remove the "unpivot" step? Does it work?

 

In any case, would you mind sharing the workbook, please? Just make sure it doens't contain any sensitive or confidential information.

 

Guy

- Ecel Team

Highlighted

It was working before the 'Unpivot' step and can be load into tables. 

I managed to find out by repeating the same process in Power BI that it was due to calculation errors in the original xls files. Power BI gives a more prescriptive warnings than Power Query 2010.

Thanks for your reply.

Highlighted

@Guy Hunkin 

 

Hi Guy,

 

I've just hit this same problem where I get the "Data could not be retrieved from the database" error when including an unpivot in my power query.  Do you know if this flaw has been fixed in the several years since Qiong brought it up?

 

Thanks,

Alex

Highlighted

@alexcabramson , it should have been fixed. I encourage you to update to the latest version and try if it works.

 

Guy

- Excel Team