Forum Discussion
Natural Left join not capturing all the data
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!
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
002 is only present in the right table.
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
