Power Query From Table Or Range Error

[TamTam]

I have seemingly randomly started getting an error message with some of my Power Queries that reads as follows:

The command " isn't supported.

For one of my workbooks, I completely rebuilt the Power Query table and related chart from scratch which initially solved the problem. A couple of weeks later, I opened the workbook and received the same error message.

I have attempted to research this problem, but I cannot find any info. I'm guessing that is because of the quotation marks/apostrophe within the error message?

Any help would be greatly appreciated.

Thank you.

 

[Ken Puls]

Hi TamTam,

Is it possible to post the M code of your query here?

• Go to Power Query -> View -> Advanced Editor and copy all the code
• Paste it here and wrap it with code tags ( Edit code - select code - click the #button.)

We can take a look and see if it's anything in the code that is suspicious.

It would also be helpful to know your full Excel details (from File -> Account) Can you share:

• The product SKU (right under "Subscription Product)
• The Version (under About Excel)
• The Build (after the version in About Excel)

 

[TamTam]

let
    Source = Excel.CurrentWorkbook(){[Name="tblJobs"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status", type text}, {"Job", type text}, {"Last Name", type text}, {"Date Sold/Lost", type date}, {"Tyler", type number}, {"Beau", type number}, {"Karen", type number}, {"Total", type number}, {"Days in Design", Int64.Type}, {"Date of Measure", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Status] = "Sold")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",0,null,Replacer.ReplaceValue,{"Tyler"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",0,null,Replacer.ReplaceValue,{"Beau"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",0,null,Replacer.ReplaceValue,{"Karen"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "High Boundary", each 70),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Inside Boundary", each if [Days in Design] > 70 then 70 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Outside Boundary", each if [Days in Design] > 70 then [Days in Design] else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column1",{{"Date Sold/Lost", Order.Ascending}})
in
    #"Sorted Rows"

About Excel: Version 1907 (Build 11901.20176 Click-to-Run) Microsoft Excel for Office 365 MSO (16.0.11901.20070) 32-Bit

Does this provide the information you asked for? Again, thank you.

 

[AliGW]

When, where and how are you seeing the error? There does not appear to by anything wrong with the code - it does not throw any syntax errors.

 

[Bob Phillips]

Can you post the full workbook?

 

[TamTam]

So I have one table that my users input data into and I've made this power query table off of that data source. I am seeing this error when I try to refresh the power query table by either right-clicking and choosing refresh or clicking the refresh button on the ribbon. I receive the error message and then no additional, newly inputted data is pulled into my power query table. When I open the query editor, it pulls all of the data, including new data, into the editor, but does not actually load that new data into the table when I close and load.

I have attached the file to this post.

Thank you

 

[AliGW]

It is this line in your VBA that is the issue, not PQ:

lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)

 

[Bob Phillips]

What do you think is wrong with that code?

 

[Bob Phillips]

This is most odd.

If I right-click on any of the queries and Refresh in the Queries pane, no problems.

If I right-click on the tables and Refresh, tblJobs2 fails and tblJobs3 is okay.

If I run the VBA UpdatePowerQueries procedure, tblJobs2 seems to work and tblJobs3 fails.

 

[Bob Phillips]

I think I have found it.

If you go into Connections, and look at Properties for tblJobs2, open up the Definition tab, and you will see there is nothing in Command Text box. That should say Select * From [tblJobs2], and if you insert it the queries should be okay. No idea how that got missed/lost.

 

[TamTam]

So for me, there is no method that works to refresh tblJobs2. Yes, when I right click on the queries & connections pane and choose refresh, there is no error message, but it doesn't actually update the data. If you want to check that out for yourself, Row 11 of Data Entry/tblJobs is equal to row 3 of Admin Jobs/tblJobs2 & 3. As for your suggestion, I see what you're saying that the Command Text box for Query Properties for Query tblJobs2 is empty, but its also greyed out and I don't know how to edit it. Where would I go to edit it?

As for my VBA code, I'm trying to get my users to input data in Data Entry/tblJobs and then have that data reflected on the chart without them having to do click Refresh or any other button. If the code is causing the problem, do you have any other suggestions?

Thank you.

 

[Bob Phillips]

It wasn't greyed out for me, I edited it. Shall I post you back that workbook?

 

[TamTam]

So, I don't need to you post it back to me because I created tblJobs3 as a duplicate of tblJobs2 and mapped my named ranges and chart data to that table so I could keep using the workbook. The reason why I reached out to this forum is that this is the second time I've had to re-make this particular power query and the third workbook overall in which I've experienced this same error. I was hoping to identify what is causing this to happen. All three of these workbooks do use some form of vba to refresh at various times. Is it generally a problem to refresh power queries via vba?

 

[Bob Phillips]

No, I do it all of the time. There is nothing in your code that tries to amend the connection, so as I said, I cannot see how the Command Text disappeared.

 

[TamTam]

Okay, I definitely appreciate your time - at least perhaps if this happens again I'll be able to fix the Command Text. Thank you.

 

[Bob Phillips]

You can do it via VBA, like so

Dim conn As WorkbookConnection
Dim qry As WorkbookQuery
Dim qrycode As String
Dim pos As Long
    
    For Each qry In ThisWorkbook.Queries
    
        Set conn = Nothing
        On Error Resume Next
        Set conn = qry.Parent.Parent.Connections("Query - " & qry.Name)
        On Error GoTo 0
        If Not conn Is Nothing Then
        
            pos = InStr(1, conn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
            If pos > 0 Then
            
                With conn.OLEDBConnection
                
                    If .CommandText = vbNullString Then .CommandText = "Select * From [" & qry.Name & "]"
                End With
            End If
        End If
    Next qry

 

[AliGW]

What do you think is wrong with that code?

if you comment that line out, the error message reported no longer appears and the table refreshes.

 

[TamTam]

Commenting out that line does not fix the problem on my end. In fact, deleting all of the vba in the workbook does not fix the problem for me. Nonetheless, I appreciate you taking the time to look at it.

 

[Bob Phillips]

if you comment that line out, the error message reported no longer appears and the table refreshes.

That is because the connection was the problem, not the VBA code. Commenting it out just masked the problem because the connection was not then referenced. If you didn't refresh the query, the refresh didn't fail either .