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

lbendlin's avatar
lbendlin
Super User
5 years ago

Yes, you can create tables with DAX.  BUT (!) only as table variables.

 

Better do this in  Power Query.

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