March Conundrum Power Query Problem and the Correct Path to Take !!!!!

[daleholden]

Hi Guys & Girls
Here we go head scratcher of the month ? I will try my best to explain without boring you to death.
I have three connection files in Power Query that I know what I want to do but not sure which path to go down.
So let me explain the table names and what they do:-
1. eBayCatCodes is the master eBay List of codes that relates to all items on eBay.
2. eBay FVF CATs is eBay compressed list from above all put into 68 master categories.
3. eBay File Exchange stored in a folder which is a daily file from eBay to me showing my active listings. This only has the Code IDS in it.
I need to merge or append or some other kewl way to lookup the codes to see where they belong exactly. But I need them to show the correct ID from the compressed list as in eBay FVF CATs.

So an example would be as follows.
If you search for 11071 you will see I have 57 items these are listed as Sound & Vision > TVs I need the merge or append to go through the compressed table and return line 58 TVs.
From there I can then add some further columns to work out Fees. I hope this makes sense if you need further examples or a better explanation please let me know.

Working file can be found at this link https://1drv.ms/x/s!Ao6WCX2miTCJh6IAaGbBxOtZxXlu4w feel free to download and edit.
Cheers

 

[sandy666]

Working file can be found at this link https://1drv.ms/x/s!Ao6WCX2miTCJh6IAaGbBxOtZxXlu4w feel free to download and edit.

there is nothing to edit because of hardcoded path to source file(s)

 

[daleholden]

Try now i have added the eBay File Exchange to the workbook

 

[sandy666]

I tried

 

[daleholden]

HI

I am not sure how best to share it so you can look at it. If i add it to the workbook it will be no use putting it in the workbook as i need it to be in the folder. If I give you a share to the file you can edit the source, if you are ok with that.


Link to folder file https://1drv.ms/u/s!Ao6WCX2miTCJh6INcJ0y8bb5Rft7Pg

 

[sandy666]

ok, and what you want to achieve?

eg. look for 11071 ? where? in exchange table?
or.....?

or you type in cell this code and want return all values for this code?

 

[daleholden]

I download FileExchange Daily I want to scan for new SKUs which are not currently in the list & look at their Cat codes look at the categories then make them match the short version and add the short version to a table so I can then calculate fees correctly

 

[sandy666]

Prepare Exchange table suitably (remove unnecessary columns, sort, etc.)
Merge Queries
first table: eBayCatCodes
second table: Exchange (daily downloaded)
Bottom: select kind of join you need

or

you can stay with two columns in table Exchange, rename columns suitably to table eBayCatCodes then Append them and remove duplicates


Edit:
btw. use the names existing there not "daily downloaded" or SKU but table Exchange (in my case, yours can be different) or Category Number / Category ID
read my first bottom line

 

[sandy666]

too late to edit...

but as I can see there are no columns to Append except Category Number (Exchange) and CategoryID (eBayCatCodes) so Merge Queries is a better option

 

[daleholden]

HI
My initial question was asking the best way

"I need to merge or append or some other kewl way to lookup the codes to see where they belong exactly. But I need them to show the correct ID from the compressed list as in eBay FVF CATs."

Currently, I have a worksheet which shows me the new skus added I then paste them into another worksheet which does the lookup and enter the titles etc. But to work out exact fees I need to manually add one of the 57 cats from the FVF sheet which I have a dropdown.

If i add 100 items this is a long drawn out task daily. But I noticed FIleExchange had the codes so I need to lookup and make them match 1 of the 57 titles. This will enable me to work out fees correctly, hope that makes sense.