Power Query - Example

Accessing 2ic Pallets Data via Power Query link arrow_upward

Working with 2ic Pallets data in power query requires authentication. If a user is unable to perform an action in the web app, they will also be unable to perform that action via power query with the same login.

2ic Software provides two options for power query integration:

  1. Excel
  2. Power BI

Both of these systems use the same Power Query editor.

Getting Started with Excel link arrow_upward

  1. Download the 2ic Excel Example Sheet for Excel:

    get_app 2ic Excel Example Sheet 2ic-power-query-excel-example.zip

  2. In the zipped folder, right-click to extract all.

  3. Open 2ic-power-query-excel-example.xlsx, then click Enable Content.
    Enable content excel

  4. Select Data tab from the headers, then select Queries & Connections. This will open the Queries & Connections side panel, double-click Transactions.
    Excel getting started

  5. This will open the Power Query Editor. This is the main location to edit queries and setup authentication details.

  6. Continue to steps using power query.

Getting Started with Power BI link arrow_upward

  1. Download the 2ic Power BI Example File:

    get_app 2ic Power BI Example 2ic-power-query-power-bi-example.zip

  2. In the zipped folder, right-click to extract all.

  3. Open 2ic-power-bi-example.pbix, then click data on the right-side panel. Right-click transactions data source and click edit query.
    Power BI getting started

  4. This will open the Power Query Editor. This is the main location to edit queries and setup authentication details.

  5. Continue to steps using power query.

Using Power Query link arrow_upward

Setting Authorisation link arrow_upward

  1. Open the web app https://app.2icsoftware.com/ and sign in. Click your name in the top-left corner of the page, and open My Details. Under API Access, copy the Access token.
    My details authentication
  2. From the Power Query Editor left side panel, open Access_Token. Paste the copied access token into Current Value.
    Power Query nav
  3. Return to My Details, under API Access, copy the Database ID.
  4. Return to Power Query Editor, then from the left side panel, open DatabaseId. Paste the copied database ID into Current Value.
  5. Click Edit Credentials. Select Anonymous, then Connect.
    Power Query nav
  6. Data will appear in the preview window. This data contains all transaction columns.
  7. Click Close & Load. Otherwise, to customise the query further, continue to steps customising query.

Customising Query link arrow_upward

Altering Columns link arrow_upward

  1. In the Power Query Editor, double-click Transactions, then select Choose Columns. This is a complete list of the columns that the 2ic Pallets API returns for each transaction. Columns that are ticked here will be displayed in the final Excel sheet.
    Excel column select
  2. To revert those changes, click the X on "Removed Other Columns".
    Excel revert columns

Altering Displayed Row Count link arrow_upward

  1. In the Power Query Editor, from the left side panel, select Row Count. Edit the row count in Current Value. This is the maximum rows that will be displayed.

    Row Count

Altering Filtering and Sorting link arrow_upward

  1. Open the web app https://app.2icsoftware.com/ and sign in. Open the Transactions page and set any filtering or sorting on the grid. This will be reflected in the Excel query.
    Excel transaction filters
  2. Press F12 on your keyboard to open Developer Tools. Select the Network tab.
  3. In the search box, type in transactions and select Fetch/XHR.
    Fetch network
  4. Clear the network tab with the button above the search box, then use the blue refresh button in the 2ic grid to send a new query.
    Clear network
  5. Click the latest transaction query, then click Payload tab. Ensure view parsed is displayed. Copy all of the text from the request payload window.
info

You may need to click show more to display the entire query.

View parsed

  1. Return to the Power Query Editor window and open the Transaction Query Input section, then paste into the input box.
    Query input

  2. Refresh to see that the query has been altered to use the new filters.

Altering Recent Date Filtering link arrow_upward

  1. In Power Query Editor, from the left side panel, select Recent Days. Edit the recent days in Current Value. This displays how many days (previous to today) to sort by.

    Recent days

info

Defining this value will overwrite any sending date filter in the Query Input.


Additional Information link arrow_upward

If any query is not working, explore errors by double-clicking ExpandedError on the right-side panel of the Applied Steps section in Power Query Editor.

View parsed

Last updated on 13/05/2025