I am struggling with refreshing data coming off multiple web API's which I combine using a parameter in the URL. The data refreshes ok 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 seperate the queries into seperate staging queries in Power BI desktop:
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
My error message is below: If I try and create seperate 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"
