Report 6 - Lab Summary Table

https://communities.bentley.com/cfs-file/__key/communityserver-wikis-components-files/00-00-00-06-06/Lab-Test-Summary-without-Geology.pbit

This template uses many Power BI data techniques to combine results from lab testing tables.

Data for each lab testing table is returned once into a table that contains the results and the specimen details.  These tables can be modified in the Power Query Builder.

Each of the lab testing tables has a new field added for the KeyField set.  This is because PowerBI can only join on one field.

The Allspecimens table is created using a UNION and DISTINCT statement.  This table contains all the specimens for the tables returned.

Data Relationships are then set up between the Allspecimens table and the original result tables.

Finally the Labresults table is created using an NATURALLEFTOUTERJOIN between the Specimen tables and the result tables.

The Labresults table is the one that is used to create the tables in the report.

In order to add test tables to this report you need to following the following steps:-

1. Return the keyfields and the results from the testing table using Power Query and the Advanced Editor - this table is referred to below as the DataTable
2. Add a KeyFieldColumn to the DataTable
3. Include the DataTable in the Distinct Union command that creates the AllSpecimens table
4. Create a ResultsTable from the DataTable using the SELECTCOLUMNS command, including only the KeyField and the results you want to include in the final grid.
5. Create a relationship between the AllSpecimens table and the ResultsTable
6. Include the ResultsTable in the Labresults table by adding an additional NATURALLEFTOUTERJOIN statement