Add new Row to existing table with null Values

[lkshck]

Hey,

I'm having a table from Azure Cost Management with 54 columns and I want to add a custom Row to the table with a credit value in the Cost column and a date value in the Date column. All other columns should contain a null value. Currently I tried it with the following:

= Table.InsertRows(#"Appended Query", 0, {[Date="01.07.2022 00:00:00", Cost="<CreditAmount>"]})

This leads to an error in this new Row for every column. Do I need to add every column name to this query and set them to null or is there any easy way to define null as a default value for all the other columns.

Thanks in advance!

[v-kkf-msft]

Hi @lkshck ,

Please add the code.

    #"Insert Row" = let Names = Table.ColumnNames(#"Changed Type"), Count = List.Count(Names), mylist = List.Repeat({null}, Count)
                    in Table.InsertRows( #"Changed Type", 0, {Record.FromList(List.ReplaceRange(List.ReplaceRange(mylist,0,1,{"01.07.2022 00:00:00"}),3,1,{15}), Names)})

Names: return the names of all columns.
Count: returns the number of columns - 7
mylist: return a list with 7 null values.
Then the list values in position 0 and position 3 (modified as appropriate) are replaced with inserted values.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@lkshck , Create a new table(enter data) with required column name and append it

[lkshck]

Do I need to create the new Table only with the Columns which need to be filled or does all Columns need to exist in the new one?