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]

check this one

ZIP file

unzip to D harddrive directly (path should be: D:\daleholden

from Merge1 you can remove unnecessary columns

after any changes in DV list (Lookup table) you need Refresh green table or use Ctrl+Alt+F5

to connect your folder on D drive simply change existing Source Path to : D:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay File Exchange

 

[daleholden]

HI
Sorry, I have no idea what you have done! It is nothing like I am trying to achieve.

You have created a connection to the Category leaf name this is of no use really.

We need to convert the codes that are listed in the File Exchange which is upto 13,000 down to one of the 57 from the FVF.

The dropdown box is no use and not sure if you have understood what i am trying to achieve.

 

[sandy666]

HI
not sure if you have understood what i am trying to achieve.

probably not

show example what you want to achieve , use real column names and table names

 

[daleholden]

HI

I will try to explain as best I can in the eBayCatCodes worksheet we have all the codes eBay uses.
So for a simple example Antiques has 301 different codes but it will always be charged at 9% if you look at eBay FVF CATs worksheet. So any 1 of the 301 codes needs naming as Antiques in the same row of the product. From here we can add a formula to work out the FVF final value fee.
So if excel sees any of the 301 codes it names it as Antique.

So if you look at the eBay FVF CATs you will see some CATS not coloured these are singular primary Category names this should be easy to achieve (I think)
The coloured Categories have Sub Cats which go in another level and some not many go in 2 levels.
So let’s take Home Furniture DIY 989 possibilities in the eBayCatCodes which at the top level is 10%.
But it has sub CATS which have different levels
Appliances 5%
DIY Tools 5%
Smart Speakers 5%
Furniture 9%
Bath 9%
So in the Home, Furniture & DIY > Appliances we have 94 codes we need Excel to be able if it sees Home, Furniture & DIY > Appliances it needs to add Appliances into the row.

So from the File Exchange file power query to go through the codes and add 1 of the 68 FVF Categories names.
Hope this helps and makes more sense.



https://1drv.ms/u/s!Ao6WCX2miTCJh6IfgBxcHgq65jH85Q

 

[sandy666]

sorry mate, I don't understand your conception
I asked you about example with detailed description, real name tables and real name columns
and expected result (few rows)

maybe someone else will undesrtand you

as I said: I know you know but I don't know what you know

have a nice day

 

[daleholden]

Hi
Sorry I thought my explanation who be clear enough.
I will put a sheet together showing what I am trying to achieve tomorrow
I really appreciate your help

 

[sandy666]

I didn't help at all
maybe if I'll see something more (like for dummies, please )

 

[daleholden]

HI Jerry

Please, can you view the file I have added a new tab with a graphic explanation?

Let me know if you need further explanation or clarification.

I guess the eBay FVF CATs table may need reconfiguring but just looking for the best way to do this

 

[daleholden]

Hi Jerry

You had any luck with this file?

HI Jerry

Please, can you view the file I have added a new tab with a graphic explanation?

Let me know if you need further explanation or clarification.

I guess the eBay FVF CATs table may need reconfiguring but just looking for the best way to do this