Forum Discussion
Change multiple values of multiple columns at once
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"
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")
3 Replies
- wdx223_Daniel
Community Champion
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")
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 tfcOr, 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