Getting Started with Power BI: Object basics

As you are learning to use Power BI, it is a good idea to build a simple example data sets to see how the various functions work. The easiest way to create simple objects from scratch is to use #-functions. Here are some examples to get you started:

Elementary Types and Transformations

Perhaps it goes without saying, but Power BI interprets anything in quotes as text, numbers not in quotes as numbers, and the words true and false as Booleans.

thisIsText = "yep, sure is."
thisIsANumber = 5
thisIsABoolean = false

Date/DateTime/DateTimeZone

#date(2014,7,31) //year, month, day
#datetime(2011,12,31,16,37,48) //year, month, day, hour, minute, second
#datetimezone(2016,1,5,8,34,16,-8,0) //year, month, day, hour, minute, second, offset hour, offset minute

which will create:

ObjectBasics003

Duration

#duration(6, 4, 3, 1) //day, hour, minute, second

which will create:

ObjectBasics004

List: {}

A list is simply an ordered collection of objects. We use curly braces to denote lists.

someList = {1, 2, "lists can have", "different types", "inside them", true}

which will create:

ObjectBasics002

Any kind of object can be in a list, including other lists, records, or even tables! You can access items in a list at a certain spot using curly braces (notice that it is 0-indexed: the first item is grabbed by asking for item 0):

fourthValue = someList{3}

ObjectBasics006.png

Record: []

Records are collections of fields, and each field can be assigned a value. We use brackets to denote records.

someRecord=[FieldOne = "something", secondField = 26, z = {1, 2, 4, "tangerine"}]

which will create:

ObjectBasics005

Field names must be text, but values can be just about anything-text, numbers, lists, records, tables, etc. You can access the value of any field in a record by using brackets:

someRecord[secondField]

ObjectBasics007

Table

Tables are very important because eventually, in our data model, everything is going to end up as a table. Here’s how to create a simple one from scratch:

someTable = #table({"Column1Name", "Column2Name"}, {{"Row1Col1", "Row1Col2"}, {"Row2Col1", "Row2Col2"}})

which will create:

ObjectBasics001

Each row in the table is essentially a record whose fields are the column names. A table is just an ordered list of those record-rows.

In fact, Power BI treats tables exactly this way. You can grab a certain row of a table exactly the same way you would grab a certain element of a list by using curly braces (again, 0-indexed):

secondRow = someTable{1}

ObjectBasics008

The result is a record with fields corresponding to column names and values corresponding to the table values.

You can also grab a certain column of a table by referencing the column name as though it is a field of a record with brackets:

firstCol = someTable[Column1Name]

ObjectBasics009

The result is a list.

 

~~~~~~~~~~~

There are a handful of other more obscure data types as well. These, and detailed descriptions of the functions that are available for manipulating these data types, can be found at Microsoft’s documentation site.

Leave a Reply

Your email address will not be published.