replace multiple values list in the same table

[dobregon]

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

[Nathaniel_C]

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.
Nathaniel

[dobregon]

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 ID

the newvalues are in the column Order

[Jimmy801]

Hello @dobregon 

you can use this formula in your new custom colum

if Text.Contains([Path], [Id]) = true then Text.Replace([Path], [Id], [Order]) else [Path]

Have fun

Jimmy

It this helps, consider marking this post as solution or like it

[Nathaniel_C]

Hi @Jimmy801 ,
Where does the three come from in the last row?
Nathaniel

[Jimmy801]

Good question @Nathaniel_C 

i didn't even look at it.. simple implemented the request specified by @dobregon .

Maybe table is wrong or we will get a new logic.. lets wait 😉

Have a nice time

Jimmy

[dobregon]

@Jimmy801 & @Nathaniel_C  thanks a lot for your replies and see the error in the last row ajaja now it is corrected the parent id is 5.

Related to your query @Jimmy801  this function only substitute the values in that can find in the row, what i want is to do something like this in the same table (not creating 2-3 tables in the powerquery)
https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ 

The path is the hierarchy based on the Id and ParentId, and what i want is to create another column based on the Path column and check every number in the path for the list of IDs that i have and susbitute taking the Order.

I have done something sumilar using this link example but only it is possible if i create 2 tables, the principal table and the other with only IDs and Order column. I'm looking if it is possible to create "virtual" buffer table with that info (IDs, Order) and then apply the recursive function

[Jimmy801]

Hello @dobregon 

this actually hasn't to do anything with your orignal request, to replace values. Here we are taking about another logic to show a hierarchy. order Path it's difficult to read, because it uses another logic by combing the first two column. You want to have it in a more grafical way. Something like a WBS structure of a project like this

1

1.1

1.1.2

1.2

2..

Got it?

How important is this to you? Because this may need some deeeeeep thinking.

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[dobregon]

I have solved some minutes ago, doing this, it is not perfect because i dont want to create a function, table function and then take the data but.. for now it is solved. That i did

1. Create a Function

let
ReplaceAll = (InputTable as table, ColumnName as text, ReplacementTable as table, optional StartRow as number) =>
let
ActualRow = if (StartRow =null) then 0 else StartRow,
result = Table.ReplaceValue(InputTable, ReplacementTable{ActualRow}[ID], ReplacementTable{ActualRow}[Order] ,Replacer.ReplaceText, {ColumnName}),

NextRow = ActualRow + 1,

OutputTable = if NextRow > (Table.RowCount(ReplacementTable)-1)
then result
else
@ReplaceAll(result, ColumnName, ReplacementTable, NextRow)
in
OutputTable
in
ReplaceAll

2. Apply the function --> Create a new table with Path replaced values

Using this function I have created another table with the Path translated to what i want 

3. Take the column to my original table
I have created a column that takes the column values from my 2nd table where id = id

[Jimmy801]

Hello @dobregon 

It Seems that you feed to the function a lookup table, that you prepared on Excel I suppose to change it one by one. However this could be accomplished also by a merge. Important is that you have a found solution.

However I like the recursive function to work through a table. Maybe it comes in handy one time

Bye

Jimmy

[Nathaniel_C]

Hi @dobregon ,
How do you expect to arrive at the last two values?