Forum Discussion
Expression.Error: We cannot apply operator .. to types Null and Null
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!
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
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:
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