title: Power Query Merging on Null values
body: <p>I was very surprised to find out that <code>Table.NestedJoin</code> joins columns join on <code>null</code>.  This seems like the wrong choice to me, but <em>the compiler is always right</em>.  In the example below, <code>Table1</code> and <code>Table2</code> (which are otherwise identical) are left joined by <code>ID</code>.</p>
  
  <p><a href="https://i.stack.imgur.com/eF27e.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eF27e.png" alt="enter image description here"></a></p>
  
  <pre><code>let
      Source = Table.NestedJoin(Table1,{"ID"},Table2,{"ID"},"Table2",JoinKind.LeftOuter),
      #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ID", "Index"}, {"Table2.ID", "Table2.Index"})
  in
      #"Expanded Table2"
  </code></pre>
  
  <p>My question is: are there any known best practices to get joins to behave more like standard SQL joins (where <code>null &lt;&gt; null</code>).</p>
  
  <p>This is, of course, beyond the simple method of FIND/REPLACE <code>null</code> with <em>special thing that no one could possibly use in arbitrary data...that someone will have inevetably used in arbitrary data</em>.</p>
  
  <p>Thanks in advance for any help.</p>