March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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?
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
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"
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.
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |