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 Authentication/Standard/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 Authentication/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.
info

If you are set up with machine to machine permissions, you can navigate to Authentication/Machine_To_Machine and enter your Client_Secret and Client_ID, this will ensure you dont need to replace the Access Token every 24 hours.

info

If you still receive unauthorised errors, ensure that Authentication/Should_Use_Machine_To_Machine is set correctly.

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 Transactions/Filter_Options/Max_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 Transactons/Transactions_Config/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 Date Filtering link arrow_upward

  1. In Power Query Editor, from the left side panel, select Transactions/Filter_Options/Date_Filter_Selector. This can be toggled between Recent_Days, Date_Range and None to select which date filtering you would like to use.

  2. When using Recent_Days Select Transactions/Filter_Options/Recent_Days. Edit the recent days in Current Value. This displays how many days (previous to today) to filter by.

  3. When using Date_Range Select Transactions/Filter_Options/Start_Date and Transactions/Filter_Options/End_Date. Edit the date value in Current Value. This configures an inclusive date range to filter by.

info

Setting these values will overwrite any sending date filter in the Query Input.


Additional Information link arrow_upward

If a query is not working, explore errors by navigating to the related Errors query in the left-side panel.

View parsed

Last updated on 7/7/2025