Results 1 to 9 of 9

Thread: Insert New Row in Excel Power Query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,046
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert New Row in Excel Power Query

    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?

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Insert New Row in Excel Power Query

    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 by sandy666; Aug 15th, 2019 at 01:03 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,046
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Row in Excel Power Query

    Quote Originally Posted by sandy666 View Post
    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.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Insert New Row in Excel Power Query

    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 by sandy666; Aug 15th, 2019 at 01:27 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Insert New Row in Excel Power Query

    or Table.InsertRows(...)
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,046
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Row in Excel Power Query

    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.

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Insert New Row in Excel Power Query

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


    with Table.FromRecords

    Code:
    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
    with Table.InsertRows

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TIR = Table.InsertRows(Source, 0, {[Header1 = "Summer", Header2 = null, Header3 = null, Header4 = null]})
    in
        TIR
    Header1 Header2 Header3 Header4
    Summer
    aa bb cc dd
    ww ee rr tt
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,046
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Row in Excel Power Query

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

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Insert New Row in Excel Power Query

    sure

    have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •