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:
Duration
#duration(6, 4, 3, 1) //day, hour, minute, second
which will create:
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:
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}
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:
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]
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:
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}
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]
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.
