Make a Dictionary to add words to a column based on another column in PoweQuery

[jaguilera98]

Hi, I'm new to PowerBI and PowerQuery. Right now I'm doing my internship at a shipping company, and the task I've been assigned to is to sort the data of the different products that have been shipped in the past, along the services provided and the prices charged for it, and after that make a report in PowerBI.

The idea is for this sorted data to be updated dynamically whenever the original database (some Excel files) is updated (so that the work I do doesn't become obsolete after I finish my internship). The company has offices in 6 different countries, where each country inputs their data in different ways, so there isn't a "common" way for exported products to be recorded between countries. For example, in country A a batch of grapes could be named "Fresh Grapes" where as in country B it could be recorded as "GRAPES" and in C just named "Fresh G" and so on.

I'm looking for a way in which I could have a "dictionary" of common names for each way a product is inputed, and to add a new column where, depending on the original product column, a certain value from the list is added. For example; if I'm working with country A's report in PowerQuery and the product is "Fresh Grapes", then the value in the new column would be "Grapes", if the value is either "Oranges" or "Lemons" then the new column's value would be "Citrus". If I'm working with country B then "GRAPES" would be turned into "Grapes" in the new column, "Orange" into "Citrus" and so on.

Since this has to still work after I'm done with my internship I can't just replace/add the values manually, also using if statements might be too much considering there are over 600 input products in different ways (and over 10000 rows in each DB), for example in country A instead of inputing "Fresh Grapes" they sometimes write "Fresh Grape".

The solution I was thinking off, based on my experience with Python, was to make a Dictionary in PowerQuery for each Excel DB, where as the key would be the originally input value and the value would be the "common name". For example orange:citrus, lemons:citrus, fresh grapes:grapes and so on, but I'm still fairly new to PowerBI and PowerQuery, so I don't know the way to do it (or if there is a way) without using over 500 if statements, which might put a big burden on the pc.

Any help, insight or different aproach to this problem will be highly appreciated, so thank you in advance for any help with this 🙂

[Greg_Deckler]

@jaguilera98 Sounds like you want a Merge query. Basically create a "lookup" table like:

Grapes, Grapes

Fresh Grapes, Grapes

Oranges, Citrus

Lemons, Citrus

Orange, Citrus

Lemon, Citrus

You then add a Merge query step into your query to merge in the 2nd column based on the 1st column. Hard to get more detailed without more information.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jaguilera98]

@Greg_Deckler  Thanks for taking the time to answer this, I'll give a bit more context and an example of what I want to do.
I would like to take the data from the product name column and create a new column where, depending on the product name, a new name is writeen, just so orders of the same product can be counted as one.
Mostly because sometimes the person creating a new export order inputs "organic mangos", but another person inputs "fresh mangos" and, at the end of the day, both orders are still just mangos, and the report I've been asked to make ha to show what are the most exported products, their prices, and such, so the first step is for me to have a common name for products which are named differently but are the same thing, here's an small example to better show what I want to do: the second column "Product" represents the product that was input by the person, the third column "New Name" represents the name to be added automatically based on the second column.

ORDER ID	Product	New Name
1	FROZEN MANGOS	MANGOS
2	FRESH MANGOS	MANGOS
3	FRESH GRAPES	GRAPES
4	FRESH ORGANIC MG	MANGOS
5	FRESH MANGOS	MANGOS
6	FRESH GRAPES	GRAPES
7	FROZEN GRAPES	GRAPES
8	FRESH MANGOS	MANGOS
9	FRESH MGOS	MANGOS
10	FRESH GP	GRAPES
11	FRESH MG	MANGOS
12	FRESH LEMONS	CITRUS
13	FZ MANGOS	MANGOS
14	FROZEN MANGOS	MANGOS
15	FROZEN GRAPES	GRAPES
16	FROZEN ORGANIC MG	MANGOS
17	FRESH GRAPES	GRAPES
18	ORGANIC GRAPES	GRAPES
19	FRESH BANANAS	BANANAS
20	BNN	BANANAS

In the meantime I'll be trying what you have said, I started learning to use PowerBI and PowerQuery just a couple days ago so it might take me sometime. Once again thank you for your reply! 🙂

[Greg_Deckler]

@jaguilera98 You may find PQ's fuzzy matching useful although it is somewhat limited. If you find yourself needing a more flexible approach, I created a DAX fuzzy matching algorithm here: Fuzzy - Microsoft Power BI Community Same basic approach, having a "lookup" table but implemented in DAX so that you have full control over the fuzzy matching aspect.

[Anonymous]

Hi @jaguilera98 ,

You need to create a lookup table with one column for the product name and another column for the corresponding name/category.

In Power Query Editor, you can use Merge Queries.

Merge queries overview

In Desktop, You can create a relationship in the [Product] field between the fact table and the lookup table, and create calculated columns in the fact table.

Name = RELATED('DimTable'[Name])

RELATED

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum