cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LJR Frequent Visitor
Frequent Visitor

Unable to combine data - Please rebuild this data combination - Refresh in Power BI Service off API

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"

3 REPLIES 3
Community Support Team
Community Support Team

Re: Unable to combine data - Please rebuild this data combination - Refresh in Power BI Service off

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LJR Frequent Visitor
Frequent Visitor

Unable to combine data - Please rebuild this data combination - Refresh in Power BI Service off API

Hi @v-frfei-msft

 

Thanks for the response. Answers below:

 

  1. I'm using the December 2018 version of the desktop. This however has been an ongoing issue across multiple versions of Power BI. I'm raising this now as it's become a matter of urgency to use to distribute reports within the business using Power BI service and not just desktop (where combining data works fine with multiple API sources).
  2. I'm using a combination of API data (passing in parameters) and a data gateway for on-prem on this report. I experience the same issue if I use API data only and merge multiple API sources together. 
  3. I've tried deleting the dataset again and republishing the report into the Power BI service. Again, it works fine in Power BI desktop but not Power BI service. The error message I repeatedly get or something similar is: [Unable to combine data] Section1/V7Clients/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7Clients.

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.  

 

Highlighted
LJR Frequent Visitor
Frequent Visitor

Re: Unable to combine data - Please rebuild this data combination - Refresh in Power BI Service off

@v-frfei-msft Hi Frank

 

Do you perhaps have any feedback on how I could approach this issue?