Change multiple values of multiple columns at once

[Anonymous]

Hello all,

I am starting to learn m code a little bit more. The problem I have got now is probably very easy to solve but unfortunately at my level of knowledge I am stuck with what I have got already. 

Ok, so what I am trying to achieve:

I have a table that consist of columns with foreign keys row ids (but not only). I want to change the column values by adding a constant text like "ABCD-". There are 24 columns to modify so I don't want to add them manually using Table.TransformColumns because within the same dataset I will have to repeat the operation multiple times (for different query/procedure outputs). I tried to use List.Accumulate to get all the column names and then Trable.TransformColumns but I am doing something wrong as I can't see any changes on the column values when I preview "#Test" output.

What I have so far:

let
    Source = Sql.Database
    (Instance, Database, [Query="EXEC PROCNAME] @DaysToRetrieve= "& Number.ToText(DaysToRetrieve)]),


   // #"Changed Type" = Table.TransformColumnTypes(Source,{{"EquipmentKey", type text}}),

   #"ColumnNames" =List.RemoveLastN(
                                     Table.ColumnNames(Source),
                                                           24
                                    ),


    #"Changed Type" = Table.TransformColumnTypes(Source,
                                                    List.Transform(#"ColumnNames",
                                                       
                                                         
                                                       each {_, type text} 
                                                                )
                                                    ),
    #"SourceMod" = Table.AddColumn(#"Changed Type", "SiteID", each "ABCD"), 
    
    
    #"SourceMod1" = Table.TransformColumns(
      #"SourceMod",
    
     {
          {"EquipmentKey", each Text.Combine({"ABCD-",(_)}), type text}
        
       }
 
      ),

     #"Test" = List.Accumulate(
                    #"ColumnNames",
                    #"SourceMod", 
                    (s,c) =>  Table.TransformColumns
                                                  (
                                                    #"SourceMod",
                                                  {
                                                    {c, each Text.Combine({"ABCD-",(_)}), type text}
                                                    }
                                                  )

     ),

 
     Source2 = Sql.Database
    (Instance2, Database, [Query="EXEC [DW].PROCNAME @DaysToRetrieve= "& Number.ToText(DaysToRetrieve)]),

    #"SourceMod2" = Table.AddColumn(Source2, "SiteID", each "EFGH"),

    #"Appended Query" = Table.Combine( {#"SourceMod2", #"SourceMod1"})
in
    #"Appended Query"

[Anonymous]

I don't think that in this case the accumulated function is the most suitable, in terms of efficiency.

Try the following diagram. The only difficulty is selecting your 24 columns.
I used the list.range function to select 3 columns starting from the index {2} that is the third one, but this is just to give an example.
In your case, where are the 24 columns or how can they be identified by the name?

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcm5DQAwCATBXi4mAWxwL4j+2zBPMlppI8Ag8DJKcQ1JgU5ZRi3MZ3XqMp7CHzI/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t, Colonna3 = _t, Colonna4 = _t, Colonna5 = _t]),
    cols=Table.ColumnNames(Origine),
    colnames=List.Range(cols,2,3),
    ttc=Table.ToColumns(Origine),
    mycols=List.Range(ttc,2,3),
    lt=List.Transform(mycols, each List.Transform(_, (e)=>Text.Combine({"ABC-",e})) ),
    tfc=Table.FromColumns(lt,colnames)

in
    tfc

Or, perhaps even better, the solution proposed by @wdx223_Daniel

    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcm5DQAwCATBXi4mAWxwL4j+2zBPMlppI8Ag8DJKcQ1JgU5ZRi3MZ3XqMp7CHzI/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t, Colonna3 = _t, Colonna4 = _t, Colonna5 = _t]),
    cols=Table.ColumnNames(Origine),
    colnames=List.Range(cols,2,3),
    trv=Table.ReplaceValue(Origine,null,null,(x,y,z)=>"ABC-"&x, colnames)

in
    trv

[wdx223_Daniel · Accepted solution]

are you sure the code of #"ColumnNames" is correct? it's remove the last 24 column names.

#"Test" =Table.ReplaceValue( #"SourceMod","","",(x,y,z)=>Text.Combine({"ABCD-",x}),#"ColumnNames")

[Anonymous]

Hi, The code of #"ColumnNames" is correct - the table consists of 41 columns and only the first 17 are the ones I want to modify. 

Thank you for your help