In this article, learn how to extend your reporting with OData for Excel. To know more about setting up OData, see the following:

Enabling OData  

Creating an OData Connection 

Using OData with Excel 

OData with Excel Troubleshooting 

Also, see the following videos:

OData Connector setup and User Profile setup (23.1) - AIQ Academy

Generate OData User Credentials (23.2) - AIQ Academy

Use OData Connector with PowerBI (23.3) - AIQ Academy 

Using OData connector with Excel (23.4) - AIQ Academy

Query: 

The Profit and Loss displays Sales as Negatives (Credits) and Expenses as Positive (Debits). I want Sales to appear as Positive and Expense Values as Negative instead.

Solution: 

1. In the Data tab go to Get Data > Launch Power Query Editor.
   
2. In the Queries panel, select the P&L Report Table.
3. Select the column you need to transform.
4. Go to the Transform tab and select Standard > Multiply. Enter the value ‘-1’ and click OK.
   

Alternative Solution: 

1. In the Data tab go to Get Data > Launch Power Query Editor.
   
2. In the Queries panel, select the P&L Report Table.
3. Go to the Add Column tab and select Custom Column.
4. In the Custom Column screen, name the new column. In the Power DAX formula box, select the relevant Period Movement columns and enter the following formula: [Period_Movement]* -1.Click OK. The new columns will now appear in the table.
   
   

Query: 

I want sub-totals broken down by GL Category and Sub-Category in my Pivot Table Report.

Solution: 

1. In the PivotTable Fields panel, select GL Category and GL Sub-Category as Rows. 
2. In the Pivot Table, right-click on any GL Category or Sub-Category field and select Sub-Total “GL Category” or Sub-Total “GL Sub Category”.
   
   

Query: 

I would like to add the Variance between Actual VS Budget into my Reports as a new column.

Solution: 

1. In the Data tab go to Get Data > Launch Power Query Editor.
   
2. In the Queries panel, select the relevant table.
3. Go to the Add Column tab and select Custom Column.
4. In the Custom Column screen, name the new column, enter the DAX formula and select the relevant Available Columns. The example given is for Monthly Variance Vs Budget: [Period_Movement]-[Budget]. For YTD Variance simply select the relevant fields in Available Columns. Click OK.
   
   

Query: 

I want to review by Quarterly Movement instead of simply by Period.

Solution: 

1. In the Data tab go to Get Data > Launch Power Query Editor.
   
2. In the Queries panel, select the relevant table.
3.  Go to the Add Column tab and select Custom Column.
4. In the Custom Column screen, name the new column, enter the DAX formula, “Q” & Text.From(Number.RoundUp([Period_Number]/3)), and select the relevant Available Columns. (The example name, ‘Q’ can be changed to, for example, ‘Quarter’ ). Click OK.
   
   

Query: 

I want to add Ageing Brackets/Buckets to my Aged Reports.

Solution: 

1. In the Data tab go to Get Data > Launch Power Query Editor.
    
   
2. In the Queries panel, select the Aged Report.
3. Select the Period Date table column by clicking in its header. Right-click in the column and select Duplicate Column. In the duplicated column, double-click in the column header and rename it to ‘Age’.
4. Go to the Transform tab.
5.  Select the Age column, go to Date and select Age.
6. Click on the Type icon in the Age column header and change to Whole Number.

With the Age Column, we can now build our Ageing Brackets. 

1. Go to the Add Column tab and select Custom Column.
2. In the Custom Column screen, name the new column. 
3. Enter the DAX formula. In the example below the Age Column has been renamed AgingIndays: 
   = if [AgingIndays] <= 30 then "30 Days" else if [AgingIndays] <= 60 then "60 Days" else if [AgingIndays] <= 90 then "90 Days" else "90 + Days" 
4. Select the relevant Available Columns. 
5. Click OK.

Query: 

I want to see a full transactions listing for the financial year/several periods at once, however the Transaction Lines Listing Report only allows one period at a time to be selected.

Solution:

1. In the Data tab go to Get Data > Launch Power Query Editor. 
    
2. In the Queries panel, select the Transactions Line Listing dataset, right-click and select Duplicate. Right-click on the duplicated table and rename it according to the period you need. NOTE: It is best to add all additional columns to the table prior to duplicating and appending so these new columns are also copied across on duplication. 
   
   
    
3. Click in the formula bar at the top of the table. Amend the period in the formula and hit the enter key. The table will auto-refresh to this new period.
4. Repeat the steps above to get 12 periods of transactions lines.
5. Going forward use the append option to bring all 12 tables into one table. In the Home tab of the Power Query Editor, go to Append Queries > Append Queries as New. 
   
   
    
6. Add all 12 periods to Tables to append and click OK. This new 12 period table can be linked to other tables/reports.
   
   

Query: 

I want to join different tables to add additional columns and filters to my existing standard reports.

Solution:

To join tables there must be one unique value in one table that can join to the duplicate value in other tables. As an example, we will join the GL Listing table to the Profit and Loss Report. Joining is always a one-to-many relationship. Power Query does not allow many-to-many e.g. joining two Profit and Loss tables directly. Instead, both can be joined indirectly via the GL Listing Report instead.

1. Load the tables as Queries into OData.
2. In the Power Pivot tab, select the Manage option to open the Power Pivot for Excel screen. 
   
   
    
3. In this screen, the Data view displays all the separate tables as worksheets. While you can create relationships from here, for a more user-friendly option, click Diagram View. 
   
   
    
4. In this example we selected the Code field in the General Ledger Accounts table. We then created a link between the tables by clicking and dragging from the selected field to the GL Code fields in the Profit and Loss tables. 
   
   Now, when running the Pivot Table, these three reports can run concurrently. We can select GL Codes etc. from the General Ledger Accounts table and value fields like Period Amount or YTD/Budget from the P&L Reports. Likewise, we could add the GL Group to the Profit and Loss Reports, something not possible in standard reports.