none
DataFormat.Error: External table is not in the expected format RRS feed

  • Question

  • Hello.  I'm having trouble in PowerQuery with the above error, and I don't see anything in this forum that matches my problem exactly.

    Here is what I'm trying to do:

    • Load several XLSB files with a FROM FOLDER
    • Filter the list of sheets to one particular one that has the data I need.  The actual command that causes the error is:  Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "Labor Input"))

    I have tried this with different files, different directories, I have copied the query file, I have rebuilt the query file from scratch and nothing seems to help.  The problem does not seem to happen when I first write the query, but when I exit out and back in I consistently get the error.

    I have pasted the entire query below (none of which is confidential).

    Any ideas on this would be greatly appreciated.

    thanks,

    Brian

    let
        Source = Folder.Files("C:\Users\Brian\SkyDrive\Documents\Working\Model\Model Files"),
        #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Excel.Workbook([Content])),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Custom.Name", "Custom.Data"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "Labor Input")),
        #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184", "Column185", "Column186", "Column187", "Column188", "Column189", "Column190", "Column191", "Column192", "Column193", "Column194", "Column195", "Column196", "Column197", "Column198", "Column199", "Column200", "Column201", "Column202", "Column203", "Column204", "Column205", "Column206", "Column207", "Column208", "Column209", "Column210", "Column211", "Column212", "Column213", "Column214", "Column215", "Column216", "Column217", "Column218", "Column219", "Column220", "Column221", "Column222", "Column223", "Column224", "Column225", "Column226", "Column227", "Column228", "Column229", "Column230", "Column231", "Column232", "Column233", "Column234", "Column235", "Column236", "Column237", "Column238", "Column239", "Column240", "Column241", "Column242", "Column243", "Column244", "Column245", "Column246", "Column247", "Column248", "Column249", "Column250", "Column251", "Column252", "Column253", "Column254", "Column255"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184", "Column185", "Column186", "Column187", "Column188", "Column189", "Column190", "Column191", "Column192", "Column193", "Column194", "Column195", "Column196", "Column197", "Column198", "Column199", "Column200", "Column201", "Column202", "Column203", "Column204", "Column205", "Column206", "Column207", "Column208", "Column209", "Column210", "Column211", "Column212", "Column213", "Column214", "Column215", "Column216", "Column217", "Column218", "Column219", "Column220", "Column221", "Column222", "Column223", "Column224", "Column225", "Column226", "Column227", "Column228", "Column229", "Column230", "Column231", "Column232", "Column233", "Column234", "Column235", "Column236", "Column237", "Column238", "Column239", "Column240", "Column241", "Column242", "Column243", "Column244", "Column245", "Column246", "Column247", "Column248", "Column249", "Column250", "Column251", "Column252", "Column253", "Column254", "Column255"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.Data",{"Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Column170", "Column171", "Column172", "Column173", "Column174", "Column175", "Column176", "Column177", "Column178", "Column179", "Column180", "Column181", "Column182", "Column183", "Column184", "Column185", "Column186", "Column187", "Column188", "Column189", "Column190", "Column191", "Column192", "Column193", "Column194", "Column195", "Column196", "Column197", "Column198", "Column199", "Column200", "Column201", "Column202", "Column203", "Column204", "Column205", "Column206", "Column207", "Column208", "Column209", "Column210", "Column211", "Column212", "Column213", "Column214", "Column215", "Column216", "Column217", "Column218", "Column219", "Column220", "Column221", "Column222", "Column223", "Column224", "Column225", "Column226", "Column227", "Column228", "Column229", "Column230", "Column231", "Column232", "Column233", "Column234", "Column235", "Column236", "Column237", "Column238", "Column239", "Column240", "Column241", "Column242", "Column243", "Column244", "Column245", "Column246", "Column247", "Column248", "Column249", "Column250", "Column251", "Column252", "Column253", "Column254", "Column255", "Content", "Custom.Name"}),
        #"Removed Columns2" = Table.RemoveColumns(#"Removed Columns1",{"Column4", "Column5", "Column9"}),
        #"Kept First Rows" = Table.FirstN(#"Removed Columns2",200),
        #"Filtered Rows1" = Table.SelectRows(#"Kept First Rows", each ([Column1] = "Labor")),
        #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Name", "File"}, {"Column1", "Labor"}, {"Column2", "Billable"}, {"Column3", "Phase"}, {"Column6", "Shore"}}),
        #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns",{"Column7"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Column8", "Level"}, {"Column10", "Bill Rate"}, {"Column11", "Custom Rate"}, {"Column12", "Cost Rate"}, {"Column13", "Custom Cost"}, {"Column14", "GM"}, {"Column15", "Description"}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"File", "Labor", "Billable", "Phase", "Shore", "Level", "Bill Rate", "Custom Rate", "Cost Rate", "Custom Cost", "GM", "Description"}, "Attribute", "Value"),
        #"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}, {"Value", "Allocation"}}),
        #"Merged Queries" = Table.NestedJoin(#"Renamed Columns2",{"Month"},MonthLookup,{"Name"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MonthAdj"}, {"NewColumn.MonthAdj"}),
        #"Removed Columns4" = Table.RemoveColumns(#"Expanded NewColumn",{"Month"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns4",{"File", "Labor", "Billable", "Phase", "Shore", "Level", "Bill Rate", "Custom Rate", "Cost Rate", "Custom Cost", "GM", "Description", "NewColumn.MonthAdj", "Allocation"}),
        #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns",{{"NewColumn.MonthAdj", "Month"}}),
        #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns3", each ([Custom Rate] = "$-"))
    in
        #"Filtered Rows2"

    Tuesday, November 24, 2015 6:14 PM

Answers

  • Hi BrianGGG. Without a file to examine, we can only guess. But this is likely due to a bug in the ACE provider PQ uses to read xlsb files.

    If possible, avoid using xlsb for this scenario.

    Ehren

    Thursday, December 3, 2015 9:11 PM
    Owner

All replies

  • Perhaps try moving the SelectRows statement after your expansion of Custom.Data, perhaps that function does not like nested tables.
    Wednesday, November 25, 2015 6:46 PM
  • You could also try to edit the expand-expression: So many expanded columns that will be deleted in the next step.

    Just keep the columns in the expand-statement that you really want to keep. Then delete the "Removed Columns" steps.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by BrianGGG Thursday, November 26, 2015 12:50 AM
    • Unmarked as answer by BrianGGG Thursday, November 26, 2015 1:07 AM
    Wednesday, November 25, 2015 8:36 PM
    Moderator
  • Update:  I tried both suggestions, the query finished, data posted back to excel, and I thought everything was great.

    Then I closed the Excel file, returned, and the error is back...

    I did, however, save both XLSB files to XLSM files and re-ran the same query.  It finished in a blink (as opposed to a noticeable pause with XLSB) and there were no errors.  This appears to be either a problem with the XLSB format or something inside my files that gets wiped out on the re-save.

    Anyone have any ideas:

    • How can I get more information on WHICH table is causing a problem in the XLSB file (there are many and I don't have complete control over the file)?
    • How can I determine if I have the most current ODBC drivers for Excel?

    Thanks

    • Edited by BrianGGG Thursday, November 26, 2015 12:46 PM
    Thursday, November 26, 2015 12:47 AM
  • Just renewing this one up to the top in the hope that someone can help me now that Thanksgiving is over...

    Thanks in Advance,

    Brian

    Tuesday, December 1, 2015 2:16 AM
  • Hi Brian. Would it be possible for you to post a link to a sample of one of your xlsb files (perhaps containing anonymized data if necessary)?

    Ehren

    Tuesday, December 1, 2015 5:51 PM
    Owner
  • Ehren,

    Thanks very much for responding.

    Unfortunately, the file in question is extremely proprietary (in both data and structure).  If I anonymized it, there would be nothing left.

    I realize that makes things very hard to diagnose, but any thoughts on this would be sincerely appreciated.

    Any ideas on how I can determine if I have the right Excel drivers?

    Thursday, December 3, 2015 1:47 AM
  • Hi BrianGGG. Without a file to examine, we can only guess. But this is likely due to a bug in the ACE provider PQ uses to read xlsb files.

    If possible, avoid using xlsb for this scenario.

    Ehren

    Thursday, December 3, 2015 9:11 PM
    Owner