Natural Left join not capturing all the data

[Anonymous]

Hello,

I have two tables with a one in one relation. Table 1:
UID

01.A
01.B
02
03
04
05
06
07
08
09
10
11
12
13
14
217
350
430
56
6011
6012
6013
602
603
604
605
607
608
609
610
613
616
617
619
621
622
624
627
628
629
630
632
633
634
635
636
637
640
641
701
703
704
707
709
710
711
713
715
719
720
722
723
724
725
728
729
730
731
732
733
734
735
736
737
738
740
741
742
743
745
746
747
749
750
751
752
753
754
757
758
759
763
764
769
771
772
773
776
777
778
779
782
783
784
785
787
788
792
793
811
812
813
815
816
817
818
819
821
822
823
824
825
826
827
828
840
850
851

Table two:
Cost_Code

01.B
01.A
02
03
6011
6013
06
604
608
632
605
609
603
607
613
617
14
628
629
13
636
637
04
09
633
707
10
630
635
634
627
12
621
624
731
05
619
703
724
715
722
610
709
711
640
641
741
725
616
704
710
723
728
729
735
743
749
750
751
713
719
730
734
740
742
758
763
769
732
733
737
754
757
759
764
776
777
778
787
622
782
792
720
745
747
785
736
771
779
783
002
784
850
11
788
793
08
752
753
819
811
813
827
07
818
840
824
825

For some reason when I use NaturalLeftJoin(Table2, Table1) the resulting table misses "002". It will be great if someone could explain why this is happening thanks!

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample, and merge queries use Left Outer join kind(Table2, Table1).

After expand table, as 002 is the only one in Table2 but not in Table1, it goes to the last line by rules. I doubt if it got to the last line you didn't find it.

I attach my sample below, please go to Power Query applied steps to check.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

002 is only present in the right table.