Insert New Row in Excel Power Query

[legalhustler]

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?

 

[sandy666]

use Table.FromRecords to create Table with single row with Summer in the first column then Append this table with your original table

 

[legalhustler]

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.

 

[sandy666]

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

 

[sandy666]

or Table.InsertRows(...)

 

[legalhustler]

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.

 

[sandy666]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Header1[/td][td=bgcolor:#5B9BD5]Header2[/td][td=bgcolor:#5B9BD5]Header3[/td][td=bgcolor:#5B9BD5]Header4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]aa[/td][td=bgcolor:#DDEBF7]bb[/td][td=bgcolor:#DDEBF7]cc[/td][td=bgcolor:#DDEBF7]dd[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ww[/td][td]ee[/td][td]rr[/td][td]tt[/td][/tr]
[/table]

with Table.FromRecords

[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

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Header1[/td][td=bgcolor:#70AD47]Header2[/td][td=bgcolor:#70AD47]Header3[/td][td=bgcolor:#70AD47]Header4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Summer[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]aa[/td][td]bb[/td][td]cc[/td][td]dd[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ww[/td][td=bgcolor:#E2EFDA]ee[/td][td=bgcolor:#E2EFDA]rr[/td][td=bgcolor:#E2EFDA]tt[/td][/tr]
[/table]

 

[legalhustler]

Worked beautifully. Used Table.InsertRows method since it's less line to type. Thanks.

 

[sandy666]

sure

have a nice day

 

[legalhustler]

Header1	Header2	Header3	Header4
aa	bb	cc	dd
ww	ee	rr	tt

with Table.FromRecords

[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

[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]

Header1	Header2	Header3	Header4
Summer
aa	bb	cc	dd
ww	ee	rr	tt

Header1	Header2	Header3	Header4
aa	bb	cc	dd
ww	ee	rr	tt

with Table.FromRecords

[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

[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]

Header1	Header2	Header3	Header4
Summer
aa	bb	cc	dd
ww	ee	rr	tt

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