How to Insert new Records to a Table using Table.InsertRows function of Power Query in Power BI

How to Add new Records to a Table using Table.InsertRows function of Power Query in Power BI
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

Now we can add the additional records to this either in the beginning or end of the table using the Table.InsertRows function of Power Query, as shown below:

= 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

--------------------------------------------------------------------------------------------------------