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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
willq
New Member

Aggregating values from different tables to create visual with one table

Hi,

I am new to power bi and would like to know if this can be done and how to do it. I stumbled across a SUMX, Union, and Filter but I'm not sure if that's correct? Any guidance on this would be helpful. Thanks!

 

I have 3 tables (Table1, Table2, Table3) with columns - Entity_ID, MCC, Paid_Amount. 

 

Table 1

Entity_IDMCCPaid_Amount
Table1IP$2.00
Table1OP$3.00
Table1ER$3.00
Table1IP$5.00
Table1OP$7.00

 

Table 2

Entity_IDMCCPaid_Amount
Table2PCP$4.00
Table2ER$5.00
Table2ER$6.00
Table2IP$6.00
Table2OP$8.00

 

Table 3

Entity_IDMCCPaid_Amount
Table3PCP$7.00
Table3PCP$2.00
Table3ER$9.00
Table3IP$7.00
Table3OP$3.00

 

I want to create a table that I can use as a visual that will aggregate the total Paid_Amount of the MCC types (PCP, ER, IP, OP etc.) across each table displayed below.

 

Entity_IDPCPERIPOP
Table1    
Table2    
Table3    

 

 

2 ACCEPTED SOLUTIONS
RossEdwards
Solution Sage
Solution Sage

Looking at your 3 tables, it seems like they use a common structure of Entity_ID, DMCC, and Paid_Amount.

Rather than have 3 tables, you can set up your Power Query (Transform data) to append all 3 tables into a new single table.  Then you can mark the original tables as "Enable Load = False" and only import the resulting single table into Power BI.

 

You can do this on the home menu of transform data where you select "Append Queries as new"

 

Append queries - Power Query | Microsoft Learn

View solution in original post

Jihwan_Kim
Super User
Super User

Hi,

One of ways is to create append table in Power Query Editor, like below, and load it to Power BI desktop.

Please check the below picture and the attached pbix file.

Table.Combine - PowerQuery M | Microsoft Learn

 

let
    Source = Table.Combine({#"Table 1", #"Table 2", #"Table 3"})
in
    Source

 

Jihwan_Kim_0-1717469502390.png

 

Jihwan_Kim_1-1717470082787.png

 

 


 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     

   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     

   


 


View solution in original post

6 REPLIES 6
willq
New Member

Hi @RossEdwards , 

Thank you for sharing this response. Just a quick question since the examples I shared were only a subset of the actual data I will be working with. Would this "Append Queries" feature work with a large set of data??. I may have to append up to 12 tables each with 30,000+ rows with similar fields.

Yes it works great with large datasets.  You just need to make sure all of the columns you want are identical in both name and whether those characters are upper and lower case.

 

To make your life easy, you can select only the columns you want in each table and right click -> Remove other columns.  This can make your code quite robust should any extra columns random pop in.

Alright thank you for clarification. I'll give it a shot to see where I land with it. Much appreciated!

Jihwan_Kim
Super User
Super User

Hi,

One of ways is to create append table in Power Query Editor, like below, and load it to Power BI desktop.

Please check the below picture and the attached pbix file.

Table.Combine - PowerQuery M | Microsoft Learn

 

let
    Source = Table.Combine({#"Table 1", #"Table 2", #"Table 3"})
in
    Source

 

Jihwan_Kim_0-1717469502390.png

 

Jihwan_Kim_1-1717470082787.png

 

 


 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     

   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     

   


 


@Jihwan_Kim Thanks for sharing the resources and your examples. It seems this may be the easiest way to get this done. I am a bit new to power bi but I'll try it out and see where I land with it. Thank you!

RossEdwards
Solution Sage
Solution Sage

Looking at your 3 tables, it seems like they use a common structure of Entity_ID, DMCC, and Paid_Amount.

Rather than have 3 tables, you can set up your Power Query (Transform data) to append all 3 tables into a new single table.  Then you can mark the original tables as "Enable Load = False" and only import the resulting single table into Power BI.

 

You can do this on the home menu of transform data where you select "Append Queries as new"

 

Append queries - Power Query | Microsoft Learn

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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