Replace value in a column from a second one, based on a list

[Anonymous]

Hello everyone,

I am actually trying to change (automatically) names in column of my table [Author] from a column [Labels], based on a List of names (dynamic one, that I fill on my Excel sheet): if the name in [List] appears in [Labels], then change [Author] with that name, else keep [Author].

I am far from being an expert, so I tried:

Personnalisé4 = Table.ReplaceValue(#"Personnalisé3",
             each [Author],
             each if Text.Contains(List,[Labels]=true) 
                     then [Labels]
                     else [Author],
             Replacer.ReplaceText, {"Author"}

If someone has the solution, I would be thankful!

[v-xiaoyan-msft]

Hi @Anonymous ，

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Ibendlin,

Thank you for your reply.

The goal is to have a dynamic list on Excel (easier to add or delete people from the list) that would help to transfer the name from [Labels] (actually a tag) to [Author]

Here is the example:

The dataset:

ID	State	Author	Creation	Title	Labels
X01	OPEN	Gregory PASTAL	18/11/2020	XXX	Yellow, Etienne DUPONT, ANSIBLE
X02	ONGOING	Gregory PASTAL	16/11/2020	XXX	blue, IT, Italy
X03	CANCELLED	Gregory PASTAL	16/11/2020	XXX	Jacques COMPOST, blue, yellow

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau2"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"State", type text}, {"Author", type text}, {"Creation", type datetime}, {"Title", type text}, {"Labels", type text}})
in
    #"Type modifié"

The list:

List

Etienne DUPONT

Jacques COMPOST

Valérie DULORS

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    Liste = #"Type modifié"[List]
in
    List

The expected outcome would be like this: (based on the list, the request is looking for a name in [Labels], and move it to [Author] when it's true) : 

ID	State	Author	Creation	Title	Labels
X01	OPEN	Etienne DUPONT	18/11/2020	XXX	Yellow, Etienne DUPONT, ANSIBLE
X02	ONGOING	Gregory PASTAL	16/11/2020	XXX	blue, IT, Italy
X03	CANCELLED	Jacques COMPOST	16/11/2020	XXX	Jacques COMPOST, blue, yellow

Thank you for your help.

[lbendlin · Accepted solution]

What should happen when your label contains more than one value from the replacement list?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc5BC8IgFAfwryKehU2D6GqbiGEqbIExPKyQCGSjtRH79r1Vtzp0eX/+78GP1zTY5xQTbJ0wEHKIl36YkeNVzTUs6CajNGM5y6F472EeY0r9gyAxXmPXRVQenDU1QdxUaqsFDmRB2YIaaZWRP931l3tKUyRIgaTGNs0fZwWXgptCaC3KP6Vde75N8Y4Ku3e2AvBNz6/HcQhP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, State = _t, Author = _t, Creation = _t, Title = _t, Labels = _t]),
    ReplaceList = {"Etienne DUPONT","Jacques COMPOST","Valérie DULORS"},
    #"Replaced Value" = Table.ReplaceValue(Source,each [Author],each try List.Intersect({ReplaceList,Text.Split([Labels],", ")}){0} otherwise [Author],Replacer.ReplaceValue,{"Author"})
in
    #"Replaced Value"

[Anonymous]

Thanks for your answer.

What should happen when your label contains more than one value from the replacement list?

-> It should not happen, but if it does, taking the first one on the list would be enough.

However, to help people using the tool (not used to Excel at all), I would like to let the list on the Excel Sheet (and not directly in the M code) :

------>

I use a List too to filter [Author] already:

Table.SelectRows(#"Colonnes permutées2", each (List.Contains(Tableau3,[Author])=false)),

So I was looking for something similar. Is it possible? 

[lbendlin]

Of course. You can modify my code to use different sources both for the table and the list.

[Anonymous]

Hey,

I adapted it and it works perfectly!

Thanks a lot! 🙂

[lbendlin]

Looks like you're pretty close with your attempt.  You should use Replacer.ReplaceValue though.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.