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.
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)
RowsToSkip = Table.SelectRows(AddIndex, each [Column1]=“Prod_Id”)[IndexNum]{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:
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
——————————————————————————————————–
Be the first to comment