Forum Discussion
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
| 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
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
7 Replies
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
Yes, you can create tables with DAX. BUT (!) only as table variables.
Better do this in Power Query.
Thanks lbendlin I guessed as much, my M skills don't extend to this! Any ideas on where I should start.
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.
