Forum Discussion

Yggdrasill's avatar
Yggdrasill
Icon for Responsive Resident rankResponsive Resident
8 years ago
Solved

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.   Is this possib...
Need Help
Show and Tell
Tips and Tricks
  • stretcharm's avatar
    stretcharm
    8 years ago

     

    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's avatar
    stretcharm
    8 years ago

     

    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's avatar
sahilhira162
Icon for Helper I rankHelper I
8 years ago

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's avatar
stretcharm
Icon for Memorable Member rankMemorable Member
8 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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