Append tables

[rashidanwar]

Hi Everyone

I have 2 tables.
Table 1 contains emlpoyees' actual performance and;
Table 2 contains employees' target perfprmance
Table 1 looks like as follow
Date               Name        Product    Qty Sold     Sales Amount
1/1/2021        Mr A          B               2                 1,000
2/1/2021        Mr A          A               3                 1,000
3/1/2021        Mr A          C               1                 1,000

Table 2 looks similar to table 2 in columns, buts its a targt data
Date               Name        Product    Qty Sold     Sales Amount
1/1/2021        Mr A          B               1                 1,000
2/1/2021        Mr A          A               4                 1,333
3/1/2021        Mr A          C               2                 2,000

I want the output as follow
Date               Name        Product    Qty Sold-Actual     Sales Amount-Actual     Qty Sold-Target     Sales Amount-Target       
1/1/2021        Mr A          B               2                            1,000
2/1/2021        Mr A          A               3                            1,000
3/1/2021        Mr A          C               1                            1,000
1/1/2021        Mr A          B                                                                                      1                             1,000
2/1/2021        Mr A          A                                                                                      4                             1,333
3/1/2021        Mr A          C                                                                                      2                              2,000

Summary want to append the first 3 columns (dimensions) to the existing columns but wants to add additional columns for the taget meassures(Qty sold and sales amount).

Thank you so much for all your help.  

[mahoneypat · Accepted solution]

There are at least two good ways you could do this.

1. Merge your second table into the first using the first three columns, and then write measures like this

Qty Sold - Actual = SUMX(Table, Table[Qty Sold] - Table[Qty Target])

2. Add a custom column called "Type" (or whatever you want) with text values of "Sold" and "Target" in the two tables, and then append them (load only the appended table) and then write measures like

Qty Sold - Actual =

var soldqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Sold")

var targetqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Target")

return soldqty - targetqty

Note in both cases, it is recommended to use measures instead of creating calculated columns.

Pat

[Fowmy · Accepted solution]

@rashidanwar 

So the following steps in Power Query:

1. Duplicate the Sales Amount Column

2. Rename the  Qty Sold     Sales Amount to something line  Qty Sold-Target and Sales Amount Target
3. Select Table 1 and choose append as a new query

[mahoneypat · Accepted solution]

There are at least two good ways you could do this.

1. Merge your second table into the first using the first three columns, and then write measures like this

Qty Sold - Actual = SUMX(Table, Table[Qty Sold] - Table[Qty Target])

2. Add a custom column called "Type" (or whatever you want) with text values of "Sold" and "Target" in the two tables, and then append them (load only the appended table) and then write measures like

Qty Sold - Actual =

var soldqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Sold")

var targetqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Target")

return soldqty - targetqty

Note in both cases, it is recommended to use measures instead of creating calculated columns.

Pat

[amitchandak]

@rashidanwar , rename the last two columns in the second table. Append in power query and add index column. Use the same index column in visual (not summarized)