Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gsalas
Helper II
Helper II

Merge Function Throwing errors if there is a NULL value

SO FRUSTRATED.  The latest update to Power Bi desktop prevents my data from loading if any left join merged queries produce a null value.  I use TON of left join merged queries and they all obviously produce null values because that is the WHOLE point of a left join!!  Now I cannot load in ANY data via my pre-built template.  This is an urgent request because I need a template fully built out for a meeting friday.

 

Issue one: Left Join Merged Queries followed by a Table.AggregateTableColumn, List.sum function.  Because not every keyword (left table) has a matching lead (right table), I get a null value on some of the keywords.  Desktop application used to simply load a "null" value in the cell.  Now desktop application is giving me the following error

 

Expression.Error: We cannot conver the value null to type Logical.

Details:

Value=

Type=Type

 

Issue two: Left Join Merged Queries followed by a Table.ExpandTableColumn function.  The data looks fine in the queries window, but when I try and load it, I get the following cryptic message: 

 

Network Leads Merged

OLE DB or ODBC error: [Expression.Error] We Cannot conver the value null to type logical.

 

Smiley Mad

7 REPLIES 7
pelowski
Helper III
Helper III

I'm running into this issue as well.  I've looked up and down the steps on the right and then looked at the data returned for each step and I don't see any errors anywhere.  I spent particular attention looking for conditional columns and steps that compared or combined columns that had NULL values.  (I have some "Keep N first characters" operations and a few others that compare against NULL values.)  After all the aggregations I'm doing, I have less than 999 rows so I'm confident I'm looking at the complete data set so I don't think I'm missing anything "below the fold" so-to-speak.

 

Question for you... were there any issues in the returned data in any of the preceding steps that led you to determine what step had the problem?  I've gone over these queries for hours now and can't find what is causing the Expand table to fail at the end.  It's literally (and ironically) the last thing I need to do for my report but I can't expand the columns.

Sounds like a bug.  Sounds like you are changing the format of the column containing nulls to True/False.  Is that correct?  could you instead use a string column with True or False?  I am not saying it is ideal, just trying to help find a work around



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

There is no True/False command in the code.  Here is the code for each issue.  Key caveat - I did not get this error before I updated Power Bi to the latest version:

 

Issue one: Left Join Merged Queries followed by a Table.AggregateTableColumn, List.sum function.  Because not every keyword (left table) has a matching lead (right table), I get a null value on some of the keywords.  Desktop application (September Update) would simply load a "null" value in the cell.  October Update desktop application is giving me the following error

 

Expression.Error: We cannot conver the value null to type Logical.

Details:

Value=

Type=Type

 

Source Code:

 

let

 

Source = #"1 Keyword Index",
#"Grouped Rows" = Table.Group(#"1 Keyword Index", {"Keyword"}, {{"All Time Spend", each List.Sum([Index Spend]), type number}}),
#"Merged in Lead Volume" = Table.NestedJoin(#"Grouped Rows",{"Keyword"},#"Lead Data Merged",{"Keyword"},"NewColumn",JoinKind.Inner),
#"Aggregated Leads" = Table.AggregateTableColumn(#"Merged in Lead Volume", "NewColumn", {{"Leads", List.Sum, "Sum of Leads"}}),

//Error loads and rest of code cannot execute
#"Chg Leads to Decimal" = Table.TransformColumnTypes(#"Aggregated Leads",{{"Sum of Leads", type number}}),
#"Renamed Leads Col" = Table.RenameColumns(#"Chg Leads to Decimal",{{"Sum of Leads", "All Time Leads"}}),
#"Merged in Touches" = Table.NestedJoin(#"Renamed Leads Col",{"Keyword"},#"Touch Data Merged",{"Keyword"},"NewColumn",JoinKind.LeftOuter),
#"Aggregated Touches" = Table.AggregateTableColumn(#"Merged in Touches", "NewColumn", {{"Touches", List.Sum, "Sum of Touches"}}),
#"Renamed Touches" = Table.RenameColumns(#"Aggregated Touches",{{"Sum of Touches", "All Time Touches"}}),
#"Null to Zero Leads" = Table.ReplaceValue(#"Renamed Touches",null,0,Replacer.ReplaceValue,{"All Time Leads"}),
#"Null to Zero Touches" = Table.ReplaceValue(#"Null to Zero Leads",null,0,Replacer.ReplaceValue,{"All Time Touches"}),
#"Added All Time Ghosts" = Table.AddColumn(#"Null to Zero Touches", "Ghosts All Time", each if [All Time Spend]=0 then "No Clicks Ever" else "Some Clicks"),
#"Added All Waste" = Table.AddColumn(#"Added All Time Ghosts", "Waste All Time", each if [All Time Touches]+[All Time Leads]=0 then "No Leads Ever" else "Some Leads"),
#"Removed Columns" = Table.RemoveColumns(#"Added All Waste",{"All Time Spend", "All Time Leads", "All Time Touches"})

 

in

#"Removed Columns"

 

Issue two: Left Join Merged Queries followed by a Table.ExpandTableColumn function.  Any unmatched data (where the expand function would return a null value) disappears and instead I get one row of "Error" values in every cell.  The error message is as follows: 

 

Expression.Error: We Cannot convert the value null to type Logical.  Details:

Value=

Type=Type

Source code for the table is as follows:

 

let
Source = Table.NestedJoin(#"Network Leads",{"Ad", "Display URL", "Network Campaign", "Ad group", "Year Month"},#"Ad Activity",{"Network Ad Name", "Display URL", "Network Campaign", "Ad Group", "Year Month"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"AiTRK Program", "Ai Ad ID"}, {"AiTRK Program", "Ai Ad ID"}),  

//This is where the row of errors shows up

#"Removed Duplicates" = Table.Distinct(#"Expanded NewColumn"),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Ad", "Display URL", "Ad group"})

 

in
#"Removed Columns"

 

 

Like I said, sounds like a bug. Do you have the install file for the previous version of Power BI. That may be the best chance of you making the deadline. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

The root source of the error was burried in the left side of the left outer join, below the 999 preview rows.  The problem was that I had concatenated three columns together, which included null values.  If I concatenate 3 columns together with one or all three values being null then I got the error message: 

Expression.Error: We cannot conver the value null to type Logical.

Details:

Value=

Type=Type

 

This expression error does not cause the table to fail until it is executed as part of a join, making it very hard to identify the step that caused the error.

How did you work around this? I literally have the same issue. I've been working with this same file for weeks and now I am all of sudden getting this error.  I don't understand anything that's changed.  @gsalas 

Your frustration at this made me laugh. I know the feeling all too well! Glad you found a fix.

 

For anyone else who's had this problem, I've also had this error, caused by null values being fed into a conditional column. I had appended two tables. For one of the columns, Table 1 had empty cells represented as "", Table 2 had them as null. In the appended table, this column was used as a condition for a conditional column (if value), but it didn't like the null values so threw up this error. I fixed by replacing the null values in Table 2 with "".

 

It's unfortunate these error messages are so vague. Took a bit of trial and error to figure this one out.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.