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)
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}
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:
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.