Explore Invoices

This example uses several visuals in Power BI to display sales and purchase invoices in a similar way to the Invoice Explorer in the MineMarket Client.

The data comes from the Invoice endpoint in the Marketing OData feed.

Note: The PBIT file available for this report includes the Invoice Details report because the two reports share data filtering.

Screenshot of the report to explore invoices

Query Editing

Note: If all invoices are only for a single despatch order each, or for despatch orders for a single contract product each, most of this query editing is not required. Required columns can be expanded within a single query.

Some manipulation of the data is required if any invoices are for multiple despatch orders because Power BI initially displays such data as individual rows.

By combining multiple copies of the data from the Invoice endpoint, the report can display each invoice as a single line in the results table with comma-separated values for the contract names and products. The filter for contract products still lists individual products rather than all possible combinations that exist in the data.

Expand the linked data for the main query to include organisation and company names

  1. On the Home ribbon of the Query Editor, select Get Data or Recent Sources and load an instance of the Invoice endpoint's data.
  2. Rename the query "Invoice_Main".
  3. Expand the linked Organisations record, but only select the OrganisationName column.
  4. Rename the column "Organisation".
  5. Expand the linked Contracts table, but only select the Company column.

    The query now has one row for every combination of invoice ID and contract.

  6. Expand the Company record, but only select the OrganisationName column.
  7. Rename the column "Company".
  8. Select the Id column.
  9. On the Home ribbon, select Remove rows > Remove Duplicates.
  10. Rename other columns as required; for example, "Invoice Number".

Create a query with comma-separated values for invoices with multiple contract names

  1. On the Home ribbon, select Get Data or Recent Sources and load a new instance of the Invoice endpoint's data.
  2. Rename the query "Invoice_List_Contracts".
  3. Expand the linked Contracts table, but only select the ContractName column.

    The query now has one row for every combination of invoice ID and contract name.

  4. Remove the extra columns:
    1. Press and hold Ctrl while clicking the Id and ContractName columns.
    2. Right-click and select Remove Other Columns from the menu.
  5. Remove any duplicate rows:
    1. On the Add Column ribbon, select Custom Column.
    2. In the Custom Column dialog box, enter the following settings and click OK.
      • New column name: Key (This is a temporary column to store the concatenated data.)
      • Custom column formula:
        =[Id] & [Contracts.ContractName]
    3. Select the Key column.
    4. On the Home ribbon, select Remove rows > Remove Duplicates.
  6. Manipulate the data so that there is only one row per invoice:
    1. On the Transform ribbon, select Group By.
    2. In the Group By dialogue box, enter the following settings and click OK.
      • Group by: Id
      • New column name: MyTable (This is a temporary column.)
      • Operation: All Rows
      • Column: Leave blank.

        The contract names are now within a table.

  7. Manipulate the data so that the contract names are within a list:
    1. On the Add Column ribbon, select Custom Column.
    2. In the Custom Column dialog box, enter the following settings and click OK.
      • New column name: Contracts
      • Custom column formula:
        =Table.Column([MyTable], "Contracts.ContractName")
  8. Expand the contract names to a comma-separated list:
    1. Click the Expand icon on the Contract column and select Extract Values from the menu.
    2. In the Extract values from list dialog box, select Comma as the delimiter and click OK.
  9. Remove the MyTable column.

Create a query with comma-separated values for invoices with multiple products

Note: The following steps are analogous to the steps for the query above for contract names.

  1. On the Home ribbon, select Get Data or Recent Sources and load a new instance of the Invoice endpoint's data.
  2. Rename the query "Invoice_List_Products".
  3. Expand the linked DespatchOrders table, but only select the ContractProductName column.

    The query now has one row for every combination of invoice ID and product name.

  4. Remove the extra columns:
    1. Press and hold Ctrl while clicking the Id and ContractProductName columns.
    2. Right-click and select Remove Other Columns from the menu.
  5. Remove any duplicate rows:
    1. On the Add Column ribbon, select Custom Column.
    2. In the Custom Column dialog box, enter the following settings and click OK.
      • New column name: Key (This is a temporary column to store the concatenated data.)
      • Custom column formula:
        =[Id] & [DespatchOrders.ContractProductName]
    3. Select the Key column.
    4. On the Home ribbon, select Remove rows > Remove Duplicates.

      Note: Because the first 5 steps are identical for the data needed for the Products filter, consider duplicating the query after this step.

  6. Manipulate the data so that there is only one row per invoice:
    1. On the Transform ribbon, select Group By.
    2. In the Group By dialogue box, enter the following settings and click OK.
      • Group by: Id
      • New column name: MyTable (This is a temporary column.)
      • Operation: All Rows
      • Column: Leave blank.

        The product names are now within a table.

  7. Manipulate the data so that the products are within a list:
    1. On the Add Column ribbon, select Custom Column.
    2. In the Custom Column dialog box, enter the following settings and click OK.
      • New column name: Products
      • Custom column formula:
        =Table.Column([MyTable], "DespatchOrders.ContractProductName")
  8. Expand the products to a comma-separated list:
    1. Click the Expand icon on the Contract Product column and select Extract Values from the menu.
    2. In the Extract values from list dialog box, select Comma as the delimiter and click OK.
  9. Remove the MyTable column.

Create a query with individual products for the filter

  1. Repeat the first 5 steps (up to and including removing duplicates) of the activity to create a query with comma-separated values for invoices with multiple products.
  2. Rename this query "Invoice_Filter_Products".
  3. Remove the Key column.
  4. Rename the column "Product".

Check table relationships

  1. On the Home ribbon (of Power BI's main interface, not the Query Editor), select Manage Relationships.
  2. Check that the following relationships are active:
    • From Invoice_Filter_Products (Id) to Invoice_Main (Id)
    • From Invoice_List_Contracts (Id) to Invoice_Main (Id)
    • From Invoice_List_Products (Id) to Invoice_Main (Id)
  3. For each relationship:
    1. Click Edit.
    2. Check that the Cardinality is Many to one (*:1) for Invoice_Filter_Products and One to one (1:1) for the other relationships.
    3. Check that the Cross filter direction is Both.
    4. Click OK.

Report Level Filter

Screenshot of the filter for the document type in the report

The report level filter limits data on all report pages to invoices with the Document Type of Sales or Purchase. (Because freight and service contracts are not exposed in the MineMarket Information Services, other invoices do not display with correct contractual data in Power BI.)

Invoice Date

Screenshot of the slicer visual for the invoice date

This slicer visual displays the Invoice Date field from the Invoice_Main query.

The default slicer type for dates is Between, which displays a start and end date and a slider. This report uses a Relative slicer type to limit the invoices to this month.

To change the slicer type, ensure the slicer header is enabled (under the visual's formatting settings), and click the downwards arrow in the top-right corner of the visual.

Other Filters

Screenshot of configuration options for a dropdown slicer visual

The other filters are also slicer visuals, but set to the Dropdown slicer type. In the Format settings for each visual, the Selection Controls have been modified to show "Select All" and allow multiple selections.

The Organisations, Companies, Invoice Types, Invoice States and Currencies filters display the respective fields from the Invoice_Main query.

The Products filter displays the products from the Invoice_Filter_Products query.

Slicer synchronisation

Screenshot of configuration options to synchronise across report pages

In order for the filtering of data on the Invoice Explorer to affect the Invoice Details page, synchronise each slicer:

  1. On the View ribbon, select Sync slicers.
  2. For each slicer, on the Sync slicers panel, select that the slicer is synchronised on all pages, but only displayed on the Invoice Explorer page.

Note: The synchronisation of slicers across pages was introduced in the February 2018 release of Power BI.

Invoices

Screenshot of the available columns for the report to explore invoices

The invoices display in a table visual. Most of the columns are from the Invoice_Main query; however, the Contracts and Products columns are from the separate queries with comma-separated values.

To modify the date format:

  1. Click the Data icon on the left of the Power BI user interface.
  2. Select the Due Date column in the Invoice_Main table.
  3. On the Modeling ribbon, select the preferred Format for the date.

Important: No currency conversions are handled in Power BI. Therefore, the Total row only displays meaningful data if all displayed invoices are in the same currency.