How to modify or rename all Column Names at once using MQuery in Power BI

How to remove Special Characters from all Column Names at once using MQuery in Power BI
Scenario:

Suppose we have the Dataset having Column Names with special characters as “[“, “.”, “]” and dataset name prefix as shown below:

Orders[Order_Id],

Orders[Order_Date],

Orders.[SalesCountry_Id],

Orders.[Supplier_Id],

Shipping_Date

Orders.Cust_Id

Orders.Prod_Id

Now we can transform these columns by replacing the “[“, “.”, “]” and dataset name prefix using the below MQuery:

RenameColumns = Table.TransformColumnNames(Dataset, (ColumnName as text) as text => Text.Replace(Text.Replace(Text.Replace(Text.Replace(ColumnName,”.”,””),”Orders” ,””),”[“,””),”]”,””))

(OR)

TransformColumns = Table.TransformColumnNames(Dataset, (ColumnName as text) as text => if (Text.BetweenDelimiters(ColumnName,”[“,”]”)=”” and Text.AfterDelimiter (ColumnName,”.”) = “” ) then ColumnName else if Text.BetweenDelimiters (ColumnName,”[“,”]”)=”” then Text.AfterDelimiter(ColumnName,”.”) else Text.BetweenDelimiters (ColumnName,”[“,”]”))

MQuery:

let

Source = Excel.Workbook(File.Contents(“E:\Tech_Lab\Power BI\DataSets\ ds_Sample_Orders.xlsx”) , null, true),

tbl_OrderDetails_Sheet = Source{[Item=”tbl_OrderDetails”,Kind=”Sheet”]}[Data],

#”Promoted Headers” = Table.PromoteHeaders(tbl_OrderDetails_Sheet, [PromoteAllScalars=true]),

#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Orders[Order_Id]”, Int64.Type}, {“Orders[Order_Date]”, type date}, {“Orders.[SalesCountry_Id]”, Int64.Type}, {“Orders.[Supplier_Id]”, Int64.Type}, {“Shipping_Date”, type date}, {“Orders.Cust_Id”, Int64.Type}, {“Orders.Prod_Id”, Int64.Type}}),

//RenameColumns = Table.TransformColumnNames(#”Changed Type”, (ColumnName as text) as text => Text.Replace(Text.Replace(Text.Replace(Text.Replace(ColumnName,”.”,””), “Orders” ,””),”[“,””),”]”,””))

TransformColumns = Table.TransformColumnNames(#”Changed Type”, (ColumnName as text) as text => if (Text.BetweenDelimiters(ColumnName,”[“,”]”)=”” and Text.AfterDelimiter(ColumnName,”.”)=””) then ColumnName else if Text.BetweenDelimiters(ColumnName,”[“,”]”)=”” then Text.AfterDelimiter(ColumnName,”.”) else Text.BetweenDelimiters(ColumnName,”[“,”]”))

TransformColumns

Result: