Forum Discussion

GianJo's avatar
GianJo
New Member
3 years ago
Solved

Replace Value with previous Row based on a condition

Hello everybody,

 

I am trying to replace a value based on a condition. If the condition is true I want to replace the value with the one in the previous Row. I am using Table.replaceValue but I ma not able to make it work.

 

 

 

 

 

 

let
    Origine = Excel.CurrentWorkbook(){[Name="tabellaDati"]}[Content],
    #"Ordinate righe" = Table.Sort(Origine,{{"Codice2", Order.Ascending},{"Codice", Order.Descending}}),
    #"Duplicata colonna" = Table.DuplicateColumn(#"Ordinate righe", "Classe", "ClasseProva"),
    #"Riordinate colonne" = Table.ReorderColumns(#"Duplicata colonna",{"Codice", "DTA_RIFERIMENTO", "Codice2", "Città", "Classe", "ClasseProva"}),
    AggiuntaColonnaIndice = Table.AddIndexColumn(#"Riordinate colonne", "Indice", -1, 1, Int64.Type),
   #"Sostituito valore" = Table.ReplaceValue(
        AggiuntaColonnaIndice,
        each [ClasseProva],
        each if [ClasseProva] = "ciao"
        then
        AggiuntaColonnaIndice{[Indice]}[ClasseProva]
        else
        [ClasseProva],
        Replacer.ReplaceText,{"ClasseProva"})
in
    #"Sostituito valore"

 

 

In the link the file with all the details.

 

https://filetransfer.io/data-package/Ob6Z5cn6#link

 

Thank you in advance

 

 

 

 

Need Help
  • latimeria's avatar
    latimeria
    3 years ago

    Hi GianJo ,

     

    Almost there, "Replacer.ReplaceText" replace text selected whereas "Replacer.ReplaceValue" replace the value

    Table.ReplaceValue(
        AggiuntaColonnaIndice,
        each [ClasseProva],
        each if [ClasseProva] = "ciao"
        then
        AggiuntaColonnaIndice{[Indice]}[ClasseProva]
        else
        [ClasseProva],
        Replacer.ReplaceValue,{"ClasseProva"})

     Now, if you create your Index from 0, you can do:

    = Table.ReplaceValue(
        AggiuntaColonnaIndice,
        each [ClasseProva],
        each if [ClasseProva] = "ciao"
        then
        AggiuntaColonnaIndice{[Indice]-1}[ClasseProva]
        else
        [ClasseProva],
        Replacer.ReplaceValue,{"ClasseProva"})

     and its clearer in my mind you are getting previous row.

2 Replies

  • latimeria's avatar
    latimeria
    Solution Specialist
    3 years ago

    Hi GianJo ,

     

    Almost there, "Replacer.ReplaceText" replace text selected whereas "Replacer.ReplaceValue" replace the value

    Table.ReplaceValue(
        AggiuntaColonnaIndice,
        each [ClasseProva],
        each if [ClasseProva] = "ciao"
        then
        AggiuntaColonnaIndice{[Indice]}[ClasseProva]
        else
        [ClasseProva],
        Replacer.ReplaceValue,{"ClasseProva"})

     Now, if you create your Index from 0, you can do:

    = Table.ReplaceValue(
        AggiuntaColonnaIndice,
        each [ClasseProva],
        each if [ClasseProva] = "ciao"
        then
        AggiuntaColonnaIndice{[Indice]-1}[ClasseProva]
        else
        [ClasseProva],
        Replacer.ReplaceValue,{"ClasseProva"})

     and its clearer in my mind you are getting previous row.