Forum Discussion

AriBouaniche's avatar
AriBouaniche
Copper Contributor
Aug 31, 2023
Solved

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

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 compar...
excel
Excel on Mac
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 31, 2023

    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.

     

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 31, 2023

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.

 

 

Resources