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
  • LiChauki's avatar
    LiChauki
    Regular Visitor
    8 years ago

    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's avatar
      puraskar_award
      Frequent Visitor
      5 years ago

      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

        

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Works great! Thanks