Creating a specimen list by Combining Tables

This is an advanced technique that can be used for creating a specimen list.  It is similar to the UNION command in SQL.

The tables must have the same fields to be combined successfully in Power BI. 

The example below illustrates how to create a specimen list.  To prepare the data for this merge follow the steps below.

• Create three tables from the following lab testing tables, Moisturecontent, LiquidLimit and Grading  - More tables can be added if you want to combine the specimens from more tables.
• Each table should only return the following fields.  LocationID, DepthTop, Type, SampleReference, SpecimenReference and Depth Specimen Top.
• Create a new keyfield column for each table so that the lab testing data can be joined via a relationship later on.

To create a new table that merges these tables together follow the steps below:- 

• Open the data View for your report by clicking on the data grid icon down the left-hand side of your report.
• Select New Table from the Table Tools Ribbon.  This creates a blank table and presents the option to define the table using an equation.
• Enter the following equation into the equation.  This will name the table as "AllSpecimens" and will merge the data together from the LiquidLimit, moisturecontent and grading tables.
  • Allspecimens = union(liquidLimit,moisturecontent,grading)
• A table will now be generated and displayed in the grid below the equation. 
• There is one additional step we need to do as there may be the same specimen in more than one table.  If this is the case, then the specimen will be listed twice.  Modify the equation to include the distinct command as shown below so that duplicates will be removed from the table.
  • Allspecimens = distinct(union(liquidLimit,moisturecontent,grading))

Once this table has been created, it can be used to join to the lab test results tables to create a single table showing all lab results by the specimen.