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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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

A
  • Question Question
Transforming a Table in Excel Power Query: Grouping and Splitting Rows Based on Unique Order IDs
Replies
3
Views
166
alex78
A
P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
372
alex78
A
B
  • Solved
Power Query - remove duplicates in one column, based on values in another column
Replies
4
Views
413
btwice
B
E
  • Question Question
Return most recent data - multiple criteria
Replies
0
Views
380
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
274
AlienSx
A

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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