Forum Discussion

Darko_Giac's avatar
Darko_Giac
Helper II
7 years ago

Cleaning Cases in Power Query

Hi all,

 

I'm currently working with a table that I have set up to refresh by connecting power query to a folder that the raw data are in.

 

I'm looking for a way to clean the data within power query for certain cells.

 

For example, I would want to correct the gender for a certain case in power query if it was input incorrectly in the raw data, which is easy enough to do once. However, once I get a new data set, there might be another case for which gender needs to be corrected.

 

Would there be a way to do this while still preserving the first change?

 

I've attached a sample workbook as a reference for what I have so far.

 

Sample Cleaning Workbook

 

Darko

Need Help
Show and Tell

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    7 years ago

    You could do it a few different ways. Here are 2.

    1. See this article on writing a custom function in Power Query to emulate the SWITCH() function that DAX has. For a few changes. this would be fine. For dozens or hundreds, this would be a pain.
    2. Create a correction table, then merge it with your original table. If a corrected gender exists, use it, otherwise use the source gender. See my file attached. You should be able to see how I added the correction table then did the merge. This would be best for a lot of changes.
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Darko_Giac,

     

    Currently, data refesh will cover the old dataset. It is not available to keep the historical data. In your scenario, may be you need to load the updated data into a new query in desktop, make changes to it. Then, append the new query to the origianl one.

     

    Regards,

    Yuliana Gu