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

[svessari]

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: 

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.

[svessari · Accepted solution]

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]

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? 

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

Best Regards,
Community Support Team _ Jing

[svessari]

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".

Regards,

Svessari

[BA_Pete]

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'.

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

[svessari]

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]

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:

Thanks,

Pete

[svessari]

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

[BA_Pete]

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:

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

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