Multidimensional Modeling with PQ – Functions

Table of Contents

• Project Planning – Project Introduction and Project Plan
• Permutations – Merging Queries to Create All Permutations for Calculations
• PQ Calcs – Calculate values in PQ
• PQ Functions – Creating Functions
• Auto-Extend Entities – Creating Auto-Extending Excel Tables for Entities
• Auto-Extend Associations – Creating Auto-Extending Excel Tables for Associations

Introduction

I like reusable functions that I can copy into a project and use without much thought. Functions take a parameter, perform some process using the parameter, then return a value. The nice thing about functions is once created, we don’t have to think about how they work. We just use them. I have a library of functions from which we will use two: fncGetNameValue() and fncCrtTblRows().

Terms and Abbreviations

• Dimension – Entity types such as periods, products, market sectors, etc.
• Item – A specific entity in a dimension such as January (item) in periods (dimension).
• Entity Table – An Excel table containing one dimension’s items.
• Association Table – An Excel table linking items in two or more dimensions such as distribution percentage of products (dimension #1) by regions (dimension #2)
• FM – Financial Modeling which includes traditional financial modeling methodologies.
• TBM – Table Based Modeling – uses tables and structured references exclusively
• MD – Multidimensional Modeling – uses multiple entities to determine a single result
• PQ – Power Query

Learnings

In this section we will learn about:

1. Using the PQ Editor
2. Creating a blank query
3. Using the Advanced Editor
4. Creating Custom Functions
5. A few PQ Functions:?
6. PQ Operators

Process Overview

Below is the basic process we will follow for each function. We will discuss each step in more detail.

1. Create a blank query?
2. Rename it
3. Open it in Advanced Editor
4. Copy/paste code from my ‘PQ Snippet Library’
5. Test

Materials

To follow along with this discussion, use Test.xlsx created in Permutations:

Preparations

1. Open Test.xlsx.
2. Launch PQ Editor using keyboard shortcut ALT-A PN L

fncGetNameValue(<NameObject>)

The first function we will create returns the value from a named formula contained in the current workbook. You may be more familiar with the term Named Range. All named ranges are named formulas, but not all named formulas are named ranges. Regardless of exactly what the name is, this will get the first value from it. I use this often to get parameters from Excel for PQ queries that retrieve selected records from financial systems. But in this case, we will use it to tell our models tables how many rows they need based on client entries into named ranges.

The first step in creating a function is to start with a blank query.

Create a Blank Query

If the PQ Editor s not already open, use the keyboard shortcut Alt-A PN L to launch it.

Once inside the PQ Editor find the Queries pane on the left then right click inside that pane and use pop-up menu option New Query > Other Source > Blank Query. This creates Query1 in the Queries pane.

Rename it

Right click on Query1 and select Rename. Change the name to fncGetNameValue.

Open it in Advanced Editor

Click on fncGetNameValue and then, from PQ’s ribbon, take menu option Home > Advanced Editor.

Copy/paste code from my ‘PQ Snippet Library’

The code is available in PQ Snippets.txt (click to download). Open it and copy/paste the first snippet into Advanced Editor. It should look like this:

When finished, click Done.

Test

Click on the new function in the Queries pane then enter Sectors in the NameObject box.

Click Invoke. 4 should appear. When you are through admiring your work, delete Invoked Function.

Code Explanation

NOTE! We do not need to know how this function works to use it. Skip this if you don’t care, or already know how it works.

NOTE! PQ is case sensitive so when we get errors, the first thing I check is capitalization and spelling.

/*?*/?

This is how PQ designates comments. Comments are optional.?

(NameObject as text) =>

A Power Query function maps a set of input values to a single output value. In this function we have just one input value (NameObject) that we explicitly declare contains only text values. The body of the function begins with let and follows the goes-to (=>) symbol.

WrkBook = Excel.CurrentWorkbook(),

Excel.CurrentWorkbook() is a PQ function that returns a table containing the contents of the current WrkBook. Contents includes names, tables, and worksheets. The table has two columns: Content and Name. Content is of type ‘Table’ and Name is of type ‘Text’.?This statement places the entire table in variable WrkBook.

NOTE! All but the last statement (the statement just prior to in) must end with a comma. The last statement must not end in a comma.

NameObj = WrkBook{[Name=NameObject]},

Curly brackets are PQ’s Item Access operator and square brackets are PQ’s Field Selector operator. When square brackets are placed inside curly brackets, they twll PQ to select the records (from WrkBook) where the field (Name) equals a value (NameObject). NameObject is our function’s parameter.?

NOTE! Even though it returns just one record, PQ sees it as a table.

NameCnt = NameObj[Content],

The record has two columns: Content and Name. This statement gets the Content column from the record in NameObj and places it in variable NameCnt.

NOTE! NameObj[Content] is a Table.?By placing the table into NameCnt PQ opened (or “expanded”) the table. Excel’s named formulas are not tables, so PQ places its values in a table and names the first column, Column1.

NameVal = NameCnt{0}[Column1]

Remember that curly brackets are PQ’s item access operator. When we place a number inside curly brackets we are telling PQ to select an item by position. Position is a zero based counter so the first item is in position zero. In this statement, the items are records from NameCnt. Thus, NameCnt{0} returns the first record. The square brackets select only the column Thus, NameCnt{0}[Column1] selects the first record in NameCnt from Column1.

NOTE! This is the last statement so no comma!

in

??NameVal

This designates the variable NameVal as what our function returns.

fncCrtTblRows (<RowCount>, <ColumnName>)

This function creates a one column table with as many rows as designated.?

Use the same procedure as before to create a blank query, rename it fncCrtTblRows, and open it in Advanced Editor.

Copy/paste code from my ‘PQ Snippet Library’

The code is available in PQ Snippets.txt (downloaded earlier). Open it and copy/paste the second snippet into Advanced Editor. It should look like this:

Test Function

Click on the new function in the Queries pane then enter 4 in the RowCount box and ID in the ColumnName box. Then click Invoke button.

We should see a table with four rows and one column labeled ID.

When you are through admiring your work, delete Invoked Function.

Code Explanation

NOTE! We do not need to know how this function works to use it. Skip this if you don’t care, or already know how it works.

(RowCount as number, ColumnName as text) =>

Our function requires two parameters: RowCount and ColumnName.

?CrtLst?= List.Generate(()=>1, each _ <= RowCount, each _ +1),

This uses the PQ function List.Generate to create a list from 1 to RowCount.?

Lst2Tbl = Table.FromList(CrtLst, Splitter.SplitByNothing(), {ColumnName})

This uses the PQ function Table.FromList to convert our list into a table. It also names the first (and only) column ColumnName. Now I’ll be honest, I’m not entirely sure why we need Splitter.SplitByNothing() but I know this doesn’t work without it.

in

??Lst2Tbl

This designates the variable Lst2Tbl as what our function returns.

Conclusion

We created two functions which we can save to NotePad and copy into any PQ project needing their functionality.?We will see how to use them in the next section: Auto-Extend Entities.