Sunday, April 9, 2023

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

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