Add new row to table and fill with specific data for each column

[Anonymous]

Hello,

I have the following table loaded in Power Query and i am trying to add a new row with specific data filled in for each column.

For example, in Power Query, find the row with Document ID = 4060 (green), add a new row below it with specific data (yellow):

This should still work when the data is refreshed and maybe additional rows are present. Is this possible? Any help is much appreciated!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZO9bsMwDITfxXPA8F/k2KFAx24dgrz/a1RSnVSmi8ZAAAX4fL47UrfbJpmxXTbUK8aVkbn/YZUEav0k/ffx/rndL7dN0bCSRM3BvJ/8D5IWsiUhK6j1cx5YHyzZFduvahACD5R4ZUnGq4wry2IMOIyOHF9vD3R+6SDbRdUghlM+okMVV1TcIK0WQNYqqIYJESfyHMoYiUYRZw594TiZQWuj5FoF05J/uj+mca+ksDYCb7V6ilZRbyng0+8LUiQlgPbP/0f2bRop9QVGYhJWG2KNE4gqhHXgQrpv5zKcDEDayaekttnlSrawbLvHp6IGnUITJio02eezsOeCWl/NBNR9OX7ZuVnjdiwbr1oH6fFzAVabosQGNv3jEZ41rZqKTTy4pvKkmp6aecjjtj3JIKmaFNifGiiEq6SO9FLBnCUfFb2blGmJHkO6fwM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, Date = _t, #"Invoice amount (calculated)" = _t, Quantity = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", Int64.Type}, {"Date", type date}, {"Invoice amount (calculated)", type number}, {"Quantity", Int64.Type}})
in
    #"Changed Type"

[Vijay_A_Verma · Accepted solution]

Use below statement

= Table.InsertRows(#"Changed Type",List.PositionOf(#"Changed Type"[Document ID],4060)+1,{[Document ID=4060, Date=#date(2022,7,15),#"Invoice amount (calculated)"=1584.98,Quantity=25,Region="HEP"]})

Complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZO9bsMwDITfxXPA8F/k2KFAx24dgrz/a1RSnVSmi8ZAAAX4fL47UrfbJpmxXTbUK8aVkbn/YZUEav0k/ffx/rndL7dN0bCSRM3BvJ/8D5IWsiUhK6j1cx5YHyzZFduvahACD5R4ZUnGq4wry2IMOIyOHF9vD3R+6SDbRdUghlM+okMVV1TcIK0WQNYqqIYJESfyHMoYiUYRZw594TiZQWuj5FoF05J/uj+mca+ksDYCb7V6ilZRbyng0+8LUiQlgPbP/0f2bRop9QVGYhJWG2KNE4gqhHXgQrpv5zKcDEDayaekttnlSrawbLvHp6IGnUITJio02eezsOeCWl/NBNR9OX7ZuVnjdiwbr1oH6fFzAVabosQGNv3jEZ41rZqKTTy4pvKkmp6aecjjtj3JIKmaFNifGiiEq6SO9FLBnCUfFb2blGmJHkO6fwM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, Date = _t, #"Invoice amount (calculated)" = _t, Quantity = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", Int64.Type}, {"Date", type date}, {"Invoice amount (calculated)", type number}, {"Quantity", Int64.Type}}),
    Custom1 = Table.InsertRows(#"Changed Type",List.PositionOf(#"Changed Type"[Document ID],4060)+1,{[Document ID=4060, Date=#date(2022,7,15),#"Invoice amount (calculated)"=1584.98,Quantity=25,Region="HEP"]})
in
    Custom1

[Anonymous]

@Vijay_A_Verma Thanks for your reply! To make manual data entry easier, is there a way to omit entering data in some of the columns? I removed one of the columns but it gives error.

For example:

= Table.InsertRows(#"Changed Type",List.PositionOf(#"Changed Type"[Document ID],4060)+1,{[Document ID=4060, Date=#date(2022,1,1),Quantity=25,Region="HEP"]})

[Vijay_A_Verma]

You can't omit. You will have make it blank or null like Quantity=null