Creating "folders" for power query steps

[HxH]

Hi everyone, 

I'm currently doing a lot of data cleaning in Power Query. I have a column where the operators of my company manually input data and therefore I have to clean it. Is there a way to put the steps of PQ in some sort of folder/group that could help me to better identify substeps I did while cleaning data? 

For example I do a lot of replace values to correct the data, and it would be much easier to navigate my PQ steps if I had something like "value replacings for product A", "value replacings for product B", with all the value replacements I did for the specific product inside, because I have like 70 products and I have to do 6-7 replacement for each one, and the PQ steps list becomes a nightmare. 

EDIT: if there's a way to do the "replace value" operation with multiple value entries, that would also solve my problem

[ImkeF]

Hi @HxH ,

it certainly sounds as if you're doing this wrong. 

If you could give some more details about your task, I could suggest an easier way. 

Just to give you an idea of how to use a translation table: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

That could be applied with conditions or on multiple columns as well.

[HxH]

Hi @ImkeF 

yes I'm probably doing this in a super inefficient way. I have a column with various models of some product. The point is that for reason I'm not going to dive into the operators manually type in this model, so I get multiple different entries (one with upper case, one with lower case, one has only half the word, various typos ecc) for each distinct model. I want to replace all this with a single value in order to have a clean "Model" column I can use to perform calculations. I suppose there is some PQ code that could help me do this very easily but I'm no expert so I was manually using the "replace values" button for each wrong entry 

Example: 

MODEL COLUMN                                                                       MODEL COLUMN 

Apple                                                                                          Apple

Aple                                                                                            Apple

apple                                                 should become -->          Apple

Banana                                                                                       Banana

bananas                                                                                      Banana

[ImkeF · Accepted solution]

Hi

if you're actually replacing the full strings, then its super easy with a translattion table. Just keep your translations in that table and use 1 or 2 query steps to do all replacements in one go.

You can also try the fuzzy-merge (see attached file) - maybe that works for you. That just requires a list of your desired target values - so no need even to type in all translations manually.

If you need to replace sub-strings, please come back.

[Stachu]

I'm not sure if this is what you're looking for, but if you have query which has a lot of steps then you can split it in multiple queries with referencing and group these queries in the folders, like here

ModifedA was created by pressing RMB on ReplacedValuesA and choosing Reference. Also ReplacedValuesA is not loaded to the model - it's only executed as part of ModifedA refresh

Otherwise you can start renaming the steps in the list, having your own description makes the steps much easier to identify