none
Replace values only in selected columns RRS feed

  • Question

  •  Hi,


    Every month I need to update a table and replace values in a "n" number os columns. Sometimes are 2 columns, sometimes 3 or 5...

    All the columns before the first column that I need do replace are the same.

    How do I can automate this process without changing manually the M code? 


    Monday, July 22, 2019 7:01 PM

Answers

  • Try this:

    = Table.ReplaceValue(#"Coluna em pivô",null,"|",Replacer.ReplaceValue,List.RemoveItems(Table.ColumnNames(#"Coluna em pivô"),{"Codigo Prestador Pagamento", "Nome Prestador Pagamento", "NOVA_Chave Guia Principal", "Tipo Beneficiario", "TIPO PRESTADOR", "NÍVEL 1 AMBIENTE", "PROCESSO", "Honorário Médico", "Qtd Movimentos"}))

    • Marked as answer by Felipe Vaz Tuesday, July 23, 2019 2:03 PM
    Tuesday, July 23, 2019 12:08 PM

All replies

  • and replace values in a "n" number os columns
    Eh?

    All the columns before the first column that I need do replace are the same.
    How could you have columns before the first column???



    Monday, July 22, 2019 7:21 PM
  • and replace values in a "n" number os columns
    Eh?

    All the columns before the first column that I need do replace are the same.
    How could you have columns before the first column???



    Upsss, my bad. Im sorry!

    For example...

    ColumnA and ColumnB are always the same

    Column C and after that may vary, like...

    Column C and D (replace values in 2 columns, not including A and B)

    Column C, only (replace values in 1 column, not including A and B)

    Column C and D and E... (replace values in 3 columns, not including A and B)

    Monday, July 22, 2019 7:29 PM
  • Hi Felipe,

    How would you determine which columns need changing in advance? One time only column C, another time column C and D, and yet another time C, D and E?

    Monday, July 22, 2019 7:52 PM
  • Hi Felipe,

    How would you determine which columns need changing in advance? One time only column C, another time column C and D, and yet another time C, D and E?

    Always after the column A and B must be replaced per "|"...
    Monday, July 22, 2019 7:58 PM
  • Hi Felipe,

    You may replace last argument of Table.ReplaceValue for:

    List.RemoveItems(Table.ColumnNames(YourTable),{"A", "B"})

    • Marked as answer by Felipe Vaz Tuesday, July 23, 2019 10:56 AM
    • Unmarked as answer by Felipe Vaz Tuesday, July 23, 2019 11:44 AM
    Monday, July 22, 2019 7:59 PM
  • Hi Felipe,

    You may replace last argument of Table.ReplaceValue for:

    List.RemoveItems(Table.ColumnNames(YourTable),{"A", "B"})

    Works pretty well! Thkx

    It saves me a lot of effort.

    Tuesday, July 23, 2019 10:57 AM
  • Hi Felipe,

    You may replace last argument of Table.ReplaceValue for:

    List.RemoveItems(Table.ColumnNames(YourTable),{"A", "B"})

    Hmmm, does not work?

    Why?

    = Table.ReplaceValue(#"Coluna em pivô",null,"|",Replacer.ReplaceValue,List.RemoveItems(Table.ColumnNames(Fonte),{"Codigo Prestador Pagamento", "Nome Prestador Pagamento", "NOVA_Chave Guia Principal", "Tipo Beneficiario", "TIPO PRESTADOR", "NÍVEL 1 AMBIENTE", "PROCESSO", "Honorário Médico", "Qtd Movimentos"}))

    Tuesday, July 23, 2019 11:41 AM
  • Try this:

    = Table.ReplaceValue(#"Coluna em pivô",null,"|",Replacer.ReplaceValue,List.RemoveItems(Table.ColumnNames(#"Coluna em pivô"),{"Codigo Prestador Pagamento", "Nome Prestador Pagamento", "NOVA_Chave Guia Principal", "Tipo Beneficiario", "TIPO PRESTADOR", "NÍVEL 1 AMBIENTE", "PROCESSO", "Honorário Médico", "Qtd Movimentos"}))

    • Marked as answer by Felipe Vaz Tuesday, July 23, 2019 2:03 PM
    Tuesday, July 23, 2019 12:08 PM
  • Try this:

    = Table.ReplaceValue(#"Coluna em pivô",null,"|",Replacer.ReplaceValue,List.RemoveItems(Table.ColumnNames(#"Coluna em pivô"),{"Codigo Prestador Pagamento", "Nome Prestador Pagamento", "NOVA_Chave Guia Principal", "Tipo Beneficiario", "TIPO PRESTADOR", "NÍVEL 1 AMBIENTE", "PROCESSO", "Honorário Médico", "Qtd Movimentos"}))

    Yes, I did it and works. Thkx...

    But, Im trying to use this method in a Text.Combine, but does not work like that.

    After replace the values dynamically, I need to add a new column merging that "dynamic" replacements using "|"

    I tried:

    = Table.AddColumn(#"Valor Substituído1", "Combinações", each Text.Combine((List.RemoveItems(Table.ColumnNames(#"Valor Substituído1"),{"Codigo Prestador Pagamento", "Nome Prestador Pagamento", "NOVA_Chave Guia Principal", "Tipo Beneficiario", "TIPO PRESTADOR", "NÍVEL 1 AMBIENTE", "PROCESSO", "Honorário Médico", "Qtd Movimentos"})), "|"))

    The code add a new column and combine, but did not combine using the values into the cells, the code get the name of columns and did that for all the new column "Combinações", like this:

    Combinações

    1|2|3|4|5|6|7


    • Edited by Felipe Vaz Tuesday, July 23, 2019 1:20 PM ortography
    Tuesday, July 23, 2019 1:17 PM
  • Dear Felipe,

    Your initial question is about replacing values in selected columns. Solution of this specific problem is changing last argument of Table.ReplaceValue function (i.e. list of columns). Of course, another problems more likely need for another approaches - this list of columns may not fit in other functions. I suppose, you should create another thread for your second problem and possibly provide data sample.

    Tuesday, July 23, 2019 2:01 PM
  • Dear Felipe,

    Your initial question is about replacing values in selected columns. Solution of this specific problem is changing last argument of Table.ReplaceValue function (i.e. list of columns). Of course, another problems more likely need for another approaches - this list of columns may not fit in other functions. I suppose, you should create another thread for your second problem and possibly provide data sample.

    Ok, np.

    Thanks in advance.

    Tuesday, July 23, 2019 2:03 PM