SOLVED

Power Query: Replacing column values with only those included in list parameters

Copper Contributor

Hi,

 

I am trying to "normalize" similar data exports that were sent to me last year and this year, so I can then append them and work a comparison between the two. Just so I'm sure I am not comparing carrots with turnips, because the platform exporting the data has undergone some changes over the two years (grrrr...) I need to make sure that the values in several columns conform only to a set of finite possibilities. I have entered those into a list parameter called "Niveau atteint":

{"A1 non atteint", "A1", "A1+", "A2", "A2+", "B1", "B1+", "Au-delà de B1", "N/A"} meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = "List"]

 What am I to do so that I can get Power Query to compare the values in the columns in question and match them to the values contained in the parameter (I fear subtle variations in spelling, for example "Au-delà du B1" instead of "Au-delà de B1"... I've read about "fuzzy matching"?), and also make any value that does not match the set list above default to "N/A"? Or, in a much simpler way, I'd like to extract all the unique values contained in said columns and assign them to the correct value in my list parameter (all in one fell swoop, of course :lol:)

 

I have to indicate that I am completely new to the world of Power Query (and that I wish I'd known about it in prior projects: it would have saved me a WORLD of hassle...)

 

Thanks, in advance, to anyone who would be willing to help!

 

--Ari.

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@AriBouaniche You tagged your post with 'Excel on Mac'. Than you can forget about fuzzy matching, as it is not supported in PQ on the Mac (yet). I created an test file with fuzzy matching on my PC and ran it on a Mac but it errors out as there are no fuzzy matching options in the Merge window.

 

On a PC you would do Fuzzy Matching based on a Transformation Table. You can read about on-line. It's not really 'fuzzy' as you specify exactly what value should be matched with another. 

 

But you can mimic this "fuzzy" matching on a Mac, as demonstrated in the attached file. Create a table with two columns. One with all the original values (From) and another with their new values (To) from after the system changes. Then merge the original column of values with From in the transformation table and next expand the To column from the merge result. That will give you a column of To-values and nulls. Now combine two columns to create on new column with the correct values.

 

 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@AriBouaniche You tagged your post with 'Excel on Mac'. Than you can forget about fuzzy matching, as it is not supported in PQ on the Mac (yet). I created an test file with fuzzy matching on my PC and ran it on a Mac but it errors out as there are no fuzzy matching options in the Merge window.

 

On a PC you would do Fuzzy Matching based on a Transformation Table. You can read about on-line. It's not really 'fuzzy' as you specify exactly what value should be matched with another. 

 

But you can mimic this "fuzzy" matching on a Mac, as demonstrated in the attached file. Create a table with two columns. One with all the original values (From) and another with their new values (To) from after the system changes. Then merge the original column of values with From in the transformation table and next expand the To column from the merge result. That will give you a column of To-values and nulls. Now combine two columns to create on new column with the correct values.

 

 

View solution in original post