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 in the Query Preview panel these appear to be working fine... however when I actually go to load them in fully I get the dreaded error:
Expression.Error: We cannot convert the value null to type Logical.
Now from reading other threads on same I have explicitily reconverted EVERY Column into either Text / Number or Date and still I get this error.
I am completely lost as to why or how I can get it sorted?
Any suggestions welcomed!
12 Replies
Hi Natel,
Yes within the query editor it all looks fine it only fires the error if I either:
Load the tables fully
or
If I try merge my 2 tables within the query editor.
I do have a good number of steps on my headers table including merging to other tables numerous times for account names etc.
Come to think of it though my problems did begin when I added a Conditional Column to the query of if column Disti contained "PARTNER" then "Y" else "N".
It might be coincidental though?
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"
I am having the null vs null error (I think). I created conditional columns that go look for a specific phrase in a text column that has mutiple comma seperated entries - thus producing a column with only the desired text in it. Some instances where I get a 'null' I see that the source column is null HOWEVER when there is text in the column but NOT the text I am seeking I get an error.
What advice might you have?