Manually add data to a table

You can manually add data to a table by using M code in the Power Query Advanced Editor.

Usually this is a bad idea. The added values are hard coded and it is not immediately obvious that data is being added to the import. It is better to add the data to another file, import and append it to the table that you want to update.

With your data saved in a table in the current Workbook.

Click on a cell in the Table with the data.
Navigate to the Data menu tab and click on From Table/Range.

The data is selected and the Power Query window displayed.

Navigate to the Home menu tab and click on the Advanced Editor icon.

Add a new row using the Table.InsertRows command.

#"Add New Row" = Table.InsertRows(#"Changed Type", 0, {[Id="E", Amount=50]})

All the columns in the table need to be referenced even if data is not being added to each one.

Update the reference in the final In section.
Click on the Done button.

The new row will be displayed.
Click on the Close & Load icon.

The table including the new row will be loaded back to Excel.

Adding more than one row

To add more than one row of data to the table, follow the same steps above.

When using the Table.InsertRows command, separate each new row with a comma.

Add two rows with two columns
#"Add Two New Rows" = Table.InsertRows(#"Changed Type", 0, {[Id="E", Amount=50], [Id="F", Amount=60]})

Click on the Done button.

The new rows will be displayed.
Click on the Close & Load icon.

The table including the new rows will be loaded back to Excel.