What is the easiest way to sum a value column from multiple tables?

[startrunmspaint]

I'm setting up a new spreadsheet that has multiple tables that each list a date and value (among other fields).

I want to create a summary table that will total the values from each table by date. What is the easiest way to accomplish this? Is there any functionality in Excel 2013 or beyond that can link tables and let me sum using some query or power pivot functionality so that I don't need to click on each table? I anticipate adding more tables during the life of this project so I don't want to see a sumif of Table A + Table B + Table C in my formula.

To clarify:
Table A lists various dates and values
Table B lists various dates and values
Table C lists various dates and values

I would like Table D to total the values from A-C for each date.

 

[ImkeF]

It's pretty easy if your Tables are real Excel-tables and not just ranges.

let
// Access your current Excel-Workbook "from outside" by opening it as a new source: "Get External Data - From File - Excel
Source = Excel.Workbook(File.Contents("........CurrentWorkbook.xlsx"), null, true),

// Filter objects of type "Table" and set additional filters if necessary
Filter = Table.SelectRows(Source, each [Kind] = "Table"),

// Expand your data - you don't have to type in this command, you can also just click if you're building this query manually
#"Expanded Data" = Table.ExpandTableColumn(Filter, "Data", Table.ColumnNames(Filter[Data]{0}), Table.ColumnNames(Filter[Data]{0}))
in
#"Expanded Data"

Please tell if you need a solution for simple ranges instead.

 

[startrunmspaint]

Can you simplify this for me a little bit. I have defined my tables as Excel tables. In this short example, I have Table1 and Table2 each with dates from Jan1 to Jan3 with corresponding values. I want to create a Table3 with a unique dates list from Table1 and Table2 and the total value for all of those dates.

 

[ImkeF]

You can do this all by using the UI. Have a look at my rookie-video :https://youtu.be/kq85a8vRjt4

This is how the code will look like:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Imke\Google Drive\ME_ConsolidateAllTablesAtOnce.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Amount", "Date"}, {"Amount", "Date"}),
    #"Grouped Rows" = Table.Group(#"Expanded Data", {"Date"}, {{"SumAmount", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"

 

[startrunmspaint]

Wow, thanks! A personalized video too. That was really helpful!

Danke! Schönen Tag noch!