Data types

Note

Microsoft Power Fx is the new name for the canvas apps formula language. These articles are work in progress as we extract the language from canvas apps, integrate it with other Microsoft Power Platform products, and make it available as open source. Start with the Microsoft Power Fx Overview for an introduction to the language.

Information flows through an app in small, discrete values, very much like the cells of a spreadsheet. For example, data in a Birthday field and an Anniversary field would both flow through as a Date value that includes the year, the month, and the day. The app knows how to format these values, constrain input to what is appropriate for each, and share the values with a database. Birthdays differ from anniversaries to people, but the system handles them in exactly the same manner. In this case, Date is an example of a data type.

This article provides details for the data types that canvas apps support. When an app connects to an external data source, each data type in that source is mapped to a data type for canvas apps.

Data type	Description	Examples
Boolean	A true or false value. Can be used directly in If, Filter and other functions without a comparison.	true
Color	A color specification, including an alpha channel.	Color.Red ColorValue( "#102030" ) RGBA( 255, 128, 0, 0.5 )
Currency	A currency value that's stored in a floating-point number. Currency values are the same as number values with currency-formatting options.	123 4.56
Date	A date without a time, in the time zone of the app's user.	Date( 2019, 5, 16 )
DateTime	A date with a time, in the time zone of the app's user.	DateTimeValue( "May 16, 2019 1:23:09 PM" )
Decimal	A number with high precision, base 10 operations, and limited range.	123 Decimal( "1.2345" )
GUID	A Globally Unique Identifier.	GUID() GUID( "123e4567-e89b-12d3-a456-426655440000" )
Hyperlink	A text string that holds a hyperlink.	"https://powerapps.microsoft.com"
Image	A Universal Resource Identifier (URI) text string to an image in .jpeg, .png, .svg, .gif, or other common web-image format.	MyImage added as an app resource "https://northwindtraders.com/logo.jpg" "appres://blobmanager/7b12ffa2..."
Media	A URI text string to a video or audio recording.	MyVideo added as an app resource "https://northwindtraders.com/intro.mp4" "appres://blobmanager/3ba411c..."
Number or Float	A number with standard precision, base 2 operations, and a wide range.	123 8.903e121 1.234e200
Choice	A choice from a set of options, backed by a number. This data type combines a localizable text label with a numeric value. The label appears in the app, and the numeric value is stored and used for comparisons.	ThisItem.OrderStatus
Record	A record of data values. This compound data type contains instances of other data types that are listed in this topic. More information: Working with tables.	{ Company: "Northwind Traders", Staff: 35, NonProfit: false }
Record reference	A reference to a record in a table. Such references are often used with polymorphic lookups. More information: Working with references.	First(Accounts).Owner
Table	A table of records. All of the records must have the same names for their fields with the same data types, and omitted fields are treated as blank. This compound data type contains instances of other data types that are listed in this topic. More information: Working with tables.	Table( { FirstName: "Sidney", LastName: "Higa" }, { FirstName: "Nancy", LastName: "Anderson" } )
Text	A Unicode text string.	"Hello, World"
Time	A time without a date, in the time zone of the app's user.	Time( 11, 23, 45 )
Two option	A choice from a set of two options, backed by a boolean value. This data type combines a localizable text label with a boolean value. The label appears in the app, and the boolean value is stored and used for comparisons.	ThisItem.Taxable
Untyped object	An object of an undeclared type. The underlying object could be any existing type, and can be converted into compatible types using functions such as Boolean(), Value(), Table() etc. For more information see Untyped object and Working with JSON.	ParseJSON("{ ""Field"" : 1234 }").Field

Many of these data types are similar and have the same underlying representation, such as a Hyperlink field being treated as Text. The additional data types provide better default experiences in forms and other controls.

Blank

All data types can have a value of blank (in other words, no value). The term "null" is often used in databases for this concept.

Use the Blank function with the Set or Patch function to set a variable or field to blank. For example, Set( x, Blank() ) removes any value in the global variable x.

Test for a blank value by using the IsBlank function. Replace possible blank values with non-blank values by using the Coalesce function.

Because all data types support blank, the Boolean and Two option data types effectively have three possible values.

Text, Hyperlink, Image, and Media

All four of these data types are based on a Unicode text string.

Embedded text

Embedded text strings in a formula are enclosed in double quotation marks. Use two double quotes together to represent a single double quote in the text string. For example, using the following formula in the OnSelect property of a Button control:

