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 

     

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

 

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

    You absolute beast ! So simple

    Thanks !

  • 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.