Combining data from multiple tables in to single reporting data point

[CMoppet]

Hello,  I am on Day 2 using Power BI, so apologies in advance if I'm unable to explain my request with the correct lingo...

I have 4 tables of complaints data (Table 1, Table 2, Table 3, Table 4), and each table has a column titled 'Category'.  Within this column, the results will either be 'Food', 'Machine', or 'Other'. 

All four tables also have a column titled 'Period', with results being in the format of mm yyyy

Aside from these two columns which are common to all four tables, the rest of the columns/data is different in each table.

I want to create a chart that shows the overall picture of how complaints each period are split between Food, Machine, and Other.  I am struggling to figure out how to 'roll up' the results from all 4 tables in to one summary view.  I've tried doing a measure that adds the Categories together, but this doesn't give me the right result.

Please could someone help me, assuming I've explained this well enough?

ManyThanks

[johnt75]

You should create a couple of dimension tables which you can link to each of the others. You should create a proper date table, there's plenty of articles and videos on creating those, and you can create a new category dimension table by selecting New Table from the Modelling tab of the ribbon and entering

Category Dimension = DISTINCT( UNION(
ALLNOBLANKROW( 'Table1'[Category]),
ALLNOBLANKROW( 'Table2'[Category]),
ALLNOBLANKROW( 'Table3'[Category]),
ALLNOBLANKROW( 'Table4'[Category])
))

then link this new table in a one-to-many relationship with the 4 other tables.

You can then create a new measure which will show the number of complaints per category like

Num complaints = COUNTROWS( 'Table1') + COUNTROWS( 'Table2') + COUNTROWS( 'Table3') + COUNTROWS( 'Table4')

put that into a visual with the category column from the new dimension table and it will give the numbers for each category

[Daryl-Lynch-Bzy · Accepted solution]

Hi @CMoppet - I would recommend appending the tables into one table in Power Query.  Use the Append table function.

After combining the table you should remove any unnecessary columns.  If you still need to know which table provide which data, you can add a new column to each table before Combining.  In the new column, put something like "Table 1", "Table 2" etc.  This can be used in Slicer when visualising the data, or in CALCULATE functions to measures.