Notify( "Jane said ""Hello, World!""" )

results in a banner when the button is pressed, where the first and last double quotes are omitted (as they delimit the text string) and the repeated double quotes around Hello, World! are replaced with a single double quote:

Single quotation marks are used for identifier names that contain special characters and have no special significance within a text string.

String interpolation

Use string interpolation to embed formulas within a text string. This is often easier to work with and visualize the output than using the Concatenate function or & operator.

Prefix the text string with a dollar sign $ and enclose the formula to be embedded with curly braces { }. To include a curly brace in the text string, use repeated curly braces: {{ or }}. String interpolation can be used anywhere a standard text string can be used.

For example, consider this formula with global variables Apples set to 3 and Bananas set to 4:

$"We have {Apples} apples, {Bananas} bananas, yielding {Apples+Bananas} fruit total."

This formula returns the text string We have 3 apples, 4 bananas, yielding 7 fruit total. The variables Apples and Bananas are inserted in the text replacing the curly braces, along with the result of the mathematical formula Apples+Bananas. Spaces and other characters around the curly braces are preserved as they are.

Embedded formulas can include any functions or operators. All that is requires is that the result of the formula can be coerced to a text string. For example, this formula will insert NickName if it's supplied, or the FirstName if not, in a greeting:

$"Welcome {Coalesce( NickName, FirstName )}, it's great to meet you!" )

If NickName is set to "Joe", then this formula produces the text string Welcome Joe, it's great to meet you!. But if NickName is blank and FirstName is "Joseph", then this formula produces Dear Joseph, great to meet you! instead.

String interpolation can include standard text strings in the embedded formula. For example, if neither NickName nor FirstName were supplied, we could still provide "Friend" as a substitute:

$"Welcome {Coalesce( NickName, FirstName, "Friend" )}!"

String interpolations can even be nested. Consider this example where First, Middle, and Last names are combined into a greeting. Even if one or two of these values are blank, the correct number of spaces will be between the name parts. If none of the parts are provided, the inner string interpolation will collapse to an empty string, and be replaced by the Coalesce function by "Friend".

$"Welcome {Coalesce( Trim( $"{First} {Middle} {Last}"}), "Friend" )}!"

First	Middle	Last	Result
John	Qunicy	Doe	Welcome John Quincy Doe!
John	blank	Doe	Welcome John Doe!
blank	blank	Doe	Welcome Doe!
blank	blank	blank	Welcome Friend!

Newlines

Embedded text strings can contain newlines. For example, consider setting the Text property of a Label control to the following:

"Line 1
Line 2
Line 3"

The above formula results in three lines shown in the label control:

Newlines are also supported with string interpolation, as shown below:

$"Line {1}
Line {1+1}
Line {1+1+1}"

The above formula results in the same output:

Image and Media resources

Through the File menu, you can add image, video, and audio files as app resources. The name of the imported file becomes the resource name in the app. In this graphic, the Northwind Traders logo, which is named nwindlogo, has been added to an app:

To use this resource in an app, specify it in the Image property of an Image control:

URIs for images and other media

You can dig a little deeper into that last example by setting the Text property of a Label control to nwindlogo. The label shows a text string:

Canvas apps reference each image or other media file, whether it's in the cloud or added as an app resource, by a URI text string.

For example, the Image property of an image control accepts not only app resources but also links to images on the web, such as "https://northwindtraders.com/logo.jpg". The property also accepts inline images that use the data URI scheme, as in this example:

"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAkAAAAFAQMAAACtnVQoAAAABlBMVEUAAAB0J3UMNU6VAAAAAXRSTlMAQObYZgAAABRJREFUCNdjUGJgCGVg6GgAkkA2AA8/AffqCEBsAAAAAElFTkSuQmCC"

That URI displays a scaled-up version of two purple diamonds:

You can show the most recent image captured in a Camera control if you set the Image property of an image control to the Photo property of the camera control. The app holds the image in memory, and the Photo property of the camera control returns a URI reference to the image. For example, you might take a picture, and the camera's Photo property could return "appres://blobmanager/7b12ffa2ea4547e5b3812cb1c7b0a2a0/1".

You use a URI to reference an image or another media file stored in a database. That way, the app doesn't retrieve the actual data until it's actually needed. For example, an attachment in a Microsoft Dataverse table might return "appres://datasources/Contacts/table/..." As in the camera example, you can display this image by setting the Image property of an image control to this reference, which retrieves the binary data.

When you save a media data type, such as an image, to a database, the app sends the actual image or media data, not the URI reference.

Size limits

As text strings and URIs, these data types have no preset limit on their length.

The binary data that these data types reference also has no preset limit on size. For example, an image captured through the camera control that's now referenced as "appres://..." can be as large and high resolution as the device's camera can muster. The resolution, frame rate, and other attributes of media files aren't limited by the data type, but specific controls for playing and capturing media may have their own limitations.

However, all data sizes are subject to the amount of available memory in the app. Browsers running on a desktop computer typically support more than 100 megabytes of data. However, the amount of available memory on a device such as a phone might be far lower, typically in the range 30-70 megabytes. To determine whether your app will run within these limits, test common scenarios on all devices on which it should run.

As a best practice, hold data in memory only as long as necessary. Upload images to a database as soon as you can; download images only when the app's user requests them.

Numbers

Note

Power Apps only supports Float today and it is the type of all numbers. Decimal support will be added soon.

Power Fx supports two kinds of numbers: Decimal and Float (with synonyms Number and Currency).

Decimal is best for most business calculations. It can accurately represent numbers in base 10 meaning that 0.1 can be exactly represented and will not be prone to rounding errors during calculations. It has a large enough range for any business need, up to 10²⁸ with up to 28 digits of precision. Decimal is the default numeric data type for most Power Fx hosts, used if one simply writes 2*2.

Float is best for scientific calculations. It can represent numbers in a much larger range, up to 10³⁰⁸. Precision is limited to 15 decimal places and math is based on base 2 so it cannot represent some common decimal values precisely. Float also has higher performance and is favored if that is a factor and precision is not critical.

Decimal numbers

The Decimal data type most often uses the .NET decimal data type. Some hosts, such as Dataverse formula columns that are run in SQL Serer, use the SQL Server decimal data type.

Decimal does math the way you learned in school, using base 10 digits. That is very important to avoid rounding errors from very small differences that can accumulate when using base 2 math (as used by Float).

The range is from positive 79,228,162,514,264,337,593,543,950,335 to negative 79,228,162,514,264,337,593,543,950,335. The decimal separator can be placed anywhere within these numbers, providing up to 28 digits of precision, and still be precisely represented. For example, 79,228,162,514,264.337593543950335 can be exactly represented, as can 7.9228162514264337593543950335.

Floating point numbers

The Float data type, also known as Number or Currency, uses the IEEE 754 double-precision floating-point standard. This standard provides a very large range of numbers in which to work, from –1.79769 x 10³⁰⁸ to 1.79769 x 10³⁰⁸. The smallest value that can be represented is 5 x 10^–324.

Float can exactly represent whole numbers (or integers) between –9,007,199,254,740,991 (–(2⁵³ – 1)) and 9,007,199,254,740,991 (2⁵³ – 1), inclusive. This range is larger than the 32-bit (or 4-byte) integer data types that databases commonly use. However, canvas apps can't represent 64-bit (or 8-byte) integer data types. You might want to store the number in a text field or use a calculated column to make a copy of the number in a text field, so that it's mapped into a Text data type in the canvas app. In this manner, you can hold, display, and enter these values, as well as comparing them to determine whether they're equal; however, you can't perform numerical calculations on them in this form.

Floating-point arithmetic is approximate, so it can sometimes give unexpected results with many documented examples. You might expect the formula 55 / 100 * 100 to return exactly 55 and (55 / 100 * 100) - 55 to return exactly zero. However, the latter formula returns 7.1054 x 10^–15, which is very small but not zero. That tiny difference doesn't normally cause a problem, and the app rounds it away when showing the result. However, small differences can compound in subsequent calculations and appear to give the wrong answer.

Database systems often store currencies and perform calculations by using decimal math, which offers a smaller range but greater control over the precision. By default, canvas apps map currencies in and out of floating-point values; therefore, the result might differ from calculations that are done in a native decimal data type. If this type of discrepancy will cause problems, you might want to work with these values as Text, just as you might with large integers described earlier in this section.

Defaults and conversions

Note

Power Apps only supports Float today and it is the type of all numbers. Decimal support will be added soon.

Most Power Fx hosts use Decimal by default. This impacts:

• Literal numbers in formulas. The number 1.234 will be interpreted as a Decimal value. For example, the formula 1.234 * 2 will interpret the 1.234 and 2 as Decimal and return a Decimal result.
• Value function. Value( "1.234" ) will return a Decimal value. For example, the formula Value( "1.234" ) * 2 the Value function will interpret the contents of the text string "1.234" as a Decimal.

To work with Float values, the Float function is used. Extending the example above, Float( 1.234 ) will convert the Decimal 1.234 to Float. Float can also be used as a replacement for Value to convert a string containing a floating point number such as Float( "1.234" ) to a Float value, which is required if the number can't be represented as a Decimal.

In summary:

Usage	Decimal	Float
Literal numbers in formulas	1.234	Float( 1.234 ) Float( "1.234" )
Conversion from text string	Value( "1.234" ) Decimal( "1.234" )	Float( "1.234" )
Conversion between numeric types	Decimal( float )	Float( decimal )
Conversion to text string	Text( decimal )	Text( float )

Mixing numeric types

Float and Decimal values can be freely mixed. When mixed, Decimal values are converted to Float values due to the larger range. Since this can result in a loss of precision, it is important to not mix the two needlessly. Since Decimal is the default literal data type and most numeric functions will preserve the type, it is relatively easy to avoid moving to Float without desiring it.

For example, consider the following calculation using pac power-fx repl after installing the Power Platform CLI. Since both numbers are Decimal, the calculation is done in Decimal, and the result retains full precision:

>> 1.0000000000000000000000000001 * 2
2.0000000000000000000000000002

If instead, the second operand was changed to Float then the entire calculation would be done in Float and the tiny fractional part would be lost:

>> 1.0000000000000000000000000001 * Float(2)
2

Date, Time, and DateTime

Time zones

Date/time values fall in these categories:

• User local: These values are stored in UTC (Coordinated Universal Time), but the app user's time zone affects how the app shows these values and how the app user specifies them. As an example, the same moment appears differently to a user in Canada than it does to a user in Japan.
• Time zone independent: The app shows these values the same way and the app user specifies them the same way, regardless of time zone. The same moment appears the same way to a user in Canada as it does to a user in Japan. App authors who don't expect their apps to run in different time zones use these values because they're simpler overall.

This table shows some examples:

Date/time type	Value stored in the database	Value displayed and entered 7 hours west of UTC	Value displayed and entered 4 hours east of UTC
User local	Sunday, May 19, 2019 4:00 AM	Saturday, May 18, 2019 9:00 PM	Sunday, May 19, 2019 8:00 AM
Time zone independent	Sunday, May 19, 2019 4:00 AM	Sunday, May 19, 2019 4:00 AM	Sunday, May 19, 2019 4:00 AM

For User local date/times, canvas apps use the time zone of the browser or device, but model-driven apps use the user's setting in Dataverse. These settings typically match, but results will differ if these settings differ.

Use the DateAdd and TimeZoneInformation functions to convert local time to UTC and back again. See the examples at the end of the documentation for these functions.

Numeric equivalents

Canvas apps hold and calculate all date/time values, whether User local or Time zone independent in UTC. The app translates the values based on the app user's time zone when showing them and when the app user specifies them.

When a canvas app reads a Time zone independent value from a data source or writes such a value to a data source, the app automatically adjusts the value to compensate for the time zone of the app's user. The app then treats the value as a UTC value, consistent with all other date/time values in the app. Because of this compensation, the original Time zone independent value appears when the app adjusts the UTC value for the app user's time zone.

You can observe this behavior more closely by using the Value function to access the underlying numerical value for a date/time value. This function returns the date/time value as the number of milliseconds since January 1, 1970 00:00:00.000 UTC.

Because every date/time value is held in UTC, the formula Value( Date( 1970, 1, 1 ) ) won't return zero in most parts of the world because the Date function returns a date in UTC. For example, the formula would return 28,800,000 in a time zone that's offset from UTC by eight hours. That number reflects the number of milliseconds in eight hours.

Returning to our example from above:

Date/time type	Value stored in the database	Value displayed and entered 7 hours west of UTC	Value function returns
User local	Sunday, May 19, 2019 4:00 AM	Saturday, May 18, 2019 9:00 PM	1,558,238,400,000 (Sunday, May 19, 2019 4:00 AM UTC)
Time zone independent	Sunday, May 19, 2019 4:00 AM	Sunday, May 19, 2019 4:00 AM	1,558,263,600,000 (Sunday, May 19, 2019 11:00 AM UTC)

Converting Unix times

Unix times reflect the number of seconds since January 1, 1970 00:00:00 UTC. Because canvas apps use milliseconds instead of seconds, you can convert between the two by multiplying or dividing by 1,000.

For example, Unix time shows September 9, 2001, at 01:46:40 UTC as 1,000,000,000. To show that date/time value in a canvas app, multiply that number by 1,000 to convert it to milliseconds, and then use it in a Text function. The formula Text( 1000000000 * 1000, DateTimeFormat.UTC ) returns the string 2001-09-09T01:46:40.000Z.

However, that function returns Saturday, September 8, 2001 18:46:40 if you use the DateTimeFormat.LongDateTime24 format in a time zone that's -7 hours offset from UTC (7 hours west of UTC). This result shows the DateTime value correctly based on the local time zone.

To convert to a Unix time, divide the result from Value by 1,000:
RoundDown( Value( UnixTime ) / 1000, 0 )

If you need the Unix time in a Date value for further calculations or display within Power Apps, use this formula:
DateAdd( Date( 1970,1,1 ), UnixTime, Seconds )

SQL Server

SQL Server has Datetime, Datetime2, and other date/time data types that don't include a time-zone offset and don't indicate which time zone they're in. Canvas apps assume these values are stored in UTC and treat them as User local. If the values are meant to be time-zone independent, correct for the UTC translations by using the TimeZoneOffset function.

Canvas apps use the included time-zone information in Datetimeoffset fields when converting a value to the app's internal UTC representation. The apps always use UTC as the time zone (zero time zone offset) when they write data.

Canvas apps read and write values of the Time data type in SQL Server as text strings in the ISO 8601 duration format. For example, you must parse this string format and use the Time function to convert the text string "PT2H1M39S" to a Time value:

With(
    Match( "PT2H1M39S", "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?" ),
    Time( Value( hours ), Value( minutes ), Value( seconds ) )
)
// Result: 2:01 AM (as shown in a label control, use the Text function to see the seconds)

Mixing date and time information

Date, Time, and DateTime have different names, but they all hold the same information about dates and times.

A Date value can include time information with it, which is usually midnight. A Time value can carry date information, which is usually January 1, 1970. Dataverse also stores time information with a Date Only field but shows only the date information by default. Similarly, canvas apps sometimes distinguish between these data types to determine default formats and controls.

Adding and subtracting date and time values directly isn't recommended because time-zone and other conversions could cause confusing results. Either use the Value function to convert date/time values to milliseconds first and take into account the app user's time zone, or use the DateAdd and DateDiff functions to add or subtract from one of these values.

Choices and Yes/No

Choices and two-option data types provide a two or more choices for an app user to select. For example, an Order Status choice might offer the choices New, Shipped, Invoiced, and Closed. The two-option data type offers only two choices.

Both of these data types show their labels in a text-string context. For example, a label control shows one of the order-status options if the control's Text property is set to a formula that references that choice. Option labels might be localized for app users in different locations.

When an app user selects an option and saves that change, the app transmits the data to the database, which stores that data in a representation that's independent of language. An option in a choice is transmitted and stored as a number, and an option in a two-option data type is transmitted and stored as a boolean value.

The labels are for display purposes only. You can't perform direct comparisons with the labels because they're specific to a language. Instead, each choice has an enumeration that works with the underlying number or boolean value. For example, you can't use this formula:

If( ThisItem.OrderStatus = "Active", ...

But you can use this formula:

If( ThisItem.OrderStatus = OrderStatus.Active, ...

For global choices (which tables share), the name of the option-set enumeration matches the name of the global choice. For local choices (which are scoped to a table), the name might contain the name of the table. This behavior avoids conflicts if multiple tables have choices that have the same name. For example, the Accounts table might have an OrderStatus choice, and its name might be OrderStatus (Accounts). That name contains one or more spaces and parentheses, so you must surround it with single quotation marks if you reference it in a formula.

In addition, two-option values can also behave as boolean values. For example, a two-option value named TaxStatus might have the labels Taxable and Non-Taxable, which correspond to true and false respectively. To demonstrate, you can use this formula:

If( ThisItem.Taxable = TaxStatus.Taxable, ...

You can also use this equivalent formula:

If( ThisItem.Taxable, ...