Insert New Row in Excel Power Query

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
Platform
  1. Windows
I have a table with 15 columns in Excel Power Query and want to add a new first row in my table called "Summer", but I only want "Summer to appear in column 1, row 1 and the rest of the row should be empty. Will need to display the rest of the (15) columns after the new row has been added. How can I achieve this?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
use Table.FromRecords to create Table with single row with Summer in the first column then Append this table with your original table
 
Last edited:
Upvote 0
use Table.FromRecords to create Table with single row with Summer in the first column then Append this table with your original table

If I append, will the record show at the end or the first row of the original table? If the the former is true, then how would I bring the last row as the first row? Thanks.
 
Upvote 0
as I said, Append new single row table with original table and you'll see new row as first in a brand new table

if you want reverse rows in your original table then reverse before Append then Append

edit:
the best way is post a link to shared example excel file which reflect structure and data type

btw. don't quote post if it is not necessary, I know what I said :)
 
Last edited:
Upvote 0
Do I need to convert my table to a list before I can use Table.FromRecords or Table.InsertRows? How would I do that? I'm trying to add the new record (on the very top, base zero) in the Advanced Editor.
 
Upvote 0
Header1Header2Header3Header4
aabbccdd
wweerrtt

with Table.FromRecords

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TFR = Table.FromRecords({[Header1 = "Summer",Header2 = null,Header3 = null,Header4 = null]}),
    TC = Table.Combine({TFR, Source})
in
    TC[/SIZE]

with Table.InsertRows

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TIR = Table.InsertRows(Source, 0, {[Header1 = "Summer", Header2 = null, Header3 = null, Header4 = null]})
in
    TIR[/SIZE]

Header1Header2Header3Header4
Summer
aabbccdd
wweerrtt
 
Upvote 0
Worked beautifully. Used Table.InsertRows method since it's less line to type. Thanks.
 
Upvote 0
Header1Header2Header3Header4
aabbccdd
wweerrtt

with Table.FromRecords

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TFR = Table.FromRecords({[Header1 = "Summer",Header2 = null,Header3 = null,Header4 = null]}),
    TC = Table.Combine({TFR, Source})
in
    TC[/SIZE]

with Table.InsertRows

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TIR = Table.InsertRows(Source, 0, {[Header1 = "Summer", Header2 = null, Header3 = null, Header4 = null]})
in
    TIR[/SIZE]


Header1Header2Header3Header4
Summer
aabbccdd
wweerrtt

Header1Header2Header3Header4
aabbccdd
wweerrtt

with Table.FromRecords

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TFR = Table.FromRecords({[Header1 = "Summer",Header2 = null,Header3 = null,Header4 = null]}),
    TC = Table.Combine({TFR, Source})
in
    TC[/SIZE]

with Table.InsertRows

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TIR = Table.InsertRows(Source, 0, {[Header1 = "Summer", Header2 = null, Header3 = null, Header4 = null]})
in
    TIR[/SIZE]


Header1Header2Header3Header4
Summer
aabbccdd
wweerrtt
Using the Table.InsertRows method, how would I do the same thing but add Summer to the last row instead? Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top