none
Power Query - Adding new Rows

    Question

  • Hi,

    I have created a Power Query that builds a dimension table with a Key index column. The Key value for normal rows start at 1.

    Is it possible to add a new row with Power Query that will represent "NO VALUE" with a Key value of -1?

    Thanks in advance,

    Pål


    Monday, October 21, 2013 12:20 PM

Answers

  • Hi Pal,

    I think the easiest way to do this would be to create a one-row table in your sheet (one row plus headers) that has the same structure as the original table. You can create a query on top of this sheet table by using "From Table" in the Power Query ribbon tab. Then use "Append" (also in the Power Query ribbon tab) to append the original and the newly created query.

    Thanks,
    M.

    Tuesday, October 22, 2013 1:10 AM
    Owner

All replies

  • Hi Pal,

    I think the easiest way to do this would be to create a one-row table in your sheet (one row plus headers) that has the same structure as the original table. You can create a query on top of this sheet table by using "From Table" in the Power Query ribbon tab. Then use "Append" (also in the Power Query ribbon tab) to append the original and the newly created query.

    Thanks,
    M.

    Tuesday, October 22, 2013 1:10 AM
    Owner
  • I know this is an older thread, but I've personally found that I prefer to not introduce additional physical tables in Excel to achieve this.  Rather, I insert the "Unknown" dimensional record as a step using M:
    Append = Table.Combine({#"LastStep", Table.FromRecords({[a=-1], [b="NO VALUE"]})})
    Friday, March 20, 2015 2:45 AM
  • The "easiest" way?  how absurd...it should be as easy as right click to add a row to PowerQuery otherwise it lacks, dare I say, Power.  Your everyday user will not find this intuitive and will quickly abandon the toolset. 
    Monday, April 20, 2015 3:26 PM
  • Power Query has made great strides in taking some of the most common use cases and making them nice little ribbon options, but at the end of the day, ETL is a complex task - and the adding of records into an ETL flow has always required knowledge of either SQL or SSIS transformation script language.  Understanding "M" is necessary for those who would like to make Power Query into a truly robust ETL tool, and while I as a BI consultant would love to see such functionality, at the end of the day there's always a bridge to be crossed for those who are willing to learn more, and those that do not wish to.
    Friday, October 16, 2015 9:06 PM