Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
shahid_tanmoy
Helper I
Helper I

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 the table wasnt manually created by me.

Is there any syntax that can do this? 

5 REPLIES 5
mussaenda
Community Champion
Community Champion

Hi @shahid_tanmoy,

 

you have multiple options.

You can manually enter data and create a new table then append it to your table.

Second option (what I do usually) i create a column with my delimiter string and split it into rows. If there is no any delimiter present, it will not split.

 

Hope this helps

ronrsnfld
Community Champion
Community Champion

Here is an example:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYzLEcAgCER74eygohGoxfFg/um/gYAHZhd4u73D3A8IsMUcKRGZzVjZRGwqssAIHc7rtrXFYtD6EkpxcV4LMi/seT876OpK2S1y83YPV0FVhTF+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Previous Step" = Table.TransformColumnTypes(Source,{
        {"Column1", type text}, {"Column2", type date}, {"Column3", type number}, {"Column4", Int64.Type}, {"Column5", Currency.Type}}),

    #"Add row of nulls" = 
        let 
            #"Null List" = List.Repeat({null}, Table.ColumnCount(#"Previous Step")),
            #"Null Row" = Record.FromList(#"Null List", Table.ColumnNames(#"Previous Step")),
            #"Add Null Row" = Table.FromRecords(Table.ToRecords(#"Previous Step") & {#"Null Row"})
           
        in 
            #"Add Null Row"

in
    #"Add row of nulls"

 

 

adudani
Super User
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Hi there, 

This doesnt work cause I cannot append the value as null

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"
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors