Creating new table from columns from existing tables, summing 2 of the columns

[clarkbj71]

Below is a generic example of my data although I have 10 tables I need to group into one rather then the 3 below in my example:  I want to sum Number1 and Number 2 and group by Date, Country and Type.  I cannot do this in the Query Editor as the calculations for Number 1 and Number 2 have to be done after the data is loaded.  I have explored Union, Group by, Summarize but having trouble with the grouping all the tables together.   

Table1
Date	County	Type	Number1	Number2
1/1/2019	USA	A	7702	0.002
2/1/2019	USA	B	5301	0.3
3/1/2019	USA	C	6542	0.543
4/1/2019	USA	D	0	0.653
1/1/2019	Germany	A	8653	0.5
2/1/2019	Germany	B	0	0.25
3/1/2019	Germany	C	4212	1.5
4/1/2019	Germany	D	5	0
1/1/2019	Mexico	A	6542	0.543
2/1/2019	Mexico	A	5542	0.643
3/1/2019	Mexico	A	4542	0.743
4/1/2019	Mexico	A	3542	0.843
1/1/2019	Russia	A	0	0.653
2/1/2019	Russia	B	1	0.753
3/1/2019	Russia	C	2	0.853
4/1/2019	Russia	D	3	0.953
Table2
Date	County	Type	Number1	Number2
1/1/2019	USA	A	7702	0.002
2/1/2019	USA	B	7705	0.004
3/1/2019	USA	C	7708	0.006
4/1/2019	USA	D	7711	0.008
1/1/2019	Germany	A	7714	0.01
2/1/2019	Germany	B	7717	0.012
3/1/2019	Germany	C	7720	0.014
4/1/2019	Germany	D	7723	0.016
1/1/2019	Mexico	A	7726	0.018
2/1/2019	Mexico	A	7729	0.02
3/1/2019	Mexico	A	7732	0.022
4/1/2019	Mexico	A	7735	0.024
1/1/2019	Russia	A	7738	0.026
2/1/2019	Russia	B	7741	0.028
3/1/2019	Russia	C	7744	0.03
4/1/2019	Russia	D	7747	0.032
Table3
Date	County	Type	Number1	Number2
1/1/2019	USA	A	5540	0.003
2/1/2019	USA	B	5301	0.013
3/1/2019	USA	C	5062	0.023
4/1/2019	USA	D	4823	0.033
1/1/2019	Germany	A	4584	0.043
2/1/2019	Germany	B	4345	0.053
3/1/2019	Germany	C	4106	0.063
4/1/2019	Germany	D	3867	0.073
1/1/2019	Mexico	A	3628	0.083
2/1/2019	Mexico	A	3389	0.093
3/1/2019	Mexico	A	3150	0.103
4/1/2019	Mexico	A	2911	0.113
1/1/2019	Russia	A	2672	0.123
2/1/2019	Russia	B	2433	0.133
3/1/2019	Russia	C	2194	0.143
4/1/2019	Russia	D	1955	0.153

[negi007]

@clarkbj71  You can easily achieve it by appending tables in power query window

Select all 10 tables in the powerquery window then select append queries as new. You will get one table having values from all 10 tables. like below.  Please make sure all tables are having same column header. You can then uncheck "enabled load option" for all 10 tables so that you have only one table in the powerbi. Once you have appended all the 10 tables into one, you can either create a column to have the sum of number1 and number 2 in power query window or create a measure in the powerbi desktop that will sum two values.

[clarkbj71]

Sent a reply to you earlier but looks like it didn't go through.  I would love to be able to use append in Query Editor but in order to create the values in Number 1 and Number 2 I had to use about 35 measures, all using different base data.  I did try doing these calculations in Query Editor with Calculated Columns but it really bogged down Power BI so switched back to using the measures which required me to create the 10 different tables.  See my reply earlier to what I am trying to create.  A measure to sum the values from the tables does not work in my visual as I can't get a legend and my slicers don't seem to work for all the visuals which is why I was hoping to create one table from the 10 but just can't get the DAX correct in combining more than two tables.

