Forum Discussion
Append tables
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.
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
rashidanwar
So the following steps in Power Query:
1. Duplicate the Sales Amount Column2. 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
3 Replies
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
rashidanwar
So the following steps in Power Query:
1. Duplicate the Sales Amount Column2. 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 queryrashidanwar , 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)
