Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
Hi @hnguyen76
Try the solution on the attached.
Hi @hnguyen76
Try the solution on the attached.