Forum Discussion
Database Error when expanding a table in a merged query
Hi Gurus,
I need some help. I'm getting a new error in a pbix that was working until yesterday and then when I tried to refresh the data showed up.
For context, I'm am trying to join data from two sources. Salesforce and AzureDevOps using an id column that is in both sets. Here's what I've done so far.
- Created a new pbix
- Connected my data sources (no errors)
- Created a new merged as new query using the below (no errors)
let
Source = Table.NestedJoin(#"DevOps-Core", {"SFDC#"}, #"SFDC-Opportunities", {"Opportunity_Number__c"}, "SFDC-Opportunities", JoinKind.LeftOuter),
#"Expanded SFDC-Opportunities" = Table.ExpandTableColumn(Source, "SFDC-Opportunities", {"Name", "Description", "StageName", "Amount", "ExpectedRevenue", "TotalOpportunityQuantity", "CloseDate", "CreatedDate", "LastModifiedDate", "Opportunity_Number__c", "Estimated_Close_Date__c", "Estimated_Service_Close_Date__c", "Projected_Close_Date__c"}, {"Name", "Description", "StageName", "Amount", "ExpectedRevenue", "TotalOpportunityQuantity", "CloseDate", "CreatedDate", "LastModifiedDate", "Opportunity_Number__c", "Estimated_Close_Date__c", "Estimated_Service_Close_Date__c", "Projected_Close_Date__c"})
in
#"Expanded SFDC-Opportunities
- Here is where the error shows up. In my merged query, I now have a new row just of errors
DataSource.Error:
Projected_Close_Date__c FROM Opportunity WHERE TRUE ^
ERROR at Row:1:Column:246 unexpected token: 'TRUE'
Details: [List]
Any thoughts? Thanks!
- Power Bi Newb
6 Replies
I had the exact same issue, same error but a different table. So how did I resolve it? I went to the query I was merging into my main query and added the following (bolded part) to the end of the query using advanced editor. No guarantees this will work for all occurances since this is the first time I ever came across it but after digging around I thought I would try it. Also made the data load faster although I could just be imagining that part.
#"Renamed Columns" = blah blah blah,
#"Buffer" = Table.Buffer(#"Renamed Columns")
in
#"Buffer"
pbiuserukResolver IV
Thanks - that worked for me. If anyone's not clear on this, you do this in the advanced editor code for the source queries. My issue was because of Salesforce tables as well.
I was having the exact same issue described here with Salesforce data, and I attempted this, and it worked. What confuses me is the table that I added this to was functioning fine with the exception of this one merge resulting in an error row. Is there an explanation for why the issue happened, what "buffer" does and why that solves the issue?
I'm happy my problem is resolved but even more confused since I do not understand either the problem or solution. It makes no sense to me why the merge wasn't successful without doing this. It's a small table, with all 119 records having a match.
- pbiuseruk
Buffer is sort of like stop folding from what I understand.
PowerBI will generally try to fold queries and that means, that the source system will actually run the queries and use their compute power/resources, rather than Power Query.
When queries are folded, Power BI tries to automatically optimise them and can rearrange steps if it thinks it's more efficient. The problem with this though is that sometimes, rearranging the steps can break the query (which is what's happened to everyone on this post from the sounds of it). The way to fix this is to use either stopfolding or buffer before the PowerQuery step that is failing.
Hope that helps.
v-yuta-msftCommunity Support
Anonymous ,
This issue seems to be more related to salesforce, have you checked if there's any unprintable characters in the salesforce data source?
Regards,
Jimmy Tao
PowerQuery is attempting a query against Salesforce that simply is incorrect. To work around this issue:
1. Download the source Salesforce table you intend to merge to a tab
2. From that tab, create a new linked query "From Table/Range"
3. Now join your source table with the new linked query range