MultipleUnclassified/Trusted error

If you’ve been using my fnGetParameter function to build dynamic content into your queries, you may have noticed that passing some variables recently started triggering an error message that reads as follows:

We couldn’t refresh the connection ‘Power Query – Categories’. Here the error message we got:

Query ‘Staging-Categories’ (Step ‘Added Index’) used ‘SingleUnclassified/Trusted/CurrentWorkbook/’ data when last evaluated, but now is attempting to use ‘MultipleUnclassified/Trusted’ data.

If you’re like me, that was promptly followed by cursing and head scratching in the pursuit of a solution.  (I would suggest clicking the “was this information helpful?” link in the bottom of the error, and submitting a “no”.

image

As far as I can tell, this error started showing up in Power Query around v 2.21 or so.  (I don’t have an exact version.)  I know that it existed in 2.22 for sure, and still exists in version 2.23.

Triggering the MultipleUnclassified/Trusted error

Triggering the error isn’t difficult, but it does seem to show up out of the blue.  To throw it, I built a solution that uses my fnGetParameter function to read the file path from a cell, and feed it into a query as follows:

let
fPath = fnGetParameter("FilePath"),
    Source = Csv.Document(File.Contents(fPath & "SalesCategories.txt"),null,",",null,1252),
#"First Row as Header" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"POSPartitionCode", Int64.Type}, {"POSCategoryCode", type text}, {"POSCategoryDescription", type text}, {"POSReportingGroupCode", type text}, {"POSTaxTypeCode", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Lnk_Category", each Text.Combine({Text.From([POSPartitionCode], "en-US"), [POSCategoryCode]}, "-"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"POSCategoryCode"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1)
in
#"Added Index"

The killer here is that – when you originally build the query – it works just fine!  But when you refresh it you trigger the error.

Fixing the MultipleUnclassified/Trusted error

There are two options to fix this particular error:

Option 1

The first method to fix this problem is to avoid the fnGetParameter function all together and just hard code the file paths.  While this works, you cut all dynamic capability from the query that you went to the effort of implementing.  In my opinion, this option is awful.

Option 2

If you want to preserve the dynamic nature of the fnGetParameter function, the only way to fix this error today is to perform the steps below in this EXACT order!

  1. Turn on Fast Combine (Power Query –> Options –> Privacy –> Ignore Privacy Levels)
  2. Save the workbook
  3. Close Excel
  4. Restart Excel
  5. Refresh the query

Each of the steps above are critical steps to ensure that transient caches are repopulated with the “fast combine” option enabled – merely setting the “fast combine” option is not enough on its own.

Expected Bug Fix

Microsoft is aware of this, and has been working on a fix.  I believe (although no guarantees), that it should be released in version 2.24.  Given the update schedule in the past, we should see it any day now.  Fingers crossed, as this is just killing my solutions!

I’ll keep checking the Power Query download page and post back when the new version goes live.

4 thoughts on “MultipleUnclassified/Trusted error

  1. I just noticed that the PowerQuery download page updated the Add-in's publish date but not the version number.

  2. Thanks. You probably tried this already but...I had to wait to get off-work to try it, so I downloaded the file again and diff'ed (fc) it with the 2.23 I grabbed a while back. They're the same. So my best guess is that the date change is indicative of 2.24 soon. Tomorrow? Hope so.

  3. I'm getting this error with a Table.Join operation where the query is references rather than the excel table object

    So this fails....

    let
    Source = RANGE_CORRECTION_APPEND,
    #"Table Join" = Table.Join(Source,{"Index"},MIP_TEST_DATA_APPEND, "Index"})
    in
    #"Table Join"

    and this works....

    let
    Source = Excel.CurrentWorkbook(){[Name="RANGE_CORRECTION_APPEND"]}[Content],
    #"Table Join" = Table.Join(Source,{"Index"},Excel.CurrentWorkbook(){[Name="MIP_TEST_DATA_APPEND"]}[Content],{"Index"})
    in
    #"Table Join"

    Referencing either table without the Table.Join operation works fine. The particular error is slightly different...starting with "Multiple" rather than "Single"...either way it makes no sense!

    "Query 'MIP_TEST_DATA_APPEND' (step 'Added Index') used 'MultipleUnclassified/Trusted' data when last evaluated, but now is attempting to use 'MultipleUnclassified/Trusted' data."

    I would like to use 'connection only' queries as some of the tables are quite large to keep in the worksheet environment. Any help hugely appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.