[pranit828]

Hi @clarkbj71 

I don't understant why you want to group it in the table itself.

groups are automatically done in the table or matrix visual.

Create a UNION table on Power BI Desktop and create a Sum column in the new union table.

Use 'Table' chart and select the columns on order which you want it to be grouped and select the summed column in the bottom.

[clarkbj71]

Sorry my example was a bit off.  My data looks more like this below:  I am looking to create 4 visuals (stacked bar charts), 2 for number 1 and two for number 2.  One by Country and one by Type (in Legend) with Date in X axis and 2 slicers -Country and Type where all graphs filter correclty.  I created three addition lookup tables for my data model (Country, Type and Date) and connected all my other tables to these.  Originally I tried to create a measure to caluclate the sum but the slicers were not filtering all the visuals correctly. Plus I could not use a legend when I used the total measure.  I thought I could avoid all this if I could combine the seperate tables into one.  I am just struggling on how to write the DAX to create the combined table when dealing with more than 2.

Table1
Date	County	Type	Number1	Number2
1/1/2019	USA	A	7702	0.002
2/1/2019	USA	B	5301	0.3
3/1/2019	USA	C	6542	0.543
4/1/2019	USA	D	0	0.653
1/1/2019	Germany	A	8653	0.5
2/1/2019	Germany	B	0	0.25
3/1/2019	Germany	C	4212	1.5
4/1/2019	Germany	D	5	0
1/1/2019	Mexico	A	6542	0.543
2/1/2019	Mexico	A	5542	0.643
3/1/2019	Mexico	A	4542	0.743
4/1/2019	Mexico	A	3542	0.843
1/1/2019	Russia	A	0	0.653
2/1/2019	Russia	B	1	0.753
3/1/2019	Russia	C	2	0.853
4/1/2019	Russia	D	3	0.953
Table2
Date	County	Type	Number1	Number2
1/1/2019	USA	A	7702	0.002
2/1/2019	USA	B	7705	0.004
3/1/2019	USA	C	7708	0.006
4/1/2019	USA	D	7711	0.008
1/1/2019	Germany	A	7714	0.01
2/1/2019	Germany	B	7717	0.012
3/1/2019	Germany	C	7720	0.014
4/1/2019	Germany	D	7723	0.016
1/1/2019	Mexico	A	7726	0.018
2/1/2019	Mexico	A	7729	0.02
3/1/2019	Mexico	A	7732	0.022
4/1/2019	Mexico	A	7735	0.024
1/1/2019	Russia	A	7738	0.026
2/1/2019	Russia	B	7741	0.028
3/1/2019	Russia	C	7744	0.03
4/1/2019	Russia	D	7747	0.032
Table3
Date	County	Type	Number1	Number2
1/1/2019	USA	A	5540	0.003
2/1/2019	USA	B	5301	0.013
3/1/2019	USA	C	5062	0.023
4/1/2019	USA	D	4823	0.033
1/1/2019	Germany	A	4584	0.043
2/1/2019	Germany	B	4345	0.053
3/1/2019	Germany	C	4106	0.063
4/1/2019	Germany	D	3867	0.073
1/1/2019	Mexico	A	3628	0.083
2/1/2019	Mexico	A	3389	0.093
3/1/2019	Mexico	A	3150	0.103
4/1/2019	Mexico	A	2911	0.113
1/1/2019	Russia	A	2672	0.123
2/1/2019	Russia	B	2433	0.133
3/1/2019	Russia	C	2194	0.143
4/1/2019	Russia	D	1955	0.153

[Ashish_Mathur]

Hi,

The Append feature of Power Query should work very well.

[clarkbj71]

Yes append would work great if I could do it in Query Editor but I cannot due to multiple measures (35) I had to create in order to get my two columms of data.  I did figure it out though.  I was trying to make it too complicated and thought I would need to use Union(SelectColumns) but all I needed was Union(Table1, Table2, Table2, etc.).