"We found extra characters at the end of JSON input" error.

[alibini]

Hi I am new to power queries.
Can anyone explain why I get the error message "We found extra characters at the end of JSON input" on the below Query when I use Excel for Office 365. Is there a way to overcome this?
When I put the JSON code it says it passes without error.
Any help much appreciated.

Advanced Editor Code:

let
    Source = Json.Document(Web.Contents("https://www.bloomberg.com/markets/api/comparison/data-bfix?currencies=AUDUSD,USDILS,EURUSD,USDJPY,GBPUSD,USDMXN,NZDUSD,USDPLN,USDCAD,USDPLN,USDCHF,USDSEK,USDCNH,USDSGD,USDCZK,USDTHB,USDDKK,USDTRY,USDHKD,USDZAR,USDHUF,XAUUSD,EURJPY,EURBGN,EURCZK,EURDKK,EURGBP,EURHUF,EURPLN,EURRON,EURSEK,EURCHF,EURNOK,EURHRK,EURRUB,EURTRY,EURAUD,EURBRL,EURCAD,EURCNY,EURHKD,EURILS,EURMXN,EURNZD,EURSGD,EURTHB,EURZAR,EURIDR,EURINR,EURKRW,EURMYR,EURPHP,AUDCAD,BRLCAD,CNYCAD,HKDCAD,INRCAD,IDRCAD,JPYCAD,MYRCAD,MXNCAD,NZDCAD,NOKCAD,PENCAD,RUBCAD,SARCAD,SGDCAD,ZARCAD,KRWCAD,SEKCAD,CHFCAD,TWDCAD,THBCAD,TRYCAD,GBPCAD,GBPJPY,CADJPY,CHFJPY,AUDJPY&dateTime=2020-05-15T06:00:00Z")),
    fieldDataCollection = Source[fieldDataCollection],
    #"Converted to Table" = Table.FromList(fieldDataCollection, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "rate"}, {"Column1.id", "Column1.rate"})
in
    #"Expanded Column1"

 

[smozgur]

(Note: Following is not the complete solution since data is still not being loaded to the worksheet even it loads correctly in the Power Query Editor by applying the necessary header variable as explained below. It should be still something about request headers, but I can't find what it is. Perhaps solving this part might help to find the final solution.)

It is because the web source doesn't return JSON data but HTML error page. The API requires cookie header information, otherwise it considers the client as a robot - see the actual response by adding the URL as a new query From Web, the return is an HTML error page.
To send the request header, I added [Headers=[cookie=""]] as the options value of the Web.Contents:

let
    Source = Json.Document(Web.Contents("https://www.bloomberg.com/markets/api/comparison/data-bfix?currencies=AUDUSD,USDILS,EURUSD,USDJPY,GBPUSD,USDMXN,NZDUSD,USDPLN,USDCAD,USDPLN,USDCHF,USDSEK,USDCNH,USDSGD,USDCZK,USDTHB,USDDKK,USDTRY,USDHKD,USDZAR,USDHUF,XAUUSD,EURJPY,EURBGN,EURCZK,EURDKK,EURGBP,EURHUF,EURPLN,EURRON,EURSEK,EURCHF,EURNOK,EURHRK,EURRUB,EURTRY,EURAUD,EURBRL,EURCAD,EURCNY,EURHKD,EURILS,EURMXN,EURNZD,EURSGD,EURTHB,EURZAR,EURIDR,EURINR,EURKRW,EURMYR,EURPHP,AUDCAD,BRLCAD,CNYCAD,HKDCAD,INRCAD,IDRCAD,JPYCAD,MYRCAD,MXNCAD,NZDCAD,NOKCAD,PENCAD,RUBCAD,SARCAD,SGDCAD,ZARCAD,KRWCAD,SEKCAD,CHFCAD,TWDCAD,THBCAD,TRYCAD,GBPCAD,GBPJPY,CADJPY,CHFJPY,AUDJPY&dateTime=2020-05-15T06:00:00Z",
[Headers=[cookie=""]])),
    fieldDataCollection = Source[fieldDataCollection],
    #"Converted to Table" = Table.FromList(fieldDataCollection, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "rate"}, {"Column1.id", "Column1.rate"})
in
    #"Expanded Column1"

Unfortunately even it returns correct data in Power Query editor now, it still doesn't load it to the worksheet.