In this tutorial, you’ll learn about Power BI Values and how they are generated inside the Power Query Editor. These values have corresponding literals that can be used in codes and expressions to produce results and data. You’ll also learn how and where to apply the values to build a compelling report.
Power BI Values And Their Literals
A single piece of data produced by evaluating an expression is called a Value. There are two main groups when looking at data types and operators: Primitive Type Values and Structured Type Values. Primitive values are values that aren’t constructed out of other values. Structured values are values that are constructed out of both primitive and other structured values.
Each kind of value has an associated literal syntax used to construct values of that specific type. It also has a set of operators that can be used. The simplest form of an expression is a literal representing a value.
This is a table with some values and their corresponding literals:
You can view other examples inside the Power Query Editor. Create a new query by opening the query editor and going to the Home tab. Next, click New Source and select Blank.
Another way is to right-click inside the Query pane, click New Query, and select Blank Query.
Null Values
The first value that will be discussed is Null, which is written using the literal null. Null is used to represent the absence of a value or a value of an indeterminant or unknown state. When you write it in the formula bar, you’ll see that the icon beside the query name is a table with a question mark.
Logical value is used for (inaudible) operations and can be either true or false. It is written using the literals true and false. If you input true in the formula bar and press Enter, you’ll notice that the value is turned into a full uppercase text.
However, if you open the Advanced Editor window, you’ll see that true is in the lowercase. This is because Power Query is case-sensitive. It means that all other keywords in M need to be written in lowercase.
Remember that Power Query created the let expression. So if you create one single expression, you can remove everything in the window and just enter true. When you click Done, you’ll get the exact result.
Blank Values
Null and Blank are two different values. To demonstrate, create a new query and open the Advanced Editor window. Next, input this code.
After that, enable the Column profile from the View tab.
In the Column statistics below the preview pane, you can see that the sample column contains 7 unique values. However, you can only distinguish two values: blanks and null.
Change the grouping of the Value Distribution. Click the ellipsis of the table, click Group By, and select Text Length. The chart will then turn into a Text Length Distribution.
In the chart, you can see that it has one empty text string because you have one item with a text length of zero characters. It also has 4 strings with one character and another single string with two characters.
What appears to be a blank can in fact be a number of things. In most cases, it will probably just be an empty text string; it can also be a bunch of spaces or other non-principal characters.
The important thing is what’s in the next column. It illustrates that none of those blanks are actually equal to null because they all represent a value whereas null represents the absence of a value.
Number Values And Special Power BI Values
Another primitive value is the number value. If you create a new query and input a numeric value in the formula bar, the preview pane will show the value. The query name will have a number type of data.
The standard library also contains expressions that return numbers. For example, if you input =Number.PI in the formula bar, the preview pane will give you the value of Pi.
There are also special values that are considered to be a number value like positive and negative infinity.
Then there’s also the not a number value which is denoted by NaN. That value is obtained by dividing zero by zero.
Date and time-related functions can all be constructed using their own specific intrinsic function. If you input a date in the formula bar and open the Advanced Editor window, you’ll see that Power Query automatically adds that intrinsic function.
You’ll see #date followed by the year, month number, and day.
Text Values
Text value represents a sequence of unicode characters. It has a literal form that’s constructed using double quote signs. However, you don’t need to put the double quote signs when writing a text inside the formula bar.
If you open the Advanced Editor window, you’ll see that Power Query automatically adds the signs.
So when you write a text value inside the Advanced Editor window, you have to add the quote signs manually. But when you pass it as a single expression in the formula bar, Power Query adds the quote signs for you.
All the values that are tackled in this tutorial are primitive values, which means that they are not made up out of other values.
In this table, the Time, Date, DateTime, DateTimeZone, and Duration values have associated intrinsic functions that help create values of the same type.
Binary value represents a sequence of bytes. The #binary can be used to construct a binary value from a list of bytes. There are also other binary functions you can use like the Enter Data option. Power Query uses binary encoding to store manually created tables like this.
***** Related Links *****
Creating Values For Each Date Using Power Query Formula
Extract Values From Records And Lists In Power Query
3 Ways To Combine Values Using DAX Inside Power BI
Conclusion
Building data reports start with writing formulas that make use of the correct values and literals. Power BI values are one of the most essential things when writing codes and formulas. They bring out results by gathering data and performing expressions created in syntax.
Melissa
