Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Hi @LJR,
Based on my test, I cannot produce your issue here.
1. Did you use the latest version of desktop?
2. What is your data source? Did you install the latest gateway?
3. Could you please delete the dataset and republish your report to try again?
Regards,
Frank
Thanks for the response. Answers below:
I'm struggling to get this to work and it's becoming a massive time constraint to build a model in the desktop that works only to find in the service that it's failing when trying to refresh. I've tried this as a workaround but can't seem to get the right combination: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Thanks for your assistance on this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.