Forum Discussion
Advocate IICreating "folders" for power query steps
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
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.
4 Replies
- ImkeF
Community Champion
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.
HxHHi 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 COLUMNApple Apple
Aple Apple
apple should become --> Apple
Banana Banana
bananas Banana
- ImkeF
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
