Add Custom Row (Table.InsertRow)

[Yggdrasill]

Hi !

I have a dimension table with few rows and few values from a database.

I want to add a custom row to this table without having to create a new one and Append.

Is this possible ?

I've tried to use the Table.InsertRows() but the explanations on MSDN are not really helping.

Thanks in advance

[stretcharm · Accepted solution]

InsertRows will do what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Dimension = _t, #"Dimension Name" = _t, #"From Data Source" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension", Int64.Type}}),
    Custom1 = Table.InsertRows(#"Changed Type",2, { [Dimension = 3, Dimension Name = "Custom Row", From Data Source = "No"] })
in
    Custom1

[stretcharm · Accepted solution]

InsertRows will do what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Dimension = _t, #"Dimension Name" = _t, #"From Data Source" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension", Int64.Type}}),
    Custom1 = Table.InsertRows(#"Changed Type",2, { [Dimension = 3, Dimension Name = "Custom Row", From Data Source = "No"] })
in
    Custom1 

[sahilhira162]

Hi @stretcharm

Can you help me out here, I am trying to SUM the values of all column in to NET INCOME row. I used the following code, but its giving me error values. 
PFA screenshot of the data and code.

Thanks in advance.

Regards,

Sahil

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"scode"= _t, #"sdesc" = _t, #"MyYTDComparative" = _t, #"MyYTDVariance" = _t, #"MyYTDBaseline" = _t, #"MyYTDVariancePercentage" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"MyYTDComparative", Float64.type}, {"MyYTDVariance", Float64.type}, {"MyYTDBaseline", Float64.type}, {"MyYTDVariancePercentage", Float64.type}}),
Custom1 = Table.InsertRows(#"Changed Type",2, { [scode = 829999, sdesc = "NET INCOME" ,MyYTDComparative = SUM(MyYTDComparative), MyYTDVariance = SUM(MyYTDVariance), MyYTDBaseline = SUM(MyYTDBaseline), MyYTDVariancePercentage = AVERAGE(MyYTDVariancePercentage)] })
in
Custom1

[stretcharm]

You could look at this Table.AggregateTableColumn

https://msdn.microsoft.com/en-us/library/mt260728.aspx

However I would probably just

create a reference to the table,

group it to get a total

add the non new columns for aggregated fields to get the row the same shape

then append the original and new table together as a new Query.

You may what to add a sort order column that can be used for the total to the bottom.

[Pttigui]

Hello,

Just to add to Stretcharm, you can do it by selecting all the columns you want to use as grouping criterias and then click group by. All the columns will already be added as grouping criteria. Originally I did it line by line so I share the trick 🙂

Guillaume

[cosminc]

Hi

Can you help please with this issue?

https://community.powerbi.com/t5/Desktop/Insert-custom-rows/m-p/731157#M352721

i struggle to add in a table some calculated rows based on original rows

thanks a lot!

Cosmin

[Yggdrasill]

You absolute beast ! So simple

Thanks !

[stretcharm · Accepted solution]

InsertRows will do what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Dimension = _t, #"Dimension Name" = _t, #"From Data Source" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension", Int64.Type}}),
    Custom1 = Table.InsertRows(#"Changed Type",2, { [Dimension = 3, Dimension Name = "Custom Row", From Data Source = "No"] })
in
    Custom1

[Anonymous]

Works great! Thanks

[LiChauki]

Hi

Is it possible to add a row based on certain conditions?

The logic for my query is:

Current table

Product number    Province

A001                       Ontario

A001                      Alberta

B001                       Quebec

B001                      Alberta

For each product number in the table IF (Province) <> "Yukon Territory" then INSERT row "Product number and Province = "Yukon Territory"

IF (Province) <> "Quebec" then INSERT row "Product number and Province = "Quebec"

Desired output 

Product number    Province

A001                       Ontario

A001                      Alberta

A001                      Yukon Territory

A001                      Quebec

B001                       Quebec

B001                      Alberta

B001                      Yukon Territory

[puraskar_award]

Any luck on this? I know we need to change the "Dimension = 3" to a dynamic number that is equal to total columns in the table as one year may have 2 columns but later may have 3 or more as in finance you expect your business needs to grow. Adding a condition prior to that step to validate the content value of the column is what we need to do. Hopefully someone can suggest what function could accomplish these two things to modify code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Dimension = _t, #"Dimension Name" = _t, #"From Data Source" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension", Int64.Type}}),
    Custom1 = Table.InsertRows(#"Changed Type",2, { [Dimension = 3, Dimension Name = "Custom Row", From Data Source = "No"] })
in
    Custom1