power query, vlookup (left join) unwanted sort

[nikio8]

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

		Result .
1		1
2		1
3		1
1		1
2		2
3		2
1		2
2		2
3		3
1		3
2		3
3		3

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[nikio8]

RL	Description		Join		Join	Table7.RL	Table7.Description
1	One		1		1	1	One
2	Two		2		1	1	One
3	Three		3		2	2	Two
4	Four		1		2	2	Two
5	Five		2		3	3	Three
			3		3	3	Three
			1		1	1	One
			2		2	2	Two
			3		3	3	Three
			1		1	1	One
			2		2	2	Two
			3		3	3	Three

<tbody>
</tbody>

 

[sandy666]

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

Index	Join	Table1.RL	Table1.Description
1​	1​	1​	One
2​	2​	2​	Two
3​	3​	3​	Three
4​	1​	1​	One
5​	2​	2​	Two
6​	3​	3​	Three
7​	1​	1​	One
8​	2​	2​	Two
9​	3​	3​	Three
10​	1​	1​	One
11​	2​	2​	Two
12​	3​	3​	Three

 

[nikio8]

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.

 

[sandy666]

did you do it all in PQ Editor?

could you post sample data (source) in table(s) form?

 

[nikio8]

did you do it all in PQ Editor?

could you post sample data (source) in table(s) form?

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?

 

[sandy666]

That will work, but this glitch is strange.
On my second try i got:
112233 123 123.

I did it again and got the same, proper result

Join	Index	Table2.RL	Table2.Description	Table2.Index
1​	1​	1​	One	1​
2​	2​	2​	Two	2​
3​	3​	3​	Three	3​
1​	4​	1​	One	4​
2​	5​	2​	Two	5​
3​	6​	3​	Three	6​
1​	7​	1​	One	7​
2​	8​	2​	Two	8​
3​	9​	3​	Three	9​
1​	10​	1​	One	10​
2​	11​	2​	Two	11​
3​	12​	3​	Three	12​

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"