![light-bulb-3535435_960_720.jpg](https://quantinsightsnetwork.com/wp-content/uploads/2023/10/light-bulb-3535435_960_720-678x381.jpg)
Scenario:
Suppose, we have an excel dataset with some required and the unwanted columns like Colum2, Column 4, Prod_Id_2, Country_Id2, Cust_Id – Copy, Cust_Id – Copy.1, Country_Id3..etc
1) First Load the Dataset(not Data) and keep only Source Step:
Source = Excel.Workbook(File.Contents(xl_DataSource), null, true)
Note: I have used the Parameters for DataSource and Dataset :
xl_DataSource = E:\Tech_Lab\Power BI\DataSets\ds_Sample_Orders.xlsx
xl_Dataset = tbl_OrderDetails
2) Next get the list of Column Names from the Dataset:
= Table.ColumnNames(Table.PromoteHeaders(Source{[Item=xl_Dataset,Kind=”Sheet”]}[Data], [PromoteAllScalars=true]))
3) Next convert the list of Column Names to a Table and then create a Flag column to identify the Columns to Remove from the Dataset:
ConvertToTable = Table.RenameColumns(Table.FromList(SelectColNames, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{“Column1”, “ColumnNames”}})
FlagCols2Remove = Table.AddColumn(ConvertToTable, “RemoveCols_Flag”, each if ([ColumnNames] is null or [ColumnNames] = “null” or Text.Length(Text.Select([ColumnNames],{“0″..”9″}))>0) then 1 else if Text.Contains([ColumnNames],”- Copy”) then 1 else 0)
The “RemoveCols_Flag” retruns 1 for the Columns to Remove and 0 to Keep.
4) Next generate a final list of ColumnNames to Remove where RemoveCols_Flag=1
ColsList2Remove= (Table.SelectColumns(Table.SelectRows(FlagCols2Remove, each ([RemoveCols_Flag] = 1)),”ColumnNames”))[ColumnNames]
SelectReqCols= Table.RemoveColumns(Table.PromoteHeaders(Source{[Item=xl_Dataset,Kind=”Sheet”]}[Data], [PromoteAllScalars=true]),ColsList2Remove)
Result :
let
Source = Excel.Workbook(File.Contents(xl_DataSource), null, true),
SelectColNames = Table.ColumnNames(Table.PromoteHeaders(Source{[Item=xl_Dataset,Kind=”Sheet”]}[Data], [PromoteAllScalars=true])),
ConvertToTable = Table.RenameColumns(Table.FromList(SelectColNames, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{“Column1”, “ColumnNames”}}),
FlagCols2Remove = Table.AddColumn(ConvertToTable, “RemoveCols_Flag”, each if ([ColumnNames] is null or [ColumnNames] = “null” or Text.Length(Text.Select([ColumnNames],{“0”..”9″}))>0) then 1 else if Text.Contains([ColumnNames],”- Copy”) then 1 else 0),
ColsList2Remove = (Table.SelectColumns(Table.SelectRows(FlagCols2Remove, each ([RemoveCols_Flag] = 1)),”ColumnNames”))[ColumnNames],
SelectReqCols = Table.RemoveColumns(Table.PromoteHeaders(Source{[Item=xl_Dataset,Kind=”Sheet”]}[Data], [PromoteAllScalars=true]),ColsList2Remove),
#”Changed Type” = Table.TransformColumnTypes(SelectReqCols,{{“Order_Id”, Int64.Type}, {“Order_Date”, type datetime}, {“Prod_Id”, Int64.Type}, {“Country_Id”, Int64.Type}, {“Cust_Id”, Int64.Type}, {“Gross_Sales”, Int64.Type}})
in
#”Changed Type”
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment