Forum Discussion
Define and invoke function within query
I am struggling with refreshing data coming off multiple web API’s which I combine using a parameter in the URL. The data refreshes fine in Power BI desktop, but I cannot get it to refresh within the...
That would be done like so:
let AssessmentsInfoFunction = (AreaAPI as text) as list => let Source = Json.Document(Web.Contents(“https://abc.xxx.com”, [ RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria” ] )) in Source, Source = {ClientDetails[RootAreaGuid]}, #"ListOfLists" = List.Combine(Source), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "AreaGuidsForFunction"}}), #"Expanded AreaGuidsForFunction" = Table.ExpandListColumn(#"Renamed Columns", "AreaGuidsForFunction"), #"Added Custom1" = Table.AddColumn(#"Expanded AreaGuidsForFunction", "Custom", each AssessmentsInfoFunction([AreaGuidsForFunction])), #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter), #"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"}) in #"Expanded ClientDetails"
The article I referenced in my previous post states that you have to use query parameters to make this successful:
so instead of this:
Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),
you have to write it like so:
Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),Hi I am not able to change schema name vi Parameter. Can you help me on this? please see my originla post here. Thanks.