Scenario:
Suppose we have a table 'tbl_Products' as shown below.
let
Source = Excel.Workbook(File.Contents(DataSource), null, true),
data_Sheet = Source{[Item="tbl_Products",Kind="Sheet"]}[Data],
PromoteHeaders = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true])
in
PromoteHeaders
= Table.InsertRows(TableOrDataset,
RowIndexPosition, /* The Row Index or Position to Insert new Rows*/
{
[Col1=value1,Col2="Value2"] /* First Row or Record */
[Col1=value1,Col2="Value2"] /* Second Row or Record */
})
To Insert the Rows at the beginning or from the first Row:
We can insert the new records at the beginning, at Index position "0" using the below Syntax:
let
Source = Excel.Workbook(File.Contents(DataSource), null, true),
data_Sheet = Source{[Item="tbl_Products",Kind="Sheet"]}[Data],
PromoteHeaders = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
InsertRows =
Table.InsertRows(PromoteHeaders,
0,
{
[Prod_Id=999999,Prod_Name="Others"],
[Prod_Id=9999999,Prod_Name="Dummy"],
[Prod_Id=null,Prod_Name=null]
}
)
in
InsertRows
Result:
Please make sure to define the new Records with all the Columns as per the Table Structure.
If you miss any Column, then the entire record will give an Error. We can pass the "null" as value if required.
To Insert the Rows at the End or from the Last row of the Table:
We can Insert the new Records at the End or from the Last row, by passing the record count of table as a Index Position.
We can find the row count of table using the Power Query Function : Table.RowCount
let
Source = Excel.Workbook(File.Contents(DataSource), null, true),
data_Sheet = Source{[Item="tbl_Products",Kind="Sheet"]}[Data],
PromoteHeaders = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
vRowCount = Table.RowCount(PromoteHeaders),
InsertRows =
Table.InsertRows(PromoteHeaders,
vRowCount,
{
[Prod_Id=999999,Prod_Name="Others"],
[Prod_Id=9999999,Prod_Name="Dummy"],
[Prod_Id=null,Prod_Name=null]
}
)
in
InsertRows
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.