Sunday, March 20, 2022

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,"[","]"))

in 
    TransformColumns

Result:


-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog