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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
svessari
Frequent Visitor

Expression.Error: We cannot apply operator .. to types Null and Null

I'm getting the following error when trying expand table when merging two tables in Power Query in Power BI. The problem persist with all kinds of joins. There are no null values in the datasets.

 

 

 

 

 

> Expression.Error: We cannot apply operator .. to types Null and Null.
> Details:
>     Operator=..
>     Left=
>     Right=

 

 

 

 

 

This is how it looks after merging: 

26rzF.png

 

This is how it looks after hitting "Table" 

This is how it looks after hitting "Table"This is how it looks after hitting "Table"

 

When hitting "Error" this shows up. This is the same error message I get when I expand it. 

When hitting "Error" this shows up. This is the same error message I get when I expand it.When hitting "Error" this shows up. This is the same error message I get when I expand it.

 

1 ACCEPTED SOLUTION
svessari
Frequent Visitor

Okay, just to let others know that might have a similar problem I found out the solution. It was my bad, but there were instances where Table B in the merge process that had null values due to an error in the SQL database. Anyway, I replaced all those values and it worked! Thanks to Pete and Jing for the help!

View solution in original post

8 REPLIES 8
svessari
Frequent Visitor

Okay, just to let others know that might have a similar problem I found out the solution. It was my bad, but there were instances where Table B in the merge process that had null values due to an error in the SQL database. Anyway, I replaced all those values and it worked! Thanks to Pete and Jing for the help!

v-jingzhang
Community Support
Community Support

Hi @svessari 

 

Before you hit "Table", usually you can check the preview data of it. Can you click the blank white area in a cell and check the table data at bottom? Does this table display correct results after merging? 

vjingzhang_0-1657095831000.png

 

If the table doesn't have correct results, can you show how you merged two tables? 

 

Best Regards,
Community Support Team _ Jing

Thanks for the reply Jing.

 

It seems to be correct, apart from in every match there's a row at the bottom with "Error" in every column. 

You can see the picture attached, in row 31 there's a correct match, but additionally there's an "error row".

Capture.PNG

Regards,

Svessari

Hi @svessari ,

 

In your main query (that you're trying to merge onto), select the query step before the merge step, go to the dropdown at the top of the column you're trying to merge on and select 'Remove Empty'.

 

BA_Pete_0-1657190324526.png

 

If your merge step still doesn't work, do the same on your sub-query (the one you're trying to add to the main query).

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete,

 

Thanks for the quick reply.

I've removed all empty values in both columns in both tables I'm trying to merge but the Error is still the same. I also tried removing all errors prior to merging in all the columns.

 

P.S.

I'm working on providing you with the code from the Advanced Editor, it'll be ready soon.

 

Regards,

Svessari

BA_Pete
Super User
Super User

Hi @svessari ,

 

Can you select your query in Power Query, go to the Home tab > Advanced Editor, copy everything in there and paste into a code window here please?

Please remove any server/filepath information from the Source step.

 

Open code window:

BA_Pete_0-1656511799664.png

 

Thanks,

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete,

 

Here's the code:

 

Dataset 1

let
    Source = Sql.Database("dummy", "dummy2"),
    Table_name = Source{[Schema="dummy3",Item="dummy4"]}[Data],
 #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"col1"}, #"column1", {"coll2"}, "column2", JoinKind.LeftOuter),
    #"Expanded col1" = Table.ExpandTableColumn(#"Merged Queries", "col1", {„all_the_columns“}, {"all_the_columns2 "}),
    #"Merged Queries1" = Table.NestedJoin(#"col1", {"col_4}, #"another_table", {"all_the_columns", JoinKind.LeftOuter),
    #"Expanded col1" = Table.ExpandTableColumn(#"Merged Queries1", "another_table", {"all_the_columns"}, {"all_the_columns"}),
    #"Sorted Rows" = Table.Sort(#"Expanded another_table",{{"first_columns", Order.Descending}}),
    #"Merged Queries2" = Table.NestedJoin(#"Changed Type", {"ID"}, #"table3", {"ID"}, "table3", JoinKind.LeftOuter),
    #"Expanded OPS person1" = Table.ExpandTableColumn(#"Merged Queries2", "table3", {"all_the_columns"}),
#"Added Custom1" = Table.AddColumn(#"Added Conditional Column1", "Custom", each { Number.From([int1])..Number.From([int2]) })
in
    #"Added Custom1"

Dataset 2

let
    Source = Sql.Database("dummy1", "dummy2"),
    Table1 = Source{[Schema="dummy",Item="dummy2"]}[Data],
   #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each { Number.From([int1])..Number.From([int2]) }),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
in
    #"Filtered Rows4"

Dataset 3

let
    Source = Table.NestedJoin(#"dataset 1", {"col1", "col2"}, #"dataset 2", {"col1", "col2"}, "dataset 2", JoinKind.LeftOuter)
in
    Source

 

 

 

What I suspect is the problem is is that I expand both Dataset 1 and Dataset 2 by two integer columns in each dataset, then I change the type to dates. After that I try to merge them together based on two columns in each dataset. That's when the error shows up.

 

Regards,

Svessari

 

 

 

 

Ok, so there's quite a bit going on here.

 

Can you confirm whether you've changed any of the previous step references in your presented code please? I understand you've put in the "all the columns" placeholders which is cool, but have you changed any of the previous step references as well? There's loads of instances where code lines don't appear to refer back to the previous code step.

For example:

BA_Pete_1-1657198920507.png

 

The key one that might cause your error would be this one:

BA_Pete_2-1657199012800.png

 

The error you've shown that you're getting is a failure to create a contrived list (using the ' .. ' operator). As your #"Added Conditional Column1" step doesn't appear to exist, this would explain why your [int1] and [int2] values would be null when trying to contrive a list between them and, hence, the "We can't apply operator '..' to null and null" error.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors