Hi All,
I have a dashboard connected to several endpoints of a service(web). There is an authentication (once per 24hrs) and a refresh(hoursly) end points that must be established first. Then use the token to call other endpoints.
1- I made all endpoints separated. all works well with anonymous authentication on desktop, but I cannot schedule refresh on pbi server. error is:
| [Unable to combine data] Section1/Refresh/Converted to Table references other queries |
2- I searched a lot online and solution is to combine queries. I did that and now the third endpoint cannot be refreshed. error is: we couldn't authenticate the credentials provided. ( they are all anonymous and working separately - point 1 senario ) while I put them in one query (see below) the last one's credentials fail.
Note: links are samples (for confidentiality purposes)
let
//Authentication
AuthUrl = "https://server/authenticate",
AuthContent = "
{
""emailAddress"": ""example@example.co"",
""password"": ""passoword""
}
",
AuthResponse= Web.Contents(AuthUrl,
[
Content=Text.ToBinary(AuthContent),
Headers=[#"Content-Type" = "application/json"]
]),
AuthJson = Json.Document(AuthResponse),
AuthTable = Record.ToTable(AuthJson),
RemoveTopRows = Table.Skip(AuthTable,3),
FormatRefToken = Table.AddColumn(RemoveTopRows, "Quotes", each """" & [Value] & """"),
RemoveExtras = Table.RemoveColumns(FormatRefToken,{"Value"}),
TransposedTable = Table.Transpose(RemoveExtras),
PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars=true]),
token = Text.From(List.First(PromotedHeaders[refreshToken])),
//Refresh
RefUrl = "https://server/party/refresh",
RefContent = "
{
""refresh_token"": "&token&"
}
",
RefResponse= Web.Contents(RefUrl,
[
Content=Text.ToBinary(RefContent),
Headers=[#"Content-Type" = "application/json"]
]),
RefJson = Json.Document(RefResponse),
RefTable = Record.ToTable(RefJson),
AddQuotes = Table.AddColumn(RefTable, "Quotes", each """" & [Value] & """"),
#"Transposed Table" = Table.Transpose(AddQuotes),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Removed Top Rows" = Table.Skip(#"Promoted Headers",1),
#"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"access_token"}),
id_token = Text.From(List.First(#"Removed Columns"[id_token])),
//Permits
Url = "https://server/reporting/permits",
Content = "
{
""offset"": "&Number.ToText(12)&",
""sort_column"": ""date_create"",
""sort_direction"": ""desc""
}
",
Response= Web.Contents(Url,
[Headers=[token=id_token,#"Content-Type" = "application/json"]]),
Json = Json.Document(Response),
ConvertedtoTable = Record.ToTable(Json),
Value = ConvertedtoTable{1}[Value],
ConvertedtoTable1 = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedtoTable1, "Column1", {"x", "y", "pz"})
in
ExpandedColumn1
