Hi

I am struggling with refreshing data coming off multiple web API's which I combine using a parameter in the URL. The data refreshes correctly in Power BI desktop, but I cannot get it to refresh within the service.


I've looked at the below link as well as ignoring the Privacy Settings in the desktop, but I still cannot figure out why I get an error message and how to separate the queries into separate staging queries in Power BI desktop:
https://www.excelguru.ca/blog/2015/0...a-combination/

I've tried in the Power BI community but no luck.


My error message is below: If I try and create separate staging tables I get error messages. Any assistance on this would be greatly appreciated!

Error Message:
[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.

Original Query (which gives an error on Added Custom 1 step) when uploaded to service:

let
Source = {

ClientDetails[AreaGuid]
},
#"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])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Custom"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Errors",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Custom"}),
#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Removed Columns", "ClientDetails", {"ClientName"}, {"ClientName"})
in
#"Expanded ClientDetails"


And this is how I've attempted to split the query out, where I still get the same error message on the second staging table:

Staging Table 1:
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")
in
#"Expanded AreaGuidsForFunction"


Staging Table 2

let
Source = V7ClientsStaging1,

#"Added Custom1" = Table.AddColumn(Source, "Custom", each AssessmentsInfo([AreaGuidsForFunction])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Custom"})
in
#"Removed Errors"


Final table (Table 3):

let
Source = V7ClientsStaging2,
#"Merged Queries" = Table.NestedJoin(Source,{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded ClientDetails",{"Custom"})
in
#"Removed Columns"

Any assistance on this would be appreciated!