Power Query [DataFormat.error] External table not in expected format

%3CLINGO-SUB%20id%3D%22lingo-sub-320485%22%20slang%3D%22en-US%22%3EPower%20Query%20%5BDataFormat.error%5D%20External%20table%20not%20in%20expected%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320485%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20working%20with%20Power%20Query%20as%20a%20way%20to%20combine%20datasets%20from%20two%20systems%20then%20plugging%20it%20into%20PowerBI%20for%20about%20a%20year%20now.%20This%20month%2C%20when%20I%20ran%20and%20refreshed%20data%20for%202018%20year-end%2C%20I%20am%20getting%20an%20error%20I%20have%20never%20gotten%20before%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20814px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68960i554BFF20527DA051%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22DataFormaterror%20Excel%20PQ.PNG%22%20title%3D%22DataFormaterror%20Excel%20PQ.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThere%20are%20always%20a%20few%20things%20to%20tweak%20here%20or%20there%20like%20adjusting%20the%20files%20that%20have%20been%20added%20to%20the%20source%20(SharePoint)%20so%20that%20it%20narrows%20down%20to%20the%20correct%20file.%20That%20is%20not%20the%20problem%20here.%20It%20seems%20to%20only%20error%20out%20at%20the%20step%20where%20the%20table%20is%20expanded%2C%20before%20any%20of%20the%20transformation%20steps.%20I%20have%20gone%20through%20my%20source%20files%20and%20checked%20their%20columns%20for%20data%20type%20and%20they're%20all%20normal.%20A%20few%20of%20the%20tables%20weren't%20updated%20at%20all%2C%20however%2C%20I%20understand%20that%20if%20one%20file%20errors%20out%20-%20all%20subsequent%20files%20will%20error%20the%20same%20way.%20Is%20there%20a%20way%20to%20find%20out%20which%20file%20is%20causing%20it%3F%20I'm%20not%20sure%20where%20in%20the%20grand%20scheme%20of%20things%20I%20should%20look%20for%20file%20order.%20Any%20insight%20would%20be%20immensely%20helpful!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20the%20very%20first%20file%20and%20query%20are%20not%20getting%20an%20error.%20However%2C%20there%20are%20a%20jumble%20of%20non-linear%20queries%20and%20files%20from%20there%20on%20out%20(i.e.%2C%20file%20%232%20in%20SharePoint%20doesn't%20match%20query%20%232%20in%20Power%20Query%20-%20which%20one%20matters%20here%3F)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-320485%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Visitor

Hi all,

 

I have been working with Power Query as a way to combine datasets from two systems then plugging it into PowerBI for about a year now. This month, when I ran and refreshed data for 2018 year-end, I am getting an error I have never gotten before:

 

DataFormaterror Excel PQ.PNG

There are always a few things to tweak here or there like adjusting the files that have been added to the source (SharePoint) so that it narrows down to the correct file. That is not the problem here. It seems to only error out at the step where the table is expanded, before any of the transformation steps. I have gone through my source files and checked their columns for data type and they're all normal. A few of the tables weren't updated at all, however, I understand that if one file errors out - all subsequent files will error the same way. Is there a way to find out which file is causing it? I'm not sure where in the grand scheme of things I should look for file order. Any insight would be immensely helpful!

 

Note: the very first file and query are not getting an error. However, there are a jumble of non-linear queries and files from there on out (i.e., file #2 in SharePoint doesn't match query #2 in Power Query - which one matters here?)

0 Replies