Cleaning Cases in Power Query

[Darko_Giac]

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

[v-yulgu-msft]

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

[edhans]

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.