Forum Discussion
Replace duplicated values with null in the same column per a given list of columns
I found several solutions for a similar problem, but it creates an additional column or does find and replace value which is not what I need. Let's say I have a Name column with the following values:
name1
name1
name2
name2
name1
and I would like to have the following output (the same previous column but with some values transformed):
name1
null
name2
null
null
In this question: Testing a query function to replace duplicated values with null, but I am getting errors wdx223_Daniel provided the following solution that returns a list with the expected output:
let
Source = Excel.CurrentWorkbook(){[Name="TB_source"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amount", Int64.Type}}),
RemoveDups = (lst as list)=>List.Accumulate(lst,{},(x,y)=> x&{ if List.Contains(x,y) then null else y}),
replaceValues = RemoveDups(Table.Column(#"Changed Type", "Name"))
in
replaceValues
but I was not able to assign this list to the existing column Name. The question: Find and Replace text string in one table using value from another table does a find and replace, I was trying to customize it, but it doesn't work because for every value in the Find will be replaced wil null. Because it does a search (not by index position).
What I would need is to loop for all values of the a given column and replace with the corresponding value at the same index position of #"uniqueList". In other languages it would be a simple assignment: a=b, but I was not able to find a way in Power Query.
I was trying the following for example:
Table.ReplaceValue(#"Changed Type", each [Name], each replaceValues,Replacer.ReplaceValue,{"Name"})but it return a the list of value on each row.
For the general case I would like to do the following transformation in #"Changed Type": For a list of selected columns replace per column duplicated values with null.
Thanks
Hi davidleal ,
utilizing on what you have already, I would suggest the following approach:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W8kvMTTVU0lFyziypBNGGBkqxOpjCRghhI6gwiDbGLmyCKWwMpE2BwrEA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, City = _t, Amount = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Name", type text}, {"City", type text}, {"Amount", Int64.Type}} ), ListOfColumns = {"Name", "City"}, RemainingColumns = List.Difference(Table.ColumnNames(#"Changed Type"), ListOfColumns), RemoveDups = (lst as list) => List.Accumulate(lst, {}, (x, y) => x & {if List.Contains(x, y) then null else y}), replaceValues = List.Transform(ListOfColumns, each RemoveDups(Table.Column(#"Changed Type", _))), Custom1 = Table.FromColumns( replaceValues & Table.ToColumns(Table.SelectColumns(#"Changed Type", RemainingColumns)), ListOfColumns & RemainingColumns ) in Custom1
3 Replies
- ImkeF
Community Champion
Hi davidleal ,
utilizing on what you have already, I would suggest the following approach:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W8kvMTTVU0lFyziypBNGGBkqxOpjCRghhI6gwiDbGLmyCKWwMpE2BwrEA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, City = _t, Amount = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Name", type text}, {"City", type text}, {"Amount", Int64.Type}} ), ListOfColumns = {"Name", "City"}, RemainingColumns = List.Difference(Table.ColumnNames(#"Changed Type"), ListOfColumns), RemoveDups = (lst as list) => List.Accumulate(lst, {}, (x, y) => x & {if List.Contains(x, y) then null else y}), replaceValues = List.Transform(ListOfColumns, each RemoveDups(Table.Column(#"Changed Type", _))), Custom1 = Table.FromColumns( replaceValues & Table.ToColumns(Table.SelectColumns(#"Changed Type", RemainingColumns)), ListOfColumns & RemainingColumns ) in Custom1 - davidlealFrequent Visitor
Thanks, it works, so it looks like that the only we to do it is to recreate a new table again, but not replacing the existing column values with the new value list.