cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yggdrasill Helper V
Helper V

Add Custom Row (Table.InsertRow)

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.

customRow.PNG

 

Is this possible ?

 

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

 

Thanks in advance

2 ACCEPTED SOLUTIONS

Accepted Solutions
stretcharm Memorable Member
Memorable Member

Re: Add Custom Row (Table.InsertRow)

 

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

View solution in original post

stretcharm Memorable Member
Memorable Member

Re: Add Custom Row (Table.InsertRow)

 

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 

 

View solution in original post

8 REPLIES 8
stretcharm Memorable Member
Memorable Member

Re: Add Custom Row (Table.InsertRow)

 

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

View solution in original post

stretcharm Memorable Member
Memorable Member

Re: Add Custom Row (Table.InsertRow)

 

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 

 

View solution in original post

Yggdrasill Helper V
Helper V

Re: Add Custom Row (Table.InsertRow)

You absolute beast ! So simple

Thanks !

sahilhira162 Helper I
Helper I

Re: Add Custom Row (Table.InsertRow)

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

 

variance_screensht.PNG

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 Memorable Member
Memorable Member

Re: Add Custom Row (Table.InsertRow)

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.

Highlighted
LiChauki
Regular Visitor

Re: Add Custom Row (Table.InsertRow)

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

 

Laz Resolver I
Resolver I

Re: Add Custom Row (Table.InsertRow)

Works great! Thanks

cosminc Post Partisan
Post Partisan

Re: Add Custom Row (Table.InsertRow)

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors