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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rashidanwar
Helper III
Helper III

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.  

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Fowmy
Super User
Super User

@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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.