Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding rows dynamically to a table

Hi All, I have two tables: 1. Dynamic summarized table 2. I am trying to create table 2 - a new table with the same data as Table 1 but with extra rows.   Table 1: dynamic summarized table Na...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    According to my understand, you want to dynamically add rows based on the original table, right?

    I did it in two ways.  And here is my pbix file.

     

    1.Use DAX

    UnionedTable =
VAR _allNames =
    ALLSELECTED ( Table1[Name] )
VAR _newTable =
    ADDCOLUMNS ( _allNames, "New", "All" )
RETURN
UNION ( Table1, _newTable )

    2.Follow these steps in Query Editor:

    Add a custom column(set value as “All”) -->Select the Name column ,use “unpivot other Columns”-->Delete the "Attribute" column ,then the table will be transformed as what you want :

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYnWglJyDLCcxyBrKcwSwXIMsFzHIFslyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Category", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "newColumn", each "All"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Name"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks lbendlin I guessed as much, my M skills don't extend to this! Any ideas on where I should start. 

lbendlin's avatar
lbendlin
Super User
5 years ago

Here's a crude example

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYnWglJyDLCcxyBrKcwSwXIMsFzHIFslyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "All"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Name"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Name", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Value", "Category"}})
in
    #"Renamed Columns"

 

I am sure there are more elegant ways.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    lbendlin  Adding a column and unpivoting - that's a great idea. I haven't got it working yet but schooling up. 

    Thanks. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      lbendlin hoping this will be quick, 

       

      I seem to be having issues with the following rows:

      #"Added Custom" = Table.AddColumn(Source, "Custom", each "All"),
      #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Name"}, "Attribute", "Value"),
      #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Name", "Value"})

       

      "I named the custom column "Custom", but what is the "Name' column for? This is throwing an error and if I change it to "Custom" it still throws an error. 

      • lbendlin's avatar
        lbendlin
        Super User
        5 years ago

        Create a blank query, open it in Advanced editor, and replace the entire code with what I posted.