Forum Discussion
replace multiple values list in the same table
Hi guys,
I have a doubt about M querys, i'm looking to apply a custom column in a table that replace values from a column loking for the list values in the same table, te table is something like this
| Id | ParentId | Hierarchy | Order | Path |
| 1 | NULL | Expected | 1 | 1 |
| 2 | NULL | Incomes | 2 | 2 |
| 3 | 2 | Bike | 1 | 2|3 |
| 4 | 2 | Motorbike | 2 | 2|4 |
| 5 | 2 | Losses | 3 | 2|5 |
| 6 | 5 | Theft | 1 | 2|5|6 |
| 7 | 5 | Broken | 2 | 2|5|7 |
And what i want is to analyse the column Path, each number look into the id column and give the order column and the result should be something like this
| Id | ParentId | Hierarchy | Order | Path | OrderPath |
| 1 | NULL | Expected | 1 | 1 | 1 |
| 2 | NULL | Incomes | 2 | 2 | 2 |
| 3 | 2 | Bike | 1 | 2|3 | 2|1 |
| 4 | 2 | Motorbike | 2 | 2|4 | 2|2 |
| 5 | 2 | Losses | 3 | 2|5 | 2|3 |
| 6 | 5 | Theft | 1 | 2|5|6 | 2|3|1 |
| 7 | 5 | Broken | 2 | 2|5|7 | 2|3|2 |
It could be possible to do in a custom column without creating other tables? i have tried some examples like
https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/
but they are not running taking the info from the same table.
Someone can help me? thanks
10 Replies
Hi dobregon ,
Can you give us the logic? Something like if this then do this, if that, then do that.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielHi dobregon ,
How do you expect to arrive at the last two values?thanks for the refply Nathaniel_C , the logic should be something that search all the numbers in the path and if the number exists (that should be) in the id, the number should be replaced by the numer in the order column
So, the text to analyse is the column path
the oldvalues are in the column IDthe newvalues are in the column Order
