Adding rows dynamically to a table

[Anonymous]

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

Name	Category
A	A
B	B
C	C
D	D
E	E

Table 2:

Trying to achieve the following expected result. Adding rows to a new table with the same information as summarized Table 1

Name	Category
A	A
A	All
B	B
B	All
C	C
C	All
D	D
D	All
E	E
E	All

Is it possible to create table 2 with Dax? I have not found a way to do it easily. Please note I am not keen on doing this manually as Table 1 changes often which is why it is a dynamic table, summarized from the main fact table. 

Thanks so much in advance

[Anonymous · Accepted solution]

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]

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

Better do this in  Power Query.

[Anonymous]

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

[lbendlin]

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]

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

Thanks. 

[Anonymous]

@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]

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