Sunday, April 24, 2022

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:

--------------------------------------------------------------------------------------------------------
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