Power Query to skip or remove top unwanted rows dynamically from a dataset before load to Power BI

How to skip or remove top unwanted rows dynamically from a dataset before load to Power BI using Power Query

Scenario:

Suppose we have a excel dataset with some unwanted rows as shown below. In this dataset the actual data rows are starting from Row 6, which needs to be considered as headers for dataset and remove the top 5 rows while loading to Power BI.

When we load this excel file to Power BI, it will be look like as follows:

The actual data rows are starting from Row 6, which needs to be considered as headers for dataset and remove the top 5 rows dynamically as explained below :

1) First, add and Index(starts from 0) step next to the Navigation step:

AddIndex = Table.AddIndexColumn(tbl_Cost_Sheet, “IndexNum”, 0, 1, Int64.Type)

2) Next, identify the Index number of Column headers row. We can take any one of the Column (Prod_Id) as a reference here:

RowsToSkip = Table.SelectRows(AddIndex, each [Column1]=“Prod_Id”)[IndexNum]{0}

Please note that our data Headers start from Row 6, but Its Index Value is 5, as it starts from 0.

3) Next, skip the Rows based on the Index Position of Headers:

FilterDataset= Table.Skip(AddIndex,RowsToSkip)

4) Finally, remove IndexNum column as we no longer needed, and then promote headers and change datatype:

Here is the complete Query used in this example.

let
Source = Excel.Workbook(File.Contents(“E:\Tech_Lab\Power BI\DataSets\ds_Sample_Data.xlsx”), null, true),
tbl_Cost_Sheet = Source{[Item=”tbl_Cost”,Kind=”Sheet”]}[Data],
AddIndex = Table.AddIndexColumn(tbl_Cost_Sheet, “IndexNum”, 0, 1, Int64.Type),
RowsToSkip = Table.SelectRows(AddIndex, each [Column1]=”Prod_Id”)[IndexNum]{0},
FilterDataset = Table.Skip(AddIndex,RowsToSkip),
RemoveIndex = Table.RemoveColumns(FilterDataset,{“IndexNum”}),
#”Promoted Headers” = Table.PromoteHeaders(RemoveIndex, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Prod_Id”, Int64.Type}, {“Prod_Name”, type text}, {“Unit_Price”, Int64.Type}, {“Unit_Cost”, Int64.Type}}) /*[Row_Number]*/
in
#”Changed Type”

Final Result: