Power BI Blog: Datamarts – Part 5

Welcome back to this week’s edition of the Power BI blog series.  This week, we will look at generating queries using the user interface (UI) available for datamarts.

Datamarts are self-service analytics solutions, enabling users to store and explore data that is loaded in a fully managed database.  Since datamarts are usually a subset of the full database, teams may be given access to the information they require only, enabling them to share relevant data and insights within those teams.

Last week, we began looking at how Power BI supports datamarts.  This week, let’s take a look at how we may create queries within a datamart.

The design icon on the bottom left-hand side allows us to select, drag and filter tables, remove columns and merge or append queries.  In this example, we want to find the top sales order, thus the Sales and SalesOrder tables have been selected and dragged to the stage screen.  Then, we can press the plus icon  (+)  as shown below: 

We may then select to ‘Merge queries as new’.

Like transforming data in Power BI desktop, merging queries allows us to select the tables to merge (i.e. incorporate additional columns / fields).  We must specify the matching columns and the ‘Join kind’, by choosing the appropriate icon.

With our tables merged, we can click the plus icon in the merged table, and select ‘Group by’:

In the dialog that pops-up, we select Sales Order in the ‘Group by’ section and call the new column Total Sales.  We select Sum as the Operation and  Sales Amount as the Column to be summed:

After pressing OK, we have a table of each Sales Order and the total corresponding Sales Amount:

Next time, we will look at the refresh options for datamarts.

Check back next week for more Power BI tips and tricks!