Power Query Merge Causing Dropped Rows

[ilya2004]

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?

 

[sandy666]

I see nothing wrong with merge and Left Outer method then expand

ID	Name		ID	Salary		ID	Name	Salary
M10	Alan		M10	100​		M10	Alan	100​
M20	Bill		M20			M20	Bill
M30	Cecil		M30	250​		M30	Cecil	250​
M40	Dirk		M40			M40	Dirk
M50	Edgar		M50	555​		M50	Edgar	555​

maybe post your M-code?

 

[ilya2004]

Here is the relevant part of the code:

let
    Source = Excel.Workbook(File.Contents("[Path Hidden]\Starting Data.xlsx"), null, true),
    #"Historical Salary Data_Sheet" = Source{[Item="Historical Salary Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Historical Salary Data_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SSO ID", Int64.Type}, {"Full Name", type text}}),
    #"Merged Queries2" = Table.NestedJoin(#"Changed Name",{"SSO ID"},#"IC Data",{"SSO ID"},"IC Data",JoinKind.LeftOuter),
    #"Expanded IC Data" = Table.ExpandTableColumn(#"Merged Queries2", "IC Data", {"IC Amount"}, {"IC Amount"}),

Right after I expand IC Amount, rows disappear.

 

[sandy666]

this code works

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"ID"},Table6,{"ID"},"Table6",JoinKind.LeftOuter),
    #"Expanded Table6" = Table.ExpandTableColumn(#"Merged Queries", "Table6", {"Salary"}, {"Salary"})
in
    #"Expanded Table6"[/SIZE]

if your code is TopSecret I will not guess what is right and what is not

Have a nice day

 

[ilya2004]

The code is not top secret, that's all it is. I just removed the file path and extra columns that aren't relevant to the question.

 

[sandy666]

hard to say something without having a look at all
here is example with external file and it works

[SIZE=1]let
    Source = Excel.Workbook(File.Contents("D:\ilya\src.xlsm"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Merged Queries" = Table.NestedJoin(#"Promoted Headers",{"ID"},Sheet2,{"ID"},"Sheet2",JoinKind.LeftOuter),
    #"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged Queries", "Sheet2", {"Salary"}, {"Salary"})
in
    #"Expanded Sheet2"[/SIZE]

so try to recognize yourself where is the "error"

btw. it's hard to create an example and post link to the shared file(s) here?