none
What prevents a query form loading? RRS feed

  • Question

  • Have a three queries that are independent from one-another.  Append two of them then merge the third.  As part of the merge additional clean up is performed.  Close and Load leads to spinning dots that have not finished loading in more than 2 hours.   Excel lower left corner say "Running a background query ..."  Expect 370-records of ~12 fields.  Any thoughts would be appreciated.

    It seems like, if a query fails to load in excel, that trying to edit the query (which is not allowed since it it loading) then close and load again will cause the load to unhang and eventually to complete the load. Loading to a sheet.


    • Edited by GeneQuery2 Tuesday, September 4, 2018 3:45 PM clarify
    Tuesday, September 4, 2018 3:18 PM

Answers

  • Yes, dealing with certain unicode characters can be challenging, especially when they aren't visually distinct from their ASCII counterparts.

    You can replace unicode 160 (hex A0) with an expression like the following:

    = Text.Replace(text, "#(00A0)", " ")

    A good way to view the character codes for a given piece of text is to use the following M expression:

    = List.Transform(Text.ToList(text), each Character.ToNumber(_))

    Ehren

    Thursday, September 20, 2018 6:02 PM
    Owner

All replies

  • Hi Gene. Were you able to resolve this?

    Ehren

    Thursday, September 13, 2018 5:02 PM
    Owner
  • Thanks Ehren.

    I moved on.  I followed your other suggestions and implemented the scheme to avoid using replace, and avoid unnecessary sorting.  Likewise I never load a query to a sheet unless absolutely necessary.  I have split my project into 5 workbooks.  I fetch data from one book to the next as required.

    One thing, the unicode 160 (the equivalent space code 32) isn't obvious.  Need to be ab le to trim or clean that code to space equivalent.

    Thanks, Gene


    elc

    Wednesday, September 19, 2018 12:18 AM
  • Yes, dealing with certain unicode characters can be challenging, especially when they aren't visually distinct from their ASCII counterparts.

    You can replace unicode 160 (hex A0) with an expression like the following:

    = Text.Replace(text, "#(00A0)", " ")

    A good way to view the character codes for a given piece of text is to use the following M expression:

    = List.Transform(Text.ToList(text), each Character.ToNumber(_))

    Ehren

    Thursday, September 20, 2018 6:02 PM
    Owner