[Unable to combine data]

[ckkrish]

Greetings, all -

This error has been driving me nuts. I've gone through many posts discussing this error but I am still kind of in square one.  Any help would be greatly appreciated.  I am getting this error in Power BI service upon refresh.  

I am attempting to access D&B database and extract information for a few DUNS numbers (company ids) thru API calls. 

1. DUNS numbers from a separate query are gathered into a list.

2. D&B API Access token is obtained from a separate query.

3. A function makes API calls to D&B database iteratively for each DUNS number in the list.

4. Finally, all the data gathered in step #3 is combined for downstream processing.

Error message:

---

[Unable to combine data] Section1/AllBlocks/Expanded blockIDs references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDbCommand interface. Table: AllBlocks

Cluster URIWABI-US-NORTH-CENTRAL-B-redirect.analysis.windows.net
Activity ID88e0232b-474e-9751-a1bc-606c9ede3fb5
Request ID0cdd49df-590b-b46a-f8a4-7a83b6f5cfcc

---

The query code that is generating the error:

---

let
// Define variables
DUNSTable = #"DORA+Bulletin42 DUNS",
DUNSNumbers = Table.Column(DUNSTable, "DUNSNumber"), // List of DUNS numbers

AccessToken = AccessTokenTable{0}[Token],
BaseURL = "https://plus.dnb.com",
Query = [blockIDs = {"companyinfo_L2_v1","diversityinsight_L3_v1","financialstrengthinsight_L1_v1"}],

// Function to get data for a single company
GetCompanyData = (DUNSNumber as text) =>
let
RelativePath = "/v1/data/duns/" & DUNSNumber,
// Make API request with headers
CompanyDataResponse = try Json.Document(Web.Contents(BaseURL, [
RelativePath = RelativePath,
Query = Query,
Headers = [
Accept = "application/json;charset=utf-8",
Authorization = "Bearer " & AccessToken
]
])) otherwise null,
ConvertedToTable = if CompanyDataResponse <> null then Record.ToTable(CompanyDataResponse) else null,
TransposedTable = if ConvertedToTable <> null then Table.Transpose(ConvertedToTable) else null,
PromotedHeadersTable = if TransposedTable <> null then Table.PromoteHeaders(TransposedTable) else null
in
PromotedHeadersTable,

// Get data for all companies and combine into one table
CompanyDataTables = List.Transform(DUNSNumbers, each GetCompanyData(_)),
ValidCompanyDataTables = List.Select(CompanyDataTables, each _ <> null),
CombinedData = Table.Combine(ValidCompanyDataTables),
#"Expanded inquiryDetail" = Table.ExpandRecordColumn(CombinedData, "inquiryDetail", {"duns", "blockIDs"}, {"duns", "blockIDs"}),
#"Expanded blockIDs" = Table.ExpandListColumn(#"Expanded inquiryDetail", "blockIDs")
in
#"Expanded blockIDs"

---

Thanks for looking, and appreciate any help.

Regards.

[ckkrish · Accepted solution]

Update.

Cautiously glad to report that the issue appears to be resolved.  What seems to work is to remove reference to all queries and bring them all into one "monolithic" query.  No compaints since that the query is referencing other queries or steps.

Thank you all for jumping in with tips and references. They were very helpful.

Cheers, and have a great weekend!

[v-menakakota]

Hi @ckkrish ,

Thank you for your response.

Glad that your query has been resolved. If our community member's response addressed your question   please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[ckkrish]

Hi @v-menakakota,

The community made an earnest effort to help, but unfortuntely they were things that I had already tried.  What worked was arrived at by my own further experimentation.  That is the reason I didn't mark any particular response from community as "Accept as Solution", but posted an update instewad describing how I was able to resolve it.   Hope that is helpful to anyone who might encounter similar issues.

Regards,

[v-menakakota]

Hi @ckkrish ,

Since you've resolved the issue on your own, please consider accepting your solution so that others can benefit from it as well.

Thank you.

[ckkrish]

Done!  

Thank you.

[v-menakakota]

Hi @ckkrish ,

Glad that your query has been resolved. If our community member's response addressed your question please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Thank you.

[v-menakakota]

Hi @ckkrish ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Verify the privacy level settings for your data source in the Power BI service.
If you're using a personal gateway, ensure the privacy level is configured as "Organizational" to enable efficient data combination.
Make sure the tables you are attempting to combine have compatible structures.

If the tables have varying column names or data types, consider applying the necessary transformations to align them before combining.
If you're dealing with a large number of DUNS numbers, consider using techniques  Divide the list of DUNS numbers into smaller chunks and process them in batches.
Develop a dedicated function to manage API calls, extract data, and perform initial data shaping for a single DUNS number. This enhances both the readability and maintainability of the code.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[ckkrish]

Hi v-menakakota,

Thanks for your response.

Privacy settings are "Organizational". 

The tables should have identical structures as they are data items resulting from identical calls.  Moreover, I have not noticed any discrepancies in desktop version.  Do you think if one of the responses (to API calls) results in fewer data elements than others it can cause an issue? I am thinking that could be a possibility. Anyway, again the desktop version doesn't seem to have any trouble with it. Refresh on service is the one unhappy with something in it.

DUNS numbers are not too large. Its about 140-150, and is well within rate/quota limits.

>>Develop a dedicated function to manage API calls, extract data, and perform initial data shaping for a single DUNS number.

A dedicated function manages the API calls. Any other improvements, could you be kind enough to review the code and make specific modifications or suggestions?  I am really not an expert in this.  Are there any references (books/urls) that you can suggest to enhance myself - over longer term, of course.

Thank you again for your suggestions.

Regards.

[lbendlin]

Don't see anything inherently wrong with this query.  Is this the only query or are you trying to merge it with others?

For some more troubleshooting refer to Chris Webb's BI Blog: Troubleshooting Web Service Refresh Problems In Power BI With The Power Query ...

[ckkrish]

Hi lbendlin,

Thanks for the link. I will review.

The query itself doesn't merge with any others. Downstream queries take the output of this query and separate the data into different groups.  On occasion, on rare occasions, this query seems to go through (I think), as one of the downstream queries generate a similar error.  I am not able to identify a pattern as to when the error is thrown by the first query vs. when the error comes out of the downstream query. Mostly the first one gives the error.

Thanks again for your response. I will review the link suggested.

Regards.