Forum Discussion

shahid_tanmoy's avatar
shahid_tanmoy
Icon for Helper I rankHelper I
3 years ago

How do I add a row in my power query editor?

Hi there,  I have a query that is sourced from a database, I want to add a row to it that is just null or something else written in it.  There is no way to practically do it that I can see since th...
Need Help
adudani's avatar
adudani
Icon for Memorable Member rankMemorable Member
3 years ago

hi shahid_tanmoy ,

 

I think you have to create a table with the row you would like to add using "Enter Data" in the same table structure as the table from the database.

 

Then append the tables together.

 

kindly note, this would lead to a table in "Dual" Storage mode.

shahid_tanmoy's avatar
shahid_tanmoy
Icon for Helper I rankHelper I
3 years ago

Hi there, 

This doesnt work cause I cannot append the value as null

  • adudani's avatar
    adudani
    Icon for Memorable Member rankMemorable Member
    3 years ago

    hi shahid_tanmoy ,

    see this example:

     

     

    create two blank queries.

    copy paste the following code in the advanced editor.

     

    Ouput:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRqDm0QME3Ma80LTG5pLQotQjIBQl5VBakFuVk5mUD2RioRilWJ1rJEKo0JLU4JxGbMgWIgoySkgIrff0SkDK95PxDC0AQJg02yQhqkl9mcXFiHlYb4cbkgdUAzckFGQMUB5tgDDUhKDUvsTSnBJt+7O7D6W6wsSZQY8Pyc8rycfkPma9LpMGmIJZCSH5lfgnWoAOHhr4jkS6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID ", Int64.Type}, {"  Manufacturer  ", type text}, {"  Hyperlink             ", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([#"  Hyperlink             "], "http://") then 1 else 0),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"  Manufacturer  ", "Manufacturer"}, {"  Hyperlink             ", "Hyperlink"}, {"ID ", "ID"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", TabletoAppend})
in
    #"Appended Query"

     

     

    Table to Append

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUYKi2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Manufacturer = _t, Hyperlink = _t, Custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Manufacturer", type text}, {"Hyperlink", type text}, {"Custom", type text}})
in
    #"Changed Type"