Scenario:
Suppose we have the Dataset having Column Names with special characters as "[", ".", "]" and dataset name prefix as shown below:
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:
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.