Mapping table or grouping a description column by category

Power Query
1

Hi,
I would like to have some ideas or bets practices on how to do a mapping table to group a transactional table.
here is the situation:
I have a general ledger transaction report and I need to identify the vendor which is in the description column. The description column also contains dates, service, locations etc.
the current process to map the vendor is as follow:
• duplicate the description column
• remove dates and symbols, in order to get an almost unique description.
• export the table to excel and manually add the vendor ID
• add this table to PQ again and merge it with the transaction table and extract the new vendor id column.
The question is if there is a more efficient way to do it, like for example using a list/table of all vendors and do a list contain look up to find the vendor in the description and then add the ID. if this is possible I would like to know how to do it or where can I find a similar case.
Thank you very much
Humberto

2

Hi @Hsilval,

Welcome back.

I would definitely explore a method like this.
Can you provide some dummy data in excel? That includes all of challenges you’re likely to encounter. Happy to explore that with you.

1 Like
3

Hi Melissa,
thank you very much for looking at this question. Attached a small subset of data with the query to modify the description and the additional table were I do the mapping between the new description and the vendor name.
please let me know if this example is clear enough or of if you need me to add any additional information.
thank you
Humberto
Mapping.xlsx (25.3 KB)

4

Hi @Hsilval,

Give this a go.
It sees if the Description contains a Vendor name and retuns the first match.

let
    Vendors = List.Buffer(  List.Distinct( Mapping[Vendor] )),
    Source = Excel.CurrentWorkbook(){[Name="Transaction"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"date", type datetime}, {"text", type text}, {"amount", Int64.Type}}),
    GetVendor = Table.AddColumn(ChType, "Vendor", each List.Select( Vendors, (x)=> Text.Contains([text], x, Comparer.OrdinalIgnoreCase )){0}?, type text )
in
    GetVendor

I hope this is helpful

1 Like
5

Hi Melissa, sorry for the delay.
Thank you very much for your help. I will try your suggestion and let you know how it goes.
regards
Humberto

6

Hi Melissa,
Thank you very much for your help, it works perfect.
just one question, what if instead of bringing the vendor name I want the vendor ID. So now I have two columns ( vendor name = the word looked and the vendor ID = the values added in the new column). it is a total different scenario or just need a small change?
thanks a lot again for your help.
Regards
Humberto

7

Hi @Hsilval,

There are always multiple ways to achief a certain result… But have you tried a simple Merge on the Vendor column to retrieve that ID?

1 Like
8

Thank you very much Melissa,
yes that would be the solution I can manage base on my knowledge :).
Just it was a bit of understanding in a theoretical way how can be done using kind of the same solution to avoid the merge step.
but I am very happy and grateful with the solution you provided.
regards
Humberto