Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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-xiaoyan-msft
Community Support
Community Support

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
Not applicable

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"

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.