Power Query Merge Causing Dropped Rows

ilya2004

Board Regular
Joined
Mar 17, 2011
Messages
135
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?
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,670
I see nothing wrong with merge and Left Outer method then expand

IDNameIDSalaryIDNameSalary
M10AlanM10
100​
M10Alan
100​
M20BillM20M20Bill
M30CecilM30
250​
M30Cecil
250​
M40DirkM40M40Dirk
M50EdgarM50
555​
M50Edgar
555​

maybe post your M-code?
 
Last edited:

ilya2004

Board Regular
Joined
Mar 17, 2011
Messages
135
Here is the relevant part of the code:

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

Well-known Member
Joined
Oct 24, 2015
Messages
6,670
this code works
Code:
[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

Board Regular
Joined
Mar 17, 2011
Messages
135
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,670
hard to say something without having a look at all
here is example with external file and it works
Code:
[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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top