+ Reply to Thread
Results 1 to 31 of 31

Power Query import issue

  1. #1
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Power Query import issue

    I am trying to import API data from cryptopia.co.nz

    They have publicly issued their APIs here:
    https://www.cryptopia.co.nz/Forum/Thread/255

    Let's say I choose to import this one:
    https://www.cryptopia.co.nz/api/GetCurrencies

    When I do that, I get this.

    How to solve it?
    Attached Images Attached Images

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    maybe it will help: Connection to an API with Power Query

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    duplicated

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power Query import issue

    It works OK in PQ in 2010.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    It seems Get&Transform (PowerQuery) 2016 has some bug
    or
    update you version of Office
    or try PowerBI
    Last edited by sandy666; 01-11-2018 at 09:15 AM.

  6. #6
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by xlnitwit View Post
    It works OK in PQ in 2010.
    I installed 2010 version and now my 2016 is gone, is it possible somehow to have different versions of MS office at the same time on the same device?

  7. #7
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by sandy666 View Post
    It seems Get&Transform (PowerQuery) 2016 has some bug
    or
    update you version of Office
    or try PowerBI
    Updated my excel 2016, problem still persists.
    Is Power BI some enhanced version of PQ?

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    I got everything from cryptopia via PBI so try
    enough paste url....

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power Query import issue

    You can have many versions of Office at once but
    -install them from oldest version to newest
    -only one click to run version can be installed
    -they should really all be either 32 or 64 bit. Mixing and matching is difficult and can cause problems.

  10. #10
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by xlnitwit View Post
    You can have many versions of Office at once but
    -only one click to run version can be installed
    What does that mean?

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power Query import issue

    There are basically two kinds of installation- either running a full installation file (physical media like a DVD or a large download) or using a web installer (which only applies to Office 2013 and later). For the latter, you can only have one version.

  12. #12
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by sandy666 View Post
    I got everything from cryptopia via PBI so try
    enough paste url....
    Okay, I tried PBI, got exactly the same error as with excel

  13. #13
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by xlnitwit View Post
    There are basically two kinds of installation- either running a full installation file (physical media like a DVD or a large download) or using a web installer (which only applies to Office 2013 and later). For the latter, you can only have one version.
    Im pretty sure my excel 2016 version is of that click to run one.
    But my excel 2010 version is a large download (I assume).

    So, in order to have those two running separately and with no issues, I have to uninstall my current 2016 one, then install 2010 and then install 2016 again?

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    I checked few services online for json2xml and every service says: error on 24th or 25th column and something wrong with http header from this url.
    So maybe try to download this GetCurrencies.json file and play with it.
    Last edited by sandy666; 01-12-2018 at 07:17 AM.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Power Query import issue

    Here are files (pbix, json and xlsm)

    I know this is not a LiveData but it's better than nothing
    Attached Files Attached Files
    Last edited by sandy666; 01-12-2018 at 08:09 AM. Reason: GetMarkets added

  16. #16
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Power Query import issue

    Quote Originally Posted by kao View Post
    Im pretty sure my excel 2016 version is of that click to run one.
    But my excel 2010 version is a large download (I assume).

    So, in order to have those two running separately and with no issues, I have to uninstall my current 2016 one, then install 2010 and then install 2016 again?
    That is the safest option.

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    it should work because this url doesn't require parameters

    in steps:
    • create blank query
    • advanced editor
    • paste this code, Done


    Please Login or Register  to view this content.
    • click List in value column
    • then Menu -> To Table
    • then Expand (uncheck: use original column name as prefix), Ok
    • close&load

    you can try with TradePairs (no param)

    the rest sources from this site need parameters
    Last edited by sandy666; 01-12-2018 at 07:15 PM.

  18. #18
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by sandy666 View Post
    it should work because this url doesn't require parameters

    in steps:
    • create blank query
    • advanced editor
    • paste this code, Done


    Please Login or Register  to view this content.
    • click List in value column
    • then Menu -> To Table
    • then Expand (uncheck: use original column name as prefix), Ok
    • close&load

    you can try with TradePairs (no param)

    the rest sources from this site need parameters
    Is it possible to make it refresh automatically so it would be live data?

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    You can set it here:

    refresh.jpg

  20. #20
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by sandy666 View Post
    it should work because this url doesn't require parameters

    in steps:
    • create blank query
    • advanced editor
    • paste this code, Done


    Please Login or Register  to view this content.
    • click List in value column
    • then Menu -> To Table
    • then Expand (uncheck: use original column name as prefix), Ok
    • close&load

    you can try with TradePairs (no param)

    the rest sources from this site need parameters

    Unfortunately, I get the same error..
    DataSource.Error: The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF
    Details:
    https://www.cryptopia.co.nz/api/GetCurrencies

  21. #21
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    It's not my fault. Service is
    try another way, maybe vb

    I tried another service with well created json and it works

    have a nice day

  22. #22
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Quote Originally Posted by sandy666 View Post
    It's not my fault. Service is
    try another way, maybe vb

    I tried another service with well created json and it works

    have a nice day
    I know it's not your fault. Any idea why it does not work for me? What excel version do you use here?
    by VB you mean Visual Basic?

  23. #23
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Power Query import issue

    2016 64 bit and I got today the same error as you. At night it worked very well.
    Now I did test with TradePairs and all data is loaded but when I refreshed data again I got error so IMHO this server cannot be used as LiveData

    and yes, in short: vb visual basic, vba visual basic for applications

    and you don't need quote whole post!
    Last edited by sandy666; 01-13-2018 at 06:44 AM.

  24. #24
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    yes, that's what I am trying to do, solve it with VBA, but I am completely new to it, so it does not look that easy

  25. #25
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    I can't help you with this because I am far away from vba, so good luck and have a nice day.

  26. #26
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    You too. You said you are using MS Office 64 bit, whereas my version is x32.
    What are the main differences between those two and where can I get x64?
    Last edited by kao; 01-13-2018 at 05:21 PM.

  27. #27
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    Last edited by sandy666; 01-13-2018 at 05:18 PM.

  28. #28
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    Sorry. edited.

    When I enter my details and my product key, it gives me this download link and it is x32, its does not let me choose the architecture.

    Does this mean my key is only for x32 version and I cannot get x64?
    Attached Images Attached Images

  29. #29
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    Probably you've windows 32 bit

    64.jpg

  30. #30
    Forum Contributor
    Join Date
    06-18-2017
    Location
    Lithuania
    MS-Off Ver
    2016
    Posts
    177

    Re: Power Query import issue

    that's the thing my windows is x64

  31. #31
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query import issue

    You didn't read all information from previous link so read here: “Office (64-bit or 32-bit) couldn’t be installed” error when trying to install

    if you've more questions about Office or Windows create new thread in appropriate part of forum

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  2. Power Query Issue
    By TheDtox in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 12-27-2017, 05:43 AM
  3. Power Query Issue
    By TheDtox in forum Access Tables & Databases
    Replies: 3
    Last Post: 12-27-2017, 02:28 AM
  4. Replies: 2
    Last Post: 12-08-2017, 03:13 PM
  5. Power Query Import Binary Data Type
    By brent_milne in forum Excel General
    Replies: 0
    Last Post: 08-13-2015, 09:46 AM
  6. Excel web query import issue with blakslash (/'') symbol
    By murali.uppala in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2014, 08:23 AM
  7. [SOLVED] Power Pivot SQL Data Import - How to update import filter
    By minnesotaart in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-29-2013, 12:39 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1