cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gavroche
New Member

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

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

Gavroche_0-1637940827880.png

Gavroche_0-1637939555077.png

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!

 

 

1 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"

 

View solution in original post

7 REPLIES 7
v-caitlyn-mstf
Community Support
Community Support

Hi @Gavroche ,

 

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.

Gavroche
New Member

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:

IDStateAuthorCreationTitleLabels
X01OPENGregory PASTAL18/11/2020XXXYellow, Etienne DUPONT, ANSIBLE
X02ONGOINGGregory PASTAL16/11/2020XXXblue, IT, Italy
X03CANCELLEDGregory PASTAL16/11/2020XXXJacques 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) : 

IDStateAuthorCreationTitleLabels
X01OPENEtienne DUPONT18/11/2020XXXYellow, Etienne DUPONT, ANSIBLE
X02ONGOINGGregory PASTAL16/11/2020XXXblue, IT, Italy
X03CANCELLEDJacques COMPOST16/11/2020XXXJacques COMPOST, blue, yellow

 

Gavroche_0-1638286712300.png

 

Thank you for your help.

 

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"

 

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) :

 

Gavroche_2-1638351157957.png------>Gavroche_1-1638351125867.png

 

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? 

 

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

Hey,

I adapted it and it works perfectly!

 

Thanks a lot! 🙂

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.