Testing a query function to replace duplicated values with null, but I am getting errors

[davidleal]

I am trying to test a simple function that replace duplicated values with null in Name column:

let
  Source = Excel.CurrentWorkbook(){[Name="TB_source"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amount", Int64.Type}}),
  RemoveDups = (aTable as table, colNames as text) =>
    let
      dupsNull = List.Generate(
      ()=>[v=aTable[colNames]{0}, idx=0],
      each [idx]<Table.RowCount(aTable),
       each [v=if List.PositionOf(aTable[colNames],aTable[colNames]{[idx]+1},Occurrence.First) = [idx]+1
         then aTable[colNames]{[idx]+1} else null, idx=[idx]+1],each [v])
     in
      dupsNull,
#"listUnique" = RemoveDups(#"Changed Type", "Name")
in
  #"listUnique"

A get a List but with errors:

when I click on the error I am getting the following information:

If I click on Go to Error I am getting the following:

I was trying to define the ColNames input argument as a list but I was not able to make it work, I am getting erros of with different information.

I was able to make work a similar peace of code, but without invoking a function as follow:

let
  Source = Excel.CurrentWorkbook(){[Name="TB_source"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amount", Int64.Type}}),
  dupsNull = List.Generate(
    ()=>[v=#"Changed Type"[Name]{0}, idx=0],
      each [idx]<Table.RowCount(#"Changed Type"),
       each [v=if List.PositionOf(#"Changed Type"[Name],#"Changed Type"[Name]{[idx]+1},Occurrence.First) = [idx]+1
         then #"Changed Type"[Name]{[idx]+1} else null, idx=[idx]+1],
           each [v])
in
dupsNull

and I am getting the expected result for the list:

Any help is appreciated, please take into account I am new to Power Query and M.

Thanks,

David

[davidleal]

Thanks @wdx223_Daniel it tested both options and both of them work. Thanks! Here the source code updated for further reference. In block comment the first option (larger):

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}),
/*
RemoveDups = (aTable as table, colNames as text) =>
  let
    dupsNull = List.Generate(
      ()=>[v=Table.Column(aTable,colNames){0}, idx=0],
        each [idx]<Table.RowCount(aTable),
          each [v=if List.PositionOf(Table.Column(aTable,colNames),
          Table.Column(aTable,colNames){[idx]+1},Occurrence.First) = [idx]+1
            then Table.Column(aTable,colNames){[idx]+1} else null, idx=[idx]+1],each [v])
in
  dupsNull, 
  #"runiqueList" = RemoveDups(#"Changed Type", "Name")
*/
  #"runiqueList" = RemoveDups(Table.Column(#"Changed Type","Name"))
in
  #"runiqueList"

 Thanks again

[wdx223_Daniel · Accepted solution]

try to change all "aTable[colNames]" to "Table.Column(aTable,colNames)"

or try this code

fx=(lst as list)=>List.Accumulate(lst,{},(x,y)=> x&{ if List.Contains(x,y) then null else y})