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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]Name[/td][td][/td][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]Salary[/td][td][/td][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Salary[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]M10[/td][td=bgcolor:#DDEBF7]Alan[/td][td][/td][td=bgcolor:#DDEBF7]M10[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#E2EFDA]M10[/td][td=bgcolor:#E2EFDA]Alan[/td][td=bgcolor:#E2EFDA]

100​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M20[/td][td]Bill[/td][td][/td][td]M20[/td][td][/td][td][/td][td]M20[/td][td]Bill[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]M30[/td][td=bgcolor:#DDEBF7]Cecil[/td][td][/td][td=bgcolor:#DDEBF7]M30[/td][td=bgcolor:#DDEBF7]

250​

[/td][td][/td][td=bgcolor:#E2EFDA]M30[/td][td=bgcolor:#E2EFDA]Cecil[/td][td=bgcolor:#E2EFDA]

250​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M40[/td][td]Dirk[/td][td][/td][td]M40[/td][td][/td][td][/td][td]M40[/td][td]Dirk[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]M50[/td][td=bgcolor:#DDEBF7]Edgar[/td][td][/td][td=bgcolor:#DDEBF7]M50[/td][td=bgcolor:#DDEBF7]

555​

[/td][td][/td][td=bgcolor:#E2EFDA]M50[/td][td=bgcolor:#E2EFDA]Edgar[/td][td=bgcolor:#E2EFDA]

555​

[/td][/tr]
[/table]

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?