power query, vlookup (left join) unwanted sort

nikio8

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
Hi all, thanks for your help in advance.

I get this weird error. I am joining a list (lookup) to a table (result).
Using pq to save time and not need vlookup on each Result table, however it is no good that the result is sorted. I am not sure why this happens, Lookup table is connection only... ? There are no extra rows - no duplication in result table.



Lookup[TABLE="width: 158"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Result .[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
[TABLE="width: 631"]
<tbody>[TR]
[TD]RL[/TD]
[TD]Description[/TD]
[TD][/TD]
[TD]Join[/TD]
[TD][/TD]
[TD]Join[/TD]
[TD]Table7.RL[/TD]
[TD]Table7.Description[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]One[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]One[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Two[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]One[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Three[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Four[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Five[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]Three[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]Three[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]One[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]Three[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]One[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD]Three[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
try this way
- add index to both tables
- merge these tables as you did
- sort by index
- remove unnecessary columns
- load to the sheet

I didn't remove index to show how it works

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Index[/td][td]Join[/td][td]Table1.RL[/td][td]Table1.Description[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]One[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]
2​
[/td][td]
2​
[/td][td]Two[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td]
3​
[/td][td]
3​
[/td][td]Three[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4​
[/td][td]
1​
[/td][td]
1​
[/td][td]One[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
5​
[/td][td]
2​
[/td][td]
2​
[/td][td]Two[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6​
[/td][td]
3​
[/td][td]
3​
[/td][td]Three[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
7​
[/td][td]
1​
[/td][td]
1​
[/td][td]One[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
8​
[/td][td]
2​
[/td][td]
2​
[/td][td]Two[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
9​
[/td][td]
3​
[/td][td]
3​
[/td][td]Three[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
10​
[/td][td]
1​
[/td][td]
1​
[/td][td]One[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
11​
[/td][td]
2​
[/td][td]
2​
[/td][td]Two[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12​
[/td][td]
3​
[/td][td]
3​
[/td][td]Three[/td][/tr]
[/table]
 
Last edited:
Upvote 0
Thanks Sandy. That will work, but this glitch is strange.
On my second try i got:
112233 123 123.
Really weird. Would be nice to know why this happens.
 
Upvote 0
did you do it all in PQ Editor?

could you post sample data (source) in table(s) form?
 
Upvote 0
sandy666 said:
did you do it all in PQ Editor?

could you post sample data (source) in table(s) form?
Click to expand...
Hi Sandy, that was the entire data. I made two tables and used left join instead of lookup. that was the result. 123 123 123 so I can see the pattern. i was not expecting 111 222 333.
Do you not get the same?
 
Upvote 0
nikio8 said:
That will work, but this glitch is strange.
On my second try i got:
112233 123 123.
Click to expand...

I did it again and got the same, proper result

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Join[/td][td=bgcolor:#70AD47]Index[/td][td=bgcolor:#70AD47]Table2.RL[/td][td=bgcolor:#70AD47]Table2.Description[/td][td=bgcolor:#70AD47]Table2.Index[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#FFFF00]
1​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]One[/td][td=bgcolor:#FFFF00]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td=bgcolor:#FFFF00]
2​
[/td][td]
2​
[/td][td]Two[/td][td=bgcolor:#FFFF00]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#FFFF00]
3​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]Three[/td][td=bgcolor:#FFFF00]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td=bgcolor:#FFFF00]
4​
[/td][td]
1​
[/td][td]One[/td][td=bgcolor:#FFFF00]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#FFFF00]
5​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]Two[/td][td=bgcolor:#FFFF00]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td=bgcolor:#FFFF00]
6​
[/td][td]
3​
[/td][td]Three[/td][td=bgcolor:#FFFF00]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#FFFF00]
7​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]One[/td][td=bgcolor:#FFFF00]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td=bgcolor:#FFFF00]
8​
[/td][td]
2​
[/td][td]Two[/td][td=bgcolor:#FFFF00]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#FFFF00]
9​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]Three[/td][td=bgcolor:#FFFF00]
9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td=bgcolor:#FFFF00]
10​
[/td][td]
1​
[/td][td]One[/td][td=bgcolor:#FFFF00]
10​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#FFFF00]
11​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]Two[/td][td=bgcolor:#FFFF00]
11​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td=bgcolor:#FFFF00]
12​
[/td][td]
3​
[/td][td]Three[/td][td=bgcolor:#FFFF00]
12​
[/td][/tr]
[/table]

Code: 
let
    Source = Table.NestedJoin(Table1,{"Index"},Table2,{"Index"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"RL", "Description", "Index"}, {"Table2.RL", "Table2.Description", "Table2.Index"})
in
    #"Expanded Table2"
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
349
alex78
A
B
  • Solved
Power Query - remove duplicates in one column, based on values in another column
Replies
4
Views
370
btwice
B
E
  • Question Question
Return most recent data - multiple criteria
Replies
0
Views
364
Exceloway
E
W
  • Question Question
Excel Power Query - Split invoicing data table at the row before last instance of a header row
Replies
1
Views
271
AlienSx
A
Ramballah
  • Solved
VLookup in Power Query?
2
Replies
16
Views
523
Ramballah
Ramballah

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top