Power Query Merge Causing Dropped Rows

%3CLINGO-SUB%20id%3D%22lingo-sub-355484%22%20slang%3D%22en-US%22%3EPower%20Query%20Merge%20Causing%20Dropped%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355484%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20know%20what%20is%20causing%20these%20but%20I%20am%20doing%20a%20merge%20with%20Power%20Query%20and%20losing%20rows%20when%20I%20click%20on%20expand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20simplicity%20sake%20I%20have%20two%20tables%2C%20one%20has%20Employee%20Number%20and%20Name%20and%20the%20other%20has%20Employee%20Number%20and%20Salary.%20Not%20all%20employees%20have%20a%20salary%20on%20the%20second%20table%20so%20I%20did%20a%20left%20hand-join%20using%20the%20names%20table%20as%20the%20master.%20When%20I%20do%20the%20join%20on%20step%20where%20it%20is%20just%20the%20merge%2C%20all%20of%20the%20data%20is%20still%20there%2C%20but%20as%20soon%20as%20I%20try%20to%20expand%20the%20salaries%20table%20to%20load%20that%20into%20the%20model%20rows%20start%20to%20disappear.%20The%20problem%20is%20that%20I%20then%20merge%20in%203%20more%20tables%20with%20other%20information%20the%20same%20way%20and%20by%20the%20time%20I%20am%20done%20my%2010k%20row%20table%20is%20down%20to%20700%20rows.%20Any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-355484%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epower%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355579%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Merge%20Causing%20Dropped%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355579%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20strange.%20In%20formula%20bar%20your%20merge%20step%20looks%20like%3C%2FP%3E%0A%3CPRE%3E%3D%20Table.NestedJoin(tblNames%2C%7B%22ID%22%7D%2CtblSalary%2C%7B%22ID%22%7D%2C%22tblSalary%22%2CJoinKind.LeftOuter)%3C%2FPRE%3E%0A%3CP%3E%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I don't know what is causing these but I am doing a merge with Power Query and losing rows when I click on expand.

 

For simplicity sake I have two tables, one has Employee Number and Name and the other has Employee Number and Salary. Not all employees have a salary on the second table so I did a left hand-join using the names table as the master. When I do the join on step where it is just the merge, all of the data is still there, but as soon as I try to expand the salaries table to load that into the model rows start to disappear. The problem is that I then merge in 3 more tables with other information the same way and by the time I am done my 10k row table is down to 700 rows. Any ideas?

1 Reply
Highlighted

That's strange. In formula bar your merge step looks like

= Table.NestedJoin(tblNames,{"ID"},tblSalary,{"ID"},"tblSalary",JoinKind.LeftOuter)

?