Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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
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
@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"]})
You can't omit. You will have make it blank or null like Quantity=null
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |