Forum Discussion
Add new row to table and fill with specific data for each column
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"
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
3 Replies
- Vijay_A_Verma
Most Valuable Professional
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 Custom1Vijay_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