Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Power Query From Table Or Range Error

  1. #1
    Seeker TamTam's Avatar
    Join Date
    Aug 2019
    Posts
    7
    Articles
    0
    Excel Version
    Microsoft Office 365

    Power Query From Table Or Range Error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,343
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker TamTam's Avatar
    Join Date
    Aug 2019
    Posts
    7
    Articles
    0
    Excel Version
    Microsoft Office 365
    Code:
    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.

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,247
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Can you post the full workbook?

  6. #6
    Seeker TamTam's Avatar
    Join Date
    Aug 2019
    Posts
    7
    Articles
    0
    Excel Version
    Microsoft Office 365
    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
    Attached Files Attached Files

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,247
    Articles
    0
    Excel Version
    Office 365 Subscription
    It is this line in your VBA that is the issue, not PQ:

    Code:
    lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    What do you think is wrong with that code?

  9. #9
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    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.

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    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.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •