CODEWITHSUNDEEP
powerquerypower-automate
Ihavenomouth yetimusttest
Ihavenomouth yetimusttest

Reputation: 1

Using Power Automate to automate vlookup/data cleaning

I am looking for the best way to automate a task that I currently do manually via vlookup in Excel.

In one table, I have an export of contacts from a CRM system, like this:

Name Email ProductSlot1 ProductSlot2 ProductSlot3
John [email protected] Car (Red) Bike (Blue)
Jane [email protected] Horse (Orange)
Henry [email protected] Car (White) Horse (Orange) Bike (Blue)

In another, I have the ‘lookup table’ I need to translate the ProductSlotX values so that they can be used as triggers for dynamic content in the email management system (because reasons).

This looks like this:

Product CRM name Product EMS name
Car (Red) Red Car
Bike (Blue) Blue Bike
Horse (Orange) Orange Horse

Ideally, I’d want to upload an export CSV/Excel Workbook to a folder and have whatever method rewrite the values from CRM to their EMS equivalent and then spit out a cleaned CSV that I can then import into the EMS. This would look like this:

Name Email DynamicSlot1 DynamicSlot2 DynamicSlot3
John [email protected] Red Car Blue Bike
Jane [email protected] Orange Horse
Henry [email protected] White Car Orange Horse Blue Bike

I’ve looked into Power Automate (cloud and desktop) and Power Query/BI, but have hit a dead end as they’re either too ‘on rails’ (i.e. want specified files) or where they let me use variables, the results are added as rows to one master table/sharepoint list, which would then require additional cleaning before I could download and import.

Any suggestions would be most welcome. Thanks in advance!

^^^^^^^^^^^^^^^^^^^^

Upvotes: 0

Views: 1408

Answers (1)

Frédéric LOYER
Frédéric LOYER

Reputation: 1064

You can have the formulae which add a column :

AddColumn1 = Table.AddColumn(Source, "EMS Name", 
                    each if Text.Contains([ProductSlot1], "(") 
                         then
                           Text.BetweenDelimiters([ProductSlot1], "(", ")")
                           & " " & Text.BeforeDelimiter([ProductSlot1], "(")
                         else "error")

You should specify what happens if the form Name (color) is not respected.

If you want to rely on a EMS mapping table (you have to declare in PowerBI), you have:

Fusionned = Table.NestedJoin(Typed, {"ProductSlot1"}, EMS_mapping,  
                            {"Product CRM name"}, "EMS_mapping",
                            JoinKind.LeftOuter),
With_EMS_mapping = Table.ExpandTableColumn(Fusionned, "EMS_mapping",
                            {"Product EMS name"},
                            {"EMS_mapping.Product EMS name"})

This query has been done graphically (Merge query then On the new column, an expansion with only the EMS name column). I guess it will be easier like this than integrated my snipset in your query.

The source or Typed input table depends of your environment.

Upvotes: 0

Related Questions