Retrieving data from public API using Web.Contents fails on all but 1 pc

[JeffWeir]

Hi folks. I'm pulling data from an API from this page: https://www.educationcounts.govt.nz/data-services/directories/api-new-zealand-schools

It works absolutely fine on one PC, but if I try it on any other PC, I get an error "The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF".   Initially the preview populates just fine, but if I hit Refresh Preview I get an error on the first step, and If I hit "Close and Apply" I get the same error.

I'd be interested to find out if anyone else can get this code to load to the canvas.

Here's my M query, and below that is another approach that *does* work.

This only works on one PC:

let
    Source = Json.Document(Web.Contents("https://catalogue.data.govt.nz/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%22bdfe0e4c-1554-4701-a8fe-ba1c8e0cc2ce%22")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{2}[Value],
    records = Value[records],
    #"Converted to Table1" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Māori", "Education_Region", "Decile", "Telephone", "Other", "Pacific", "Latitude", "International", "Email", "Area_Unit", "Add2_City", "Fax", "Regional_Council", "Add2_Suburb", "Col_Id", "European", "Territorial_Authority", "Ward", "Māori_Electorate", "Add2_Line1", "Org_Name", "Definition", "Add1_City", "URL", "Urban_Area", "Col_Name", "Longitude", "CoEd_Status", "Contact1_Name", "Local_Office_Name", "General_Electorate", "Total", "Roll_Date", "_id", "_full_text", "Add1_Suburb", "Authority", "Org_Type", "Asian", "MELAA", "Add1_Line1", "Add2_Postal_Code", "School_Id"}, {"Māori", "Education_Region", "Decile", "Telephone", "Other", "Pacific", "Latitude", "International", "Email", "Area_Unit", "Add2_City", "Fax", "Regional_Council", "Add2_Suburb", "Col_Id", "European", "Territorial_Authority", "Ward", "Māori_Electorate", "Add2_Line1", "Org_Name", "Definition", "Add1_City", "URL", "Urban_Area", "Col_Name", "Longitude", "CoEd_Status", "Contact1_Name", "Local_Office_Name", "General_Electorate", "Total", "Roll_Date", "_id", "_full_text", "Add1_Suburb", "Authority", "Org_Type", "Asian", "MELAA", "Add1_Line1", "Add2_Postal_Code", "School_Id"})
in
 

This works:

let
    Source = Web.BrowserContents("https://catalogue.data.govt.nz/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%22bdfe0e4c-1554-4701-a8fe-ba1c8e0cc2ce%22"),
    Table = Html.Table(Source, {{"Results", "pre"}}),
    ConvertToJson=Json.Document(Table{0}[Results]),
    #"Converted to Table" = Record.ToTable(ConvertToJson),

    Value = #"Converted to Table"{2}[Value],
    records = Value[records],
    #"Converted to Table1" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Māori", "Education_Region", "Decile", "Telephone", "Other", "Pacific", "Latitude", "International", "Email", "Area_Unit", "Add2_City", "Fax", "Regional_Council", "Add2_Suburb", "Col_Id", "European", "Territorial_Authority", "Ward", "Māori_Electorate", "Add2_Line1", "Org_Name", "Definition", "Add1_City", "URL", "Urban_Area", "Col_Name", "Longitude", "CoEd_Status", "Contact1_Name", "Local_Office_Name", "General_Electorate", "Total", "Roll_Date", "_id", "_full_text", "Add1_Suburb", "Authority", "Org_Type", "Asian", "MELAA", "Add1_Line1", "Add2_Postal_Code", "School_Id"}, {"Māori", "Education_Region", "Decile", "Telephone", "Other", "Pacific", "Latitude", "International", "Email", "Area_Unit", "Add2_City", "Fax", "Regional_Council", "Add2_Suburb", "Col_Id", "European", "Territorial_Authority", "Ward", "Māori_Electorate", "Add2_Line1", "Org_Name", "Definition", "Add1_City", "URL", "Urban_Area", "Col_Name", "Longitude", "CoEd_Status", "Contact1_Name", "Local_Office_Name", "General_Electorate", "Total", "Roll_Date", "_id", "_full_text", "Add1_Suburb", "Authority", "Org_Type", "Asian", "MELAA", "Add1_Line1", "Add2_Postal_Code", "School_Id"})
in
    #"Expanded Column1"

[v-lili6-msft]

hi, @JeffWeir 

Please check if below similar post could help.

https://community.powerbi.com/t5/Desktop/DataSource-Error-The-server-committed-a-protocol-violation/...

Regards,

Lin