We have a web api data source that lives behind a cloudfare deployment where http 302 redirects are involved.
Using power bi desktop > get data > web [ api ] > enter url details and Authorization header value initial produces an access denied. This was because the services api documentation points to a web api that issues 302 redirects. If we put the redirect url value in things work fine. Problem is that is a brittle solution and instead we'd like to support following the 302 redirects.
We have the following code in place that on "Response = Json.Document(InitialRequest)," line is generating the error "Expression.Error: '302' is not supported by the ManualStatusHandling option.".
Shouldn't 302 be supported and if not any insights on how we properly author web api data source M code to follow a redirect?
let
// initial request to get the redirect url
InitialRequest = Web.Contents("https://my.securityjourney.com/api/v3/public-reporting/users", [ManualStatusHandling={302}]),
//RedirectUrl = Text.FromBinary(InitialRequest[Headers][Location]), // Expression.Error: We cannot apply field access to the type Binary.
Response = Json.Document(InitialRequest), // Expression.Error: '302' is not supported by the ManualStatusHandling option.
RedirectUrl = Response[Headers][Location],
// follow the redirect url to get the actual data
FinalRequest = Web.Contents(RedirectUrl),
Source = Json.Document(FinalRequest),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded users" = Table.ExpandListColumn(#"Converted to Table", "users"),
#"Expanded users1" = Table.ExpandRecordColumn(#"Expanded users", "users", {"email", "firstName", "lastName", "totalPoints", "securityChampion", "timeSpent"}, {"users.email", "users.firstName", "users.lastName", "users.totalPoints", "users.securityChampion", "users.timeSpent"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded users1",{{"users.email", type text}, {"users.firstName", type text}, {"users.lastName", type text}, {"users.totalPoints", Int64.Type}, {"users.securityChampion", type logical}, {"users.timeSpent", type text}})
in
#"Changed Type"
