Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hnguyen76
Resolver II
Resolver II

Power Query Bulk Rename Based on array

Hi everyone!

I have a simple table that has these column names:

 

TB_BalanceDifferenceERPGMYear_QuarterProvidedUnprovidedL1_Type
10010SYS1GM12020-Q1100Type1

 

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!

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @hnguyen76 

 

Try the solution on the attached.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

View solution in original post

2 REPLIES 2
Mariusz
Community Champion
Community Champion

Hi @hnguyen76 

 

Try the solution on the attached.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

@Mariusz ,

Beautifully done! Thanks for the quick reply! Much appreciated!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.