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 service. The error message is:
[Unable to combine data] Section1/V7ClientsStaging2/Added Custom1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7Clients.
I have tried privacy settings, staging tables (as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/) and still cannot get it to work.
A recommendation off another blog was to add the function definition into the main query. I'm struggling with the syntax however to get this to work. Any assistance would be appreciated.
Main Query (the function is AssessmentsInfo, bolded below):
let
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 AssessmentsInfo([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"
AssessmentsInfo function definition:
(AreaAPI as text) as list =>
let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”
]
))
in
Source
AssessmentsInfo function definition within the Main Query (not working, gives a column of non invoked functions):
let
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
(AreaAPI as text) as list =>
let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”
]
))
in
Source
),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"})
in
#"Expanded ClientDetails"
Any assistance on the syntax of adding the function definition within the query would be much appreciated!
