Hi everyone.
Lesson 3.05 for Power Query introduces transformation tables. The example is fairly simple as each table has only a few rows.
I'm trying to figure out the best way to deal with this in Power Query for Power BI. Do I need to use a query to determine which columns can't match between tables, export it to Excel, type in the values I want, and then forever use it as a source for my query?
Or is there a simpler way to do it within Power Query?
Thanks.
Hi Nate,
I'm not following what you want to achieve. Please upload a mock up in Excel of your scenario that illustrates the issue and desired result so I can help you.
Mynda
Thanks for the reply, Mynda. I'll use your Fuzzy Matching sheet to demonstrate.
You have a list of companies and products, but the company name is not consistent. You then have two more tables: one with the correct company names and the other with the transformation for the names that need help matching up.
I understand how to do that in Excel, but here's my scenario:
I extract a list of paychecks from a payroll system into Power BI's Power Query. I then extract a list of time entries from a time tracking system.
The payroll system is very exact. It has names like "Smith, John". But the time tracking system uses preferred names so "Smith, John" in the time tracking system might just be "JT". Fuzzy matching won't catch this so I have to use a transformation table. Do I need to create an Excel spreadsheet for nearly 100 employees and just have that as a data source for my PQ, or is there an easier way to build a transformation table in Power BI's PQ so I don't have to reference an external sheet?
For example, in Power Query, can I somehow create a list of the names that don't match and then just type in the values I need them transformed to?
Hi Nate,
The simplest way would be to have a table with the 100 names. You can generate this using Power Query that completes the names that do match and leaves blanks for the names that don't so you can just fill in the blanks, and then use that table as your source.
i.e. create a new query that contains the payroll name in one column and the time tracking names in another and close & load to Excel. Then copy and paste this data into a new non-Power Query table and type in the missing names. Then load this new table to Power Query and use as your lookup table. You can then delete the original query with the two name columns.
HTH.
Mynda
Thanks Mynda! I'll see if that works for.
I don't think I can delete the 'original query with the two name columns' because the clients' employees are changing frequently so I have to continually update the lists.
Ah, in that case, you can use your new table to lookup the missing names and bring them into your query with the other staff data.
