Forum Discussion
QUERY ISSUES: Expression.Error: We cannot convert the value null to type Logical.
Hi Folks, I know this has been asked loads before and I have read the threads but nothing is working for me.... I have 2 tables pulling from Salesforce Objects, a Header Table and a Product Table...
Anonymous Are you building any additional conditional columns or expression where a comparison of values essentially is null vs. null? Or is this a simple connection to Salesforce with no additional applied steps? Typically if this was a value only error it would just spit out that exception list, but there could be a core expression that is breaking the whole thing on load because it is only pulling a preview of the date (first 1000) rows in PQ.
Hi Seth,
Yes I have additional logic, in my reply to natel I mentioned I have 4 merges to an accounts table to return the various accounts name and additional account numbers... then off those additional field I have ANOTHER 4 merges that basically checks if the the global ultimate account is on a list and return a Y or N...
As mentioned to Natel this all did work briefly then I did add another merge to account name bring it to 5 but did a conditional column on that ones result to check if it contained text and return a Y or N.
Finally I have a custom column with an if formula basically:
If account duns ="Y" or Partner duns="Y" or SP duns="Y" or Disti name="Y" then "Y" else "EXCLUDE"
Here the Query Editor Text:
let Source = Salesforce.Data("https://COMPANYXE.my.salesforce.com"), Opportunity1 = Source{[Name="Opportunity"]}[Data], #"COLUMNS REMOVED HERE" = Table.SelectColumns(Opportunity1,{"Id", "AccountId", "Name", "StageName", "Amount", "CloseDate", "ForecastCategory", "ForecastCategoryName", "Country__c", "COMPANYXE_s_Unique_Value_Why_COMPANYXE__c", "Opportunity_Number__c", "Opportunity_Owner__c", "Product_Services_Led__c", "Quote_Cart_Number__c", "Quote_Cart__c", "SO_Number__c", "Type_of_Sale__c", "Account_Name1__c", "Service_Provider__c", "Products__c", "Primary_Alliance_Partner__c", "Alliance_Business_Type__c", "ELA2__c", "Alliance_Engagement_Document__c", "PO_Number__c", "Service_Provider_Business_Type__c", "Alliance_Offerings__c", "COMPANYXD_Deal_ID__c", "COMPANYXD_Opportunity_Number__c", "Global_Alliance_Close_Date__c", "Global_Alliance_Forecast_Amount__c", "Global_Alliance_Forecast_Product_Amount__c", "Global_Alliance_Forecast_Status__c", "Included_in_Mgt_Call__c", "Global_Alliance_Comment__c", "COMPANYXD_Distributor_Reseller_Account_Name__c", "LeadSource","Partner__c"}), #"Remove Contract Renewals" = Table.SelectRows(#"COLUMNS REMOVED HERE", each ([LeadSource] <> "Contract Renewal")), #"Filter Close Dates" = Table.SelectRows(#"Remove Contract Renewals", each [CloseDate] >= CloseDateFirst and [CloseDate] <= CloseDateLast), #"Filter Omitted from Forecast Cat" = Table.SelectRows(#"Filter Close Dates", each ([ForecastCategory] <> "Omitted")), #"Merge in SP Details" = Table.NestedJoin(#"Filter Omitted from Forecast Cat",{"Service_Provider__c"},#"SFDC AC Account Table",{"Id"},"SFDC AC Account Table",JoinKind.LeftOuter), #"Expanded SFDC AC Account Table" = Table.ExpandTableColumn(#"Merge in SP Details", "SFDC AC Account Table", {"Name", "Global_DUNS_Entity__c", "Party_Number__c", "Global_DUNS_Entity_Hub__c", "UCID__c", "Affinity_ID__c"}, {"SP.Name", "SP.Global_DUNS_Entity__c", "SP.Party_Number__c", "SP.Global_DUNS_Entity_Hub__c", "SP.UCID__c", "SP.Affinity_ID__c"}), #"Convert DUNs to Number" = Table.TransformColumnTypes(#"Expanded SFDC AC Account Table",{{"SP.Global_DUNS_Entity_Hub__c", Int64.Type}}), #"Merge in Theatre Mappings" = Table.NestedJoin(#"Convert DUNs to Number",{"Country__c"},#"Country Theater Mapping",{"Name"},"Country Theater Mapping",JoinKind.LeftOuter), #"Expanded Country Theater Mapping" = Table.ExpandTableColumn(#"Merge in Theatre Mappings", "Country Theater Mapping", {"Theater__c"}, {"Country Theater Mapping.Theater__c"}), #"Filter EMEA ONLY" = Table.SelectRows(#"Expanded Country Theater Mapping", each ([Country Theater Mapping.Theater__c] = "EMEA")), #"Merge in SP DUNS" = Table.NestedJoin(#"Filter EMEA ONLY",{"SP.Global_DUNS_Entity_Hub__c"},#"DUNS IN OUT",{"Duns"},"DUNS IN OUT",JoinKind.LeftOuter), #"Expanded DUNS IN OUT" = Table.ExpandTableColumn(#"Merge in SP DUNS", "DUNS IN OUT", {"Partner", "Include?"}, {"SP DUNS.Partner", "SP DUNS.Include?"}), #"Merge in PAP Details" = Table.NestedJoin(#"Expanded DUNS IN OUT",{"Primary_Alliance_Partner__c"},#"SFDC AC Account Table",{"Id"},"SFDC AC Account Table",JoinKind.LeftOuter), #"Expanded SFDC AC Account Table1" = Table.ExpandTableColumn(#"Merge in PAP Details", "SFDC AC Account Table", {"Name", "Global_DUNS_Entity__c", "Global_DUNS_Entity_Hub__c", "UCID__c", "Affinity_ID__c"}, {"PAP.Name", "PAP.Global_DUNS_Entity__c", "PAP.Global_DUNS_Entity_Hub__c", "PAP.UCID__c", "PAP.Affinity_ID__c"}), #"Merge Acc Info" = Table.NestedJoin(#"Expanded SFDC AC Account Table1",{"AccountId"},#"SFDC AC Account Table",{"Id"},"SFDC AC Account Table",JoinKind.LeftOuter), #"Expanded SFDC AC Account Table2" = Table.ExpandTableColumn(#"Merge Acc Info", "SFDC AC Account Table", {"Name", "Global_DUNS_Entity__c", "Segmentation__c", "Party_Number__c", "Global_DUNS_Entity_Hub__c", "UCID__c", "Affinity_ID__c"}, {"Acc.Name", "Acc.Global_DUNS_Entity__c", "Acc.Segmentation__c", "Acc.Party_Number__c", "Acc.Global_DUNS_Entity_Hub__c", "Acc.UCID__c", "Acc.Affinity_ID__c"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded SFDC AC Account Table2",{{"Acc.Global_DUNS_Entity_Hub__c", Int64.Type}, {"PAP.Global_DUNS_Entity_Hub__c", Int64.Type}}), #"Merge PAP DUNS" = Table.NestedJoin(#"Changed Type",{"PAP.Global_DUNS_Entity_Hub__c"},#"DUNS IN OUT",{"Duns"},"DUNS IN OUT",JoinKind.LeftOuter), #"Expanded DUNS IN OUT1" = Table.ExpandTableColumn(#"Merge PAP DUNS", "DUNS IN OUT", {"Partner", "Include?"}, {"PAP.Partner", "PAP.Include?"}), #"Merge Acc DUNS" = Table.NestedJoin(#"Expanded DUNS IN OUT1",{"Acc.Global_DUNS_Entity_Hub__c"},#"DUNS IN OUT",{"Duns"},"SFDC AC Account Table",JoinKind.LeftOuter), #"Expanded SFDC AC Account Table3" = Table.ExpandTableColumn(#"Merge Acc DUNS", "SFDC AC Account Table", {"Partner", "Include?"}, {"Acc.Partner", "Acc.Include?"}), #"Merge Disti" = Table.NestedJoin(#"Expanded SFDC AC Account Table3",{"Partner__c"},#"SFDC AC Account Table",{"Id"},"SFDC AC Account Table",JoinKind.LeftOuter), #"Expanded SFDC AC Account Table4" = Table.ExpandTableColumn(#"Merge Disti", "SFDC AC Account Table", {"Name", "Global_DUNS_Entity__c", "Party_ID__c", "Global_DUNS_Entity_Hub__c", "UCID__c", "Affinity_ID__c"}, {"Disti.Name", "Disti.Global_DUNS_Entity__c", "Disti.Party_ID__c", "Disti.Global_DUNS_Entity_Hub__c", "Disti.UCID__c", "Disti.Affinity_ID__c"}), #"Check Disiti Field" = Table.AddColumn(#"Expanded SFDC AC Account Table4", "Disti Inc YN", each if Text.Contains([Disti.Name], "DIMENSION DATA") then "Y" else "N"), #"Merged Queries" = Table.NestedJoin(#"Check Disiti Field",{"Disti.Global_DUNS_Entity_Hub__c"},#"DUNS IN OUT",{"Duns"},"DUNS IN OUT",JoinKind.LeftOuter), #"Custom Field Inc Duns / Disti" = Table.AddColumn(#"Merged Queries", "DUNS DISTI INC EXCL", each if [#"Acc.Include?"]="Y" or [#"PAP.Include?"]="Y" or [#"SP DUNS.Include?"]="Y" or [Disti Inc YN]="Y" then "Y" else "EXCLUDE"), #"FILTER; EXLUDE NON APPLIABLE OPS" = Table.SelectRows(#"Custom Field Inc Duns / Disti", each ([DUNS DISTI INC EXCL] = "Y")), #"Filtered Rows" = Table.SelectRows(#"FILTER; EXLUDE NON APPLIABLE OPS", each [Id] <> null and [Id] <> ""), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Global_Alliance_Forecast_Amount__c", type number}, {"Global_Alliance_Forecast_Product_Amount__c", type number}, {"Amount", type number}, {"CloseDate", type date}, {"Global_Alliance_Close_Date__c", type date}, {"Disti.Affinity_ID__c", Int64.Type}, {"Disti.UCID__c", Int64.Type}, {"Disti.Global_DUNS_Entity_Hub__c", Int64.Type}, {"Disti.Party_ID__c", Int64.Type}, {"Disti.Global_DUNS_Entity__c", Int64.Type}, {"Acc.Affinity_ID__c", Int64.Type}, {"Acc.UCID__c", Int64.Type}, {"Acc.Global_DUNS_Entity_Hub__c", Int64.Type}, {"Acc.Party_Number__c", Int64.Type}, {"Acc.Global_DUNS_Entity__c", Int64.Type}, {"PAP.Affinity_ID__c", Int64.Type}, {"PAP.UCID__c", Int64.Type}, {"PAP.Global_DUNS_Entity_Hub__c", Int64.Type}, {"PAP.Global_DUNS_Entity__c", Int64.Type}, {"SP.Affinity_ID__c", Int64.Type}, {"SP.UCID__c", Int64.Type}, {"SP.Global_DUNS_Entity__c", Int64.Type}, {"SP.Party_Number__c", Int64.Type}, {"SP.Global_DUNS_Entity_Hub__c", Int64.Type}, {"Id", type text}, {"AccountId", type text}, {"Name", type text}, {"StageName", type text}, {"ForecastCategory", type text}, {"ForecastCategoryName", type text}, {"Country__c", type text}, {"COMPANYXE_s_Unique_Value_Why_COMPANYXE__c", type text}, {"Opportunity_Number__c", type text}, {"Opportunity_Owner__c", type text}, {"Product_Services_Led__c", type text}, {"Quote_Cart_Number__c", type text}, {"Quote_Cart__c", type text}, {"SO_Number__c", type text}, {"Type_of_Sale__c", type text}, {"Account_Name1__c", type text}, {"Service_Provider__c", type text}, {"Products__c", type text}, {"Primary_Alliance_Partner__c", type text}, {"Alliance_Business_Type__c", type text}, {"ELA2__c", type text}, {"Alliance_Engagement_Document__c", type text}, {"PO_Number__c", type text}, {"Service_Provider_Business_Type__c", type text}, {"Alliance_Offerings__c", type text}, {"COMPANYXD_Deal_ID__c", type text}, {"COMPANYXD_Opportunity_Number__c", type text}, {"Global_Alliance_Forecast_Status__c", type text}, {"Included_in_Mgt_Call__c", type text}, {"Global_Alliance_Comment__c", type text}, {"COMPANYXD_Distributor_Reseller_Account_Name__c", type text}, {"LeadSource", type text}, {"Partner__c", type text}, {"SP.Name", type text}, {"Country Theater Mapping.Theater__c", type text}, {"PAP.Name", type text}, {"SP DUNS.Partner", type text}, {"SP DUNS.Include?", type text}, {"Acc.Name", type text}, {"Acc.Segmentation__c", type text}, {"PAP.Partner", type text}, {"PAP.Include?", type text}, {"Acc.Partner", type text}, {"Acc.Include?", type text}, {"Disti.Name", type text}, {"Disti Inc YN", type text}, {"DUNS DISTI INC EXCL", type text}}) in #"Changed Type1"Anonymous - Since it happens when merging the 2 tables, my first suspicion would be NULL values in the column(s) involved with the failing merge.
I had the same suspicion and did a remove empty on the mutual fields in the seperate queries... and still the same thing... I also judt did a formula of
if oppid=null then "y" else "n"
and then filtered on "y" and there was no nulls eitherway...