Hi everyone!
I have a simple table that has these column names:
| TB_Balance | Difference | ERP | GM | Year_Quarter | Provided | Unprovided | L1_Type |
| 100 | 10 | SYS1 | GM1 | 2020-Q1 | 10 | 0 | Type1 |
I will have a few files from different sources that have different column names and I'd like to do a text search and replace using an array. I think the idea that I want is to list all the column names, find any of the column names that matches the left side of the array and then replace it with the right side of the array.
So in this example "TB_Balance" would be replaced by just "Balance", "Provided" to "Provision", and so forth. I think I'm pretty close but I can't seem to get the right results. THis is my current script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNIBkkAiODLYEEi5+4JIIwMjA91AQ5gcCIdUFqQaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TB_Balance = _t, Difference = _t, ERP = _t, GM = _t, Year_Quarter = _t, Provided = _t, Unprovided = _t, L1_Type = _t]),
// BEGIN CUSTOM SUBSTITUTION ARRAY
ReplaceArr =
[
TB_Balance = "Balance",
Provided = "Provision",
Unprovided = "Unprovision",
L1_Type = "Type",
L2_Type = "Type",
L3_Type = "Type",
Diff = "Difference"
],
FindAndReplace = Table.TransformColumns(Table.ColumnNames(Source), Record.FieldOrDefault(ReplaceArr, _, _)),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TB_Balance", Int64.Type}, {"Difference", Int64.Type}, {"ERP", type text}, {"GM", type text}, {"Year_Quarter", type text}, {"Provided", Int64.Type}, {"Unprovided", Int64.Type}, {"L1_Type", type text}})
in
#"Changed Type"
Any help would be greatly appreciated! Thanks!
