Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I'm refining my knowledge of the PowerM language and I find that I have redondant code that I'm sure could be simplified... only if I knew how.
Tried many approach but I still get error.
Here is my challenge.
I have to transform a file by replacing the English word by its French word in 4 different columns.
Tried different technique. The two first are working but some code are redondant.
What if I had to do it not on 4 but 15 columns????
What if I had to replace not 4 but 20 words???
The code would become cumbersome / heavy.
A table where I could easily Add/Remove word is the ideal solution I think.
Tried to modify some of the code in (3) to ressemble like (1) but could not make it work.
The question is: How to modify (2) to make it work for multiple columns at once????
Any suggestion?
Thank you for making me write better PowerM code
Daniel
// (1) Replace using Replace.Value on multiple Columns at once (working)
let
Source = Csv.Document(File.Contents("C:\NC20220410.csv"),[Delimiter=";", Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replace Yes-Oui" = Table.ReplaceValue(#"En-têtes promus","Yes","Oui", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"}),
#"Replace No-Non" = Table.ReplaceValue(#"Replace Yes-Oui","No","Non", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"}),
#"Replace Maybe-Peut-être" = Table.ReplaceValue(#"Replace No-Non","Maybe","Peut-être", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"}),
#"Replace Of Course-Certainement" = Table.ReplaceValue(#"Replace Maybe-Peut-être","Of Course","Certainement", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"})
in
#"Replace Of Course-Certainement"
// (2) Replace via table on single Column at a time (working)
let
Source = Csv.Document(File.Contents("C:\NC20220410.csv"),[Delimiter=";", Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"ReplaceViaTableColumnA" = Table.TransformColumns(#"En-têtes promus",{ "ColumnA", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),
#"ReplaceViaTableColumnB" = Table.TransformColumns(#"ReplaceViaTableColumnA",{ "ColumnB", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),
#"ReplaceViaTableColumnC" = Table.TransformColumns(#"ReplaceViaTableColumnB",{ "ColumnC", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),
#"ReplaceViaTableColumnD" = Table.TransformColumns(#"ReplaceViaTableColumnC",{ "ColumnD", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),
in
#"ReplaceViaTableColumnD"
// (3): Replace via table on multiple Columns at once (My wish)
let
Source = Csv.Document(File.Contents("C:\NC20220410.csv"),[Delimiter=";", Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"ReplaceAllColumns" = Table.TransformColumns(#"En-têtes promus",{ {"ColumnA","ColumnB","ColumnC","ColumnD"}, each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore)
in
#"ReplaceAllColumns"
tblBeforeAfterLight
Before | After |
Yes | Oui |
No | Non |
Maybe | Peut-être |
Of Course | Certainement |
Solved! Go to Solution.
Your table approach for replacement word is the way to go.
In the below code, I am replacing it in all the columns of the table through
ColumnList = Table.ColumnNames(Source)
But if you need to do in selected columns, then just specify the column names in this ColumList. (You can once again make a table which you can convert to list)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRikwtBpJA5JtYmZQKpFPSUoAkmPDLB0nF6kQrpQEZRWBZNOyXn5cKNQWkDqwGrA0kWQTE/mkKzvmlRcWpYBPA8kCyCGIeSAvEATDbwXqR9UC0QRhKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
ColumnList = Table.ColumnNames(Source),
ChangeValues = Table.FromRecords(Table.TransformRows(Source, (r) => List.Accumulate(Table.ColumnNames(Source), r, (s,c)=> Record.TransformFields(s,{{c, each try tblBeforeAfterLight {[Before=_]}[After] otherwise _}})))
, Value.Type(Source))
in
ChangeValues
1.- First transpose your table of names and promote headers so you only have one row.
let
Source = Excel.CurrentWorkbook(){[Name="Table_Names"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
2.- then add this M code in the formula bar
Table.TransformColumnNames( Source, each try Table.Column(Table_Names,_){0} otherwise _)
let
Source = Excel.CurrentWorkbook(){[Name="Table_Data"]}[Content],
Custom1 = Table.TransformColumnNames( Source, each try Table.Column(Table_Names,_){0} otherwise _)
in
Custom1
Your table approach for replacement word is the way to go.
In the below code, I am replacing it in all the columns of the table through
ColumnList = Table.ColumnNames(Source)
But if you need to do in selected columns, then just specify the column names in this ColumList. (You can once again make a table which you can convert to list)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRikwtBpJA5JtYmZQKpFPSUoAkmPDLB0nF6kQrpQEZRWBZNOyXn5cKNQWkDqwGrA0kWQTE/mkKzvmlRcWpYBPA8kCyCGIeSAvEATDbwXqR9UC0QRhKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
ColumnList = Table.ColumnNames(Source),
ChangeValues = Table.FromRecords(Table.TransformRows(Source, (r) => List.Accumulate(Table.ColumnNames(Source), r, (s,c)=> Record.TransformFields(s,{{c, each try tblBeforeAfterLight {[Before=_]}[After] otherwise _}})))
, Value.Type(Source))
in
ChangeValues