Forum Discussion

CMoppet's avatar
CMoppet
Icon for Helper IV rankHelper IV
4 years ago
Solved

Combining data from multiple tables in to single reporting data point

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)...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.



johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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