Add a row to a table filling just some of the fields

[carloP24]

Hello everyone, this is my first post, so apologize for any mistakes!

In Power Query I would like to insert a new row to a table and filling JUST SOME of the row's fields with values, without mentioning all the fields that I don' need to fill (since in my case there are a lot of them). See tables below as examples:

I would like to go from this table

Isin	Portfolio	MV
IT78978	PREVI	1.000
XS87979	PREVI	2.000

To this one

Isin	Portfolio	MV
IT78978	PREVI	1.000
XS87979	PREVI	2.000
	Area D	3.000

As you can notice, I would like to add the last row and filling ONLY the "Portfolio" and "MV" field with a value, without mentioning the "Isin" field.

Thank you very much in advance

[edhans · Accepted solution]

See this code. You cannot just insert a row in Power Query, but you can create a new table with just the record(s) you need, then append.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gwxt7A0t1DSUQoIcg3zBNKGSrE60UoRwRbmluaWSOJGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Isin = _t, Portfolio = _t, MV = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MV", Currency.Type}}),
    NewRow = #table(
            {"Portfolio", "MV"},
                {
                    {"Area D", 3}
                }
          ),
    #"Appended Query" = Table.Combine({#"Changed Type", NewRow})
in
    #"Appended Query"

The Append line appends the table in the Changed Type step, your original two rows, with the NewRow I created.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[carloP24]

Thank you very much, perfect solution! I didn't know the #table  functionality!