POST API leading to 400 error

Anonymous · ‎08-30-2018

The POST function of Web.Contents is not fully working. Please see here for further details.

Anonymous · ‎08-22-2018

I have converted an API query from a functioning GET request to a nonfunctioning POST request.

The POST request uses a function to call the service. The function is:

let functionName = (param1 as text) =>
    
    let
        Source = Json.Document(Web.Contents("https://data.police.uk/api/crimes-street/all-crime", [Content = Text.ToBinary(param1)]))
    in
    Source

in 
functionName

An example of param1 (the latitude and longitudes of a search area) is:

poly=51.333334,0.538979:51.334288,0.537429:51.336624,0.534575:51.336066,0.532365:51.334182,0.527714:51.334176,0.527285:51.334598,0.526137:51.33534,0.525435:51.335913,0.523858:51.336925,0.52338:51.33769,0.522:51.33865,0.521602:51.339395,0.520975:51.339126,0.518314:51.339926,0.516449:51.340587,0.51578:51.340406,0.513506:51.342105,0.512985:51.343906,0.513111:51.344699,0.513949:51.344515,0.514418:51.344811,0.514936:51.346791,0.516382:51.347798,0.518048:51.347762,0.518405:51.348778,0.518711:51.349395,0.520096:51.349255,0.520342:51.34942,0.520694:51.349725,0.520402:51.349921,0.520591:51.349884,0.520977:51.350826,0.521778:51.350391,0.523407:51.35049,0.524993:51.349502,0.525358:51.349597,0.526382:51.349035,0.527615:51.348231,0.528202:51.347518,0.529829:51.347128,0.530364:51.347345,0.530854:51.346576,0.532478:51.346223,0.53209:51.34613,0.532684:51.345932,0.532583:51.345889,0.532805:51.345275,0.532822:51.344953,0.532296:51.344236,0.533508:51.343934,0.532929:51.343811,0.533082:51.343328,0.53209:51.34349,0.53189:51.343261,0.531381:51.343077,0.531545:51.342975,0.530327:51.342276,0.530832:51.341784,0.530885:51.341887,0.531426:51.340858,0.532404:51.342165,0.534552:51.343346,0.537226:51.343414,0.537988:51.34411,0.537598:51.34436,0.538224:51.344081,0.539008:51.341823,0.538757:51.339751,0.537854:51.339076,0.537797:51.338139,0.53791:51.336394,0.538586:51.33454,0.538398:51.333334,0.538979

The API documentation is available here

Any help would be appreciated.

Anonymous · ‎08-23-2018

@Anonymous,

Do you want to create parameter for poly? If so, please use the following code.

let functionName = (param1 as text) =>


let
    Source = Json.Document(Web.Contents("https://data.police.uk/api/crimes-street/all-crime?poly="&param1&"")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

in

functionName

Regards,
Lydia

Anonymous · ‎08-23-2018

Hi Lydia

The call needs to be a post request. I tried to make it a post request rather than get by using the Content field in Web.Contents as discussed here.

The call however does not work and I cannot figure out why. (The call needs to be post rather than get as sometimes the URL can get over 4000 characters long, the limit when it needs to be sent as a post request.)

Param1 is currently passed in as a parameter from a table. The table contains the lats and longs of a couple of areas. The code for this table is:

let

//Creates name and ID for the search areas

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslDSUQpIzcvMKy7NSVSK1YlWMgQJBZcU5eenKASVFiXmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PoliceLocationID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PoliceLocationID", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"PoliceLocationID", type text}}),


//GETs latitude and longitude of search area

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "poly", each Function.InvokeAfter(()=>BoundaryFunction([PoliceLocationID]), #duration(0,0,0,0.05))),


//Formats latitude and longitude for use in a search

    #"Split Column by Position" = Table.SplitColumn( #"Added Custom", "poly", Splitter.SplitTextByPositions({0, 1}, true), {"poly", "Custom.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Position",{"Custom.2"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Custom", each "poly="&[poly]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"poly"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "poly"}}),


//Unsuccessfully calls function detailed in my original question (PoliceDataExtract) to extract crime data

     #"Added Custom2" = Table.AddColumn(  #"Renamed Columns", "Crimes", each Function.InvokeAfter(()=>PoliceDataExtract([poly]), #duration(0,0,0,0.05)))

in
   #"Added Custom2"

The code I use to extract the longitude and latitudes (boundary function in the code directly above) is:

let functionName = (param1 as text) =>
let
    
    Source = Json.Document(Web.Contents("https://data.police.uk/api/kent/" & param1 & "/boundary")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"latitude", "longitude"}, {"Column1.latitude", "Column1.longitude"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each [Column1.latitude]&","&[Column1.longitude]&":"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.latitude", "Column1.longitude"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Custom", "Custom - Copy"),
    #"Removed Columns1" = Table.RemoveColumns(#"Duplicated Column",{"Custom - Copy"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom.1", each 1),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom.1"}, {{"Count", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom2", each Table.Column([Count], "Custom")),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Count", "Custom.1"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns2", {"Custom2", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Combined" = Text.Combine({#"Extracted Values"{0}[Custom2]})
in
 #"Combined"
in 
functionName

Cheers

Redmond

Anonymous · ‎08-30-2018

This error is a bug. Using an online tester with the same header and body the API correctly returns the data.

The issue has been posted here.

Anonymous · ‎08-31-2018

@Anonymous,

How do you define the PoliceDataExtract function in Power BI Desktop? What is the detailed error message do you get in Power BI?

Regards,
Lydia

Anonymous · ‎08-31-2018

Anonymous · ‎08-31-2018

Hi Lydia

The police data extract function is the first function in the linked blog post:

let functionName = (param1 as text) =>
    
    let
        Source = Json.Document(Web.Contents("https://data.police.uk/api/crimes-street/all-crime", [Content = Text.ToBinary(param1)]))
    in
    Source

in 
functionName

Anonymous · ‎09-03-2018

@Anonymous,

I make a bit change to your PoliceDataExtract function and your code.

let functionName = (param1 as text) =>


let
    Source = Json.Document(Web.Contents("https://data.police.uk/api/crimes-street/all-crime?poly="&param1&"")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

in

functionName

let

//Creates name and ID for the search areas

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslDSUQpIzcvMKy7NSVSK1YlWMgQJBZcU5eenKASVFiXmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PoliceLocationID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PoliceLocationID", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"PoliceLocationID", type text}}),


//GETs latitude and longitude of search area

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "poly", each Function.InvokeAfter(()=>BoundaryFunction([PoliceLocationID]), #duration(0,0,0,0.05))),


//Formats latitude and longitude for use in a search

    #"Split Column by Position" = Table.SplitColumn( #"Added Custom", "poly", Splitter.SplitTextByPositions({0, 1}, true), {"poly", "Custom.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Position",{"Custom.2"}),


  #"Added Custom2" = Table.AddColumn(  #"Removed Columns1", "Crimes", each Function.InvokeAfter(()=>PoliceDataExtract([poly]), #duration(0,0,0,0.05)))

in
   #"Added Custom2"

I found that the error is caused by the length of poly value returned by BoundaryFunction. For example, I put 8 into the BoundaryFunction and get poly value(51.399112,0.584493:51.398824,0.580915:51.398893,0.568542:51.399457,0.563565:51.399981,0.561146:51.400739,0.559137:51.40138,0.558424:51.406986,0.553596:51.408598,0.55132:51.409556,0.548624:51.413898,0.548843:51.414974,0.54851:51.419798,0.549143:51.419919,0.549119:51.419835,0.548139:51.421212,0.548311:51.422039,0.548194:51.422152,0.548393:51.422861,0.54832:51.426114,0.546486:51.427137,0.549014:51.42844,0.548332:51.429262,0.54811:51.431952,0.544675:51.431856,0.544261:51.434623,0.542665:51.434446,0.541671:51.434197,0.541608:51.432945,0.533814:51.43462,0.531912:51.437129,0.529973:51.437862,0.529914:51.437127,0.527677:51.436002,0.523239:51.437392,0.522376:51.439347,0.520689:51.44047,0.519461:51.441421,0.51883:51.441358,0.517803:51.445192,0.516999:51.445179,0.518979:51.446486,0.519089:51.447668,0.51944:51.449482,0.519236:51.450372,0.519526:51.451025,0.519343:51.452532,0.51998:51.454213,0.520028:51.456144,0.520795:51.455673,0.521779:51.455576,0.522902:51.455991,0.522119:51.45725,0.521205:51.459115,0.521427:51.462079,0.524411:51.462404,0.525021:51.462768,0.524788:51.462845,0.525158:51.463043,0.525088:51.463375,0.525363:51.463613,0.52517:51.463842,0.525344:51.464055,0.525124:51.464217,0.525166:51.464953,0.525754:51.465023,0.525621:51.465248,0.52574:51.465475,0.526105:51.465721,0.525983:51.465981,0.526396:51.466187,0.526016:51.466711,0.526006:51.46688,0.526302:51.468344,0.526868:51.46889,0.527349:51.469301,0.527294:51.469815,0.527771:51.470291,0.527791:51.470648,0.52871:51.471078,0.528373:51.471614,0.526917:51.471227,0.5259:51.470945,0.526014:51.470639,0.526432:51.47041,0.526332:51.470325,0.524524:51.470882,0.524197:51.471458,0.524392:51.472597,0.52444:51.473918,0.524875:51.474385,0.524712:51.474633,0.525103:51.474837,0.525101:51.4755,0.526178:51.475647,0.52809:51.47605,0.528316:51.476256,0.52881:51.476293,0.529608:51.475979,0.530927:51.475536,0.531877:51.475183,0.531879:51.475054,0.532177:51.475091,0.533171:51.475758,0.533583:51.476486,0.533635:51.476977,0.532957:51.477532,0.532972:51.47772,0.532713:51.478164,0.532827:51.478169,0.531894:51.478477,0.531511:51.478642,0.532286:51.478921,0.532204:51.479135,0.532393:51.479245,0.532094:51.479417,0.532146:51.479588,0.531692:51.479792,0.531503:51.479909,0.531574:51.479882,0.531305:51.480171,0.53103:51.480484,0.531178:51.480525,0.531722:51.480709,0.53139:51.481207,0.531305:51.481271,0.531821:51.481821,0.532003:51.481896,0.531344:51.482094,0.53118:51.482244,0.531339:51.482837,0.531114:51.483882,0.532595:51.483918,0.533063:51.484102,0.532592:51.484525,0.532331:51.497999,0.530074:51.500067,0.529886:51.501844,0.529066:51.502225,0.536223:51.502614,0.540368:51.502551,0.545692:51.501497,0.554177:51.501406,0.56466:51.50113,0.567043:51.50094,0.572604:51.501391,0.581168:51.501451,0.586361:51.501196,0.589372:51.501939,0.592411:51.501468,0.594629:51.500867,0.604211:51.500234,0.62557:51.500626,0.633359:51.502864,0.652961:51.503492,0.660136:51.502682,0.670382:51.503158,0.67959:51.486706,0.679766:51.48647,0.682219:51.484657,0.692702:51.484273,0.69415:51.483591,0.694919:51.482739,0.696997:51.482441,0.700748:51.481741,0.704871:51.48069,0.709146:51.480011,0.710616:51.48037,0.711022:51.480009,0.711405:51.478952,0.71485:51.478632,0.71535:51.477394,0.721065:51.477143,0.721966:51.476903,0.722136:51.476707,0.723532:51.475507,0.727786:51.473814,0.732391:51.472927,0.733686:51.471724,0.736053:51.468101,0.738927:51.465778,0.73971:51.460357,0.739254:51.454411,0.736657:51.451222,0.734922:51.448613,0.740648:51.447653,0.73999:51.445918,0.739231:51.443113,0.738679:51.440211,0.737419:51.437207,0.734877:51.435893,0.733158:51.433791,0.729869:51.432123,0.725885:51.429494,0.716811:51.428597,0.712612:51.427967,0.707781:51.427272,0.69388:51.428111,0.687317:51.428466,0.681877:51.428741,0.669793:51.428322,0.665038:51.42693,0.661747:51.425524,0.658954:51.424496,0.657257:51.423131,0.65549:51.422008,0.654522:51.419373,0.653156:51.414574,0.649476:51.413331,0.64828:51.412206,0.646661:51.411882,0.645879:51.410117,0.640092:51.409711,0.637857:51.409601,0.636218:51.409628,0.631923:51.411018,0.622145:51.411427,0.614475:51.411107,0.605172:51.410395,0.599723:51.409209,0.596314:51.408261,0.594603:51.406346,0.593189:51.402945,0.591531:51.401514,0.590151:51.400478,0.588513:51.399852,0.587176:51.399341,0.585755:51.399112,0.584493).

When I input the poly value to PoliceDataExtract function, it throws 400 error, however, if I cut off the poly value to shorter, PoliceDataExtract function returns correct value.
Regards,
Lydia

Anonymous · ‎09-03-2018

Hi Lydia

Thanks for getting back to me on this though it unfortunatly it does not meet the requirements of the task.

Using a shortened poly will mean that the search area it's self would be altered in a way we are unable to understand. I have found a way around this for this project. It uses a similar approach to yours, ie sticks with a GET request, though rather than removing part of the URL it uses a simplifed polygon of the search area from which lats and longs are extracted. This results in a shortened URL though for all intents and purposes the same area.

Thank you for your time.

POST API leading to 400 error

Issue with new card visual after publishing to PBI...

Issue with Card Visual Layout After November Power...

Automatically expand table columns to fill availab...

New Button Slicer: Issue with the Grid type of Arr...

List Slicer Button Dimensions Ignored in Service

FabCon is coming to Atlanta

POST API leading to 400 error

Issue with new card visual after publishing to PBI...

Issue with Card Visual Layout After November Power...

Automatically expand table columns to fill availab...

New Button Slicer: Issue with the Grid type of Arr...

List Slicer Button Dimensions Ignored in Service