need help to create table

[Herndon_powerbi]

Hi all,

          I have two tables using them I want to get a new table. Please let me know how can I do this in Power BI. Thanks in adv

[Anonymous · Accepted solution]

Hi @Herndon_powerbi ,

I have done it in two ways, please check:

• Use DAX:

NewTable using DAX = 
var _t1=SUMMARIZE('Table1',"subject","english","total_marks",SUM(Table1[english]))
var _t2=SUMMARIZE('Table1',"subject","maths","total_marks",SUM(Table1[maths]))
var _t3=SUMMARIZE('Table2',"subject","science","total_marks",SUM('Table2'[science]))
return UNION(_t1,_t2,_t3)

• In Power Query:

For Table1:  select english and maths column -->click "Unpivot columns"--> select Attribute column --> click "Groupby"

For Table2:  select science column -->click "Unpivot columns"--> select Attribute column --> click "Groupby"

And then click "Append Queries as new " to create a new table like this:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Herndon_powerbi · Accepted solution]

Thank you so much

[Anonymous · Accepted solution]

Hi @Herndon_powerbi ,

I have done it in two ways, please check:

• Use DAX:

NewTable using DAX = 
var _t1=SUMMARIZE('Table1',"subject","english","total_marks",SUM(Table1[english]))
var _t2=SUMMARIZE('Table1',"subject","maths","total_marks",SUM(Table1[maths]))
var _t3=SUMMARIZE('Table2',"subject","science","total_marks",SUM('Table2'[science]))
return UNION(_t1,_t2,_t3)

• In Power Query:

For Table1:  select english and maths column -->click "Unpivot columns"--> select Attribute column --> click "Groupby"

For Table2:  select science column -->click "Unpivot columns"--> select Attribute column --> click "Groupby"

And then click "Append Queries as new " to create a new table like this:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.