Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

NameCategory
AA
BB
CC
DD
EE

 

Table 2:

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

NameCategory
AA
AAll
BB
BAll
CC
CAll
DD
DAll
EE
EAll

 

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

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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 )

9.3.1.1.png

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"

 9.3.1.2.png9.3.1.3.png

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

View solution in original post

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

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 )

9.3.1.1.png

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"

 9.3.1.2.png9.3.1.3.png

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
Super User
Super User

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

 

Better do this in  Power Query.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Thanks. 

Anonymous
Not applicable

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors