Data Types - Power Platform (2023)

  • Article
  • 17 minutes to read

To use

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

Information flows through an application in small, discrete values, like spreadsheet cells. For example, data in aBirthdayfield and aBirthdaywould flow across a fieldDataA value that includes the year, month, and day. The application knows how to format these values, restrict input to what is appropriate for each, and share the values ‚Äč‚Äčwith a database. Birthdays differ from people's birthdays, but the system treats them exactly the same. In this case,Datais an example ofdata type.

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

data typeDescriptionexamples
boleanoATRUEoFALSEvalue. Can be used directly onE,Filterand other functions without comparison.TRUE
CorA color specification, including an alpha channel.Red color
ValorColor("#102030" )
RGBA (255, 128, 0, 0,5)
To divideA currency value stored in a floating point number. Currency values ‚Äč‚Äčare the same as numeric values ‚Äč‚Äčwith currency format options.123
4,56
DataA date without time, in the app user's time zone.Data (2019, 5, 16)
Date and timeA date and time, in the app user's time zone.DateTimeValue("May 16, 2019 13:23:09." )
GUIDAGlobal unique identifier.GUID()
GUID("123e4567-e89b-12d3-a456-426655440000" )
hyperlinkA text string containing a hyperlink."https://powerapps.microsoft.com"
ImageAUniversal Resource Identifier (URI)text string to an image in .jpeg, .png, .svg, .gif or other common web image format.My imageadded as an app feature
"https://northwindtraders.com/logo.jpg"
"appres://blobmanager/7b12ffa2..."
MediaA URI text string for a video or audio recording.My videoadded as an app feature
"https://northwindtraders.com/intro.mp4"
"appres://blobmanager/3ba411c..."
NumberA floating point number.123
-4,567
8.903e121
ChoiceA choice from a set of options, backed by a number. This data type combines a localizable text label with a numeric value. The tag appears in the app and the numerical value is stored and used for comparisons.ThisItem.OrderStatus
RecordA record of data values. This composite data type contains instances of the other data types listed in this topic. More information:work with tables.{ Company: "Traders of the North Wind",
Staff: 35,
Nonprofit: false }
reference recordA reference to a record in a table. These references are often used with polymorphic searches. More information:work with references.First (Accounts). Owner
mesaA table of records. All records must have the same names for their fields with the same data types and omitted fields are treated asbranco. This composite data type contains instances of the other data types listed in this topic. More information:work with tables.Table({ Name: "Sidney",
Nickname: "Higa" },
{ Nome: "Nancy",
Last name: "Anderson" } )
TextA string of Unicode text."Hello World"
TempoAn undated time, in the application user's time zone.Tempo (11, 23, 45)
Two optionsA 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 application and the boolean value is stored and used for comparisons.This article. Taxable
untyped objectAn object of an undeclared type. The underlying object can be any existing type and can be converted to compatible types using functions likeboleano(),Valentina(),Mesa()etc. For more information, seeuntyped objectyWorking with JSON.ParseJSON("{ ""Field"" : 1234 }").Field

Many of these data types are similar and have the same underlying representation, such as ahyperlinkfield that is treated asText. Additional data types provide better out-of-the-box experiences in forms and other controls.

Branco

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

Use oBrancowork with theTo placeoCorrectionfunction to establish a variable or field tobranco. For example,set(x, blank())remove any value in global variablex.

test for abrancovalue using theis blankFunction. replace possiblebrancovalues ‚Äč‚Äčwithoutbrancovalues ‚Äč‚Äčusing theget togetherFunction.

As all data types supportbranco, heboleanoyTwo optionsdata types effectively have three possible values.

Text, hyperlink, image and media

These four types of data are based on aUnicodetext string

embedded text

Text strings embedded in a formula are enclosed in double quotes. Use two double quotes together to represent a single double quote in the text string. For example, using the following formula inSelectingowned by aButtonto 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 double quotes repeated around itHello World!are replaced with single double quotes:

Data Types - Power Platform (1)

Single quotes are used toidentifier namesthat contain special characters and have no special meaning in a text string.

(Video) Dataverse Data Types

string interpolation

Use string interpolation to embed formulas in a string of text. It is usually easier to work with this and visualize the result than to use theConcatenatefunction theoperator.

Prefix the text string with a dollar signPSand enclose the formula to be incorporated in braces{ }. To include a brace in the text string, use repeating braces:{{o}}. String interpolation can be used anywhere a standard text string can be used.

For example, consider this formula with global variablesapplesset to 3 andbananasdefined as 4:

$"We have {Apples} apples, {Bananas} bananas, making a total of {Apples+Bananas} fruits."

This formula returns the text string.We have 3 apples, 4 bananas, totaling 7 fruits.variablesapplesybananasthey are inserted into the text by replacing the braces, along with the result of the mathematical formulaapples+bananas. Spaces and other characters between braces are preserved as-is.

Built-in formulas can include any function or operator. All that is required is that the result of the formula can be converted to a text string. For example, this formula will insertSurnameif provided, or theFirst nameif not, in a greeting:

$"Welcome {Coalesce( NickName, FirstName )}, nice to meet you!" )

ESurnameis set to "Joe", this formula will produce the text stringWelcome Joe, nice to meet you!. But yesSurnameesbrancoyFirst nameis "Joseph", then this formula producesDear José, nice to meet you!instead of.

String interpolation can include standard text strings in the embedded formula. For example, if noneSurnameemFirst namehave been provided, we can still provide"Amigo"instead:

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

String interpolations can even be nested. Consider this example whereFirst,Half, youLastnames are combined into a salutation. Even if one or two of these values ‚Äč‚Äčarebranco, the correct number of spaces will be between parts of the name. If no part is provided, the inner string interpolation will be reduced to an empty string and will be replaced by the interpolationget togetherfunction by "friend".

$"Welcome {Coalesce( Trim( $"{First} {Second} {Last}"}), "Friend" )}!"
FirstHalfLastResult
JohnQunicygamaWelcome John Quincy Doe!
JohnbrancogamaWelcome John Doe!
brancobrancogamaWelcome Donate!
brancobrancobrancoWelcome friend!

new lines

Embedded text strings can contain line breaks. For example, consider setting theTextowned by aLabelcontrol the following:

"Line 1Line 2Line 3"

The above formula results in three lines being displayed in the label control:

Data Types - Power Platform (2)

Newlines also support string interpolation, as shown below:

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

The above formula gives the same result:

Data Types - Power Platform (3)

Image and media resources

AcrossFilemenu, you can add image, video and audio files as application resources. The name of the imported file becomes the name of the resource in the application. In this graphic, the Northwind Traders logo, which bears the namenwindlogo, was added to an application:

Data Types - Power Platform (4)

To use this feature in an application, specify it in theImageowned by aImageto control:

(Video) Microsoft Power FX - Data Types

Data Types - Power Platform (5)

URI for images and other media

You can dig a little deeper into this last example by setting theTextowned by aLabelcontrol thenwindlogo. The label displays a string of text:

Data Types - Power Platform (6)

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

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

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

This URI shows a zoomed-in version of two purple diamonds:

Data Types - Power Platform (7)

You can view the most recently captured image on aCameraMake sure you set theImageproperty of an image control for thePhotocamera control property. The application keeps the image in memory and thePhotoThe camera control property returns a URI reference to the image. For example, you can take a picture and the cameraPhotothe property can return"appres://blobmanager/7b12ffa2ea4547e5b3812cb1c7b0a2a0/1".

It uses a URI to reference an image or other 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 to a Microsoft Dataverse table might return"appres://datasources/Contacts/table/..."As in the camera example, you can display this image by setting theImageproperty of an image control to this reference, which retrieves the binary data.

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

size limits

Like strings and URIs, these data types don't have a predefined length limit.

Binary data referenced by these data types also does not have a predefined size limit. For example, an image captured by camera control is now named as"apres://..."it can be as large and high resolution as your device's camera allows. Resolution, frame rate, and other attributes of media files are not limited by data type, but specific controls for media playback and capture can have their own limitations.

However, all data sizes are subject to the amount of memory available in the application. Browsers running on a desktop computer typically support over 100 megabytes of data. However, the amount of memory available on a device such as a phone can be much less, typically between 30 and 70 megabytes. To determine whether your app will run within these limits, test common scenarios across all devices on which your app should run.

As a best practice, keep data in memory only as long as necessary. Upload images to a database as soon as you can; Download images only when requested by the app user.

Number and Currency

NumberyTo dividedata types use theIEEE 754 double precision floating point standard. This pattern gives you a wide range of numbers to work with, from ‚Äď1.79769 x 10308one 1.79769 x 10308. The smallest value that can be represented is 5 x 10‚Äď324.

Canvas apps can exactly represent whole numbers (or integers) between ‚Äď9,007,199,254,740,991 (‚Äď(253‚Äď 1)) e 9.007.199.254.740.991 (253‚Äď 1), inclusive. This range is larger than the 32-bit (or 4-byte) integer data types commonly used by databases. However, canvas apps cannot represent 64-bit (or 8-byte) integer data types. You might want to store the number in a text field or use a computed column to make a copy of the number in a text field so that it maps to aTextdata type in the canvas app. That way, you can keep, display, and enter these values, as well as compare them to determine if they're equal; however, you cannot perform numerical calculations on them this way.

Floating-point arithmetic is approximate, so it can sometimes give unexpected results with many documented examples. You might expect the formula55/100 * 100to return exactly 55 and(55/100 * 100) - 55to return exactly zero. However, the last formula returns 7.1054 x 10-15, which is very small, but not zero. This small difference is usually not a problem, and the app rounds up when displaying the result. However, small differences can add up in subsequent calculations and give the impression of giving an incorrect answer.

(Video) Microsoft Power Platform - Folge 2 - Grundlagen zu Microsoft Dataverse

Database systems often store coins and perform calculations using decimal math, which gives you a smaller range but greater control over accuracy. By default, canvas apps map currencies in and out of floating point values; therefore, the result may differ from calculations performed on a native decimal data type. If this type of discrepancy causes problems, you may want to work with these values ‚Äč‚ÄčasText, just as you would for the large integers described earlier in this section.

Date, time and date and time

time zones

Date/time values ‚Äč‚Äčbelong to these categories:

  • local user: These values ‚Äč‚Äčare stored inUTC (Coordinated Universal Time), but the app user's time zone affects how the app displays these values ‚Äč‚Äčand how the app user specifies them. For example, the same moment appears differently for a user in Canada than it does for a user in Japan.
  • independent time zone: The app displays these values ‚Äč‚Äčin the same way, and the app user specifies them in the same way, regardless of time zone. The same moment appears the same for a user in Canada as it does for a user in Japan. Application authors who don't expect their applications to run in different time zones use these values ‚Äč‚Äčbecause they are generally simpler.

This table shows some examples:

type of date/timeValue stored in the database.Value displayed and entered 7 hours west of UTCValue displayed and entered 4 hours east of UTC
local userSunday, May 19, 2019
04:00.
Saturday, May 18, 2019
21:00
Sunday, May 19, 2019
08:00
independent time zoneSunday, May 19, 2019
04:00.
Sunday, May 19, 2019
04:00.
Sunday, May 19, 2019
04:00.

Forlocal userdate/time, canvas apps use the device's browser or time zone, but model-driven apps use user settings in the Dataverse. These settings usually match, but the results will be different if these settings are different.

Use oadd dateytime zone informationfunctions to convert local time to UTC and vice versa. See the examples at the end of the documentation for these functions.

numerical equivalents

Canvas apps store and calculate all date/time values, whetherlocal useroindependent time zonein UTC. The app translates the values ‚Äč‚Äčbased on the app user's time zone when it displays them and when the app user specifies them.

When a canvas app reads aindependent time zonevalue from a data source or writes that value to a data source, the application automatically adjusts the value to compensate for the application user's time zone. The app then treats the value as a UTC value, consistent with all other date/time values ‚Äč‚Äčin the app. Due to this compensation, the originalindependent time zoneThe value appears when the app adjusts the UTC value to the app user's time zone.

You can examine this behavior more closely using theValentinato access the underlying numeric value for a date/time value. This function returns the datetime value as the number of milliseconds since January 1, 1970 00:00:00000 UTC.

Since each date/time value is maintained in UTC, the formulaValue( Date( 1970, 1, 1 ) )will not return zero in most parts of the world because theDataThe function returns a date in UTC. For example, the formula would return 28,800,000 in a time zone eight hours off UTC. This number reflects the number of milliseconds in eight hours.

Going back to our previous example:

type of date/timeValue stored in the database.Value displayed and entered 7 hours west of UTCValentinafunction returns
local userSunday, May 19, 2019
04:00.
Saturday, May 18, 2019
21:00
1.558.238.400.000
(Sunday, May 19, 2019
04:00. m. UTC)
independent time zoneSunday, May 19, 2019
04:00.
Sunday, May 19, 2019
04:00.
1.558.263.600.000
(Sunday, May 19, 2019
11.00 am. m. UTC)

Converting Unix times

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

For example, Unix time displays September 9, 2001 01:46:40 UTC as 1,000,000,000. To display this date/time value in a canvas app, multiply this number by 1000 to make it milliseconds, then use it in aTextFunction. FormulaText (1000000000 * 1000, data and time format.UTC)returns the string2001-09-09T01:46:40.000Z.

However, this function returnsSaturday, September 8, 2001 6:46:40 PMif you use theDateTimeFormat.LongDateTime24format in a time zone within -7 hours of UTC (7 hours west of UTC). This result shows theDate and timevalue correctly based on the local time zone.

To convert to Unix time, divide the result ofValentinaper 1,000:
Round Down (Value(UnixTime) / 1000.0)

If you need Unix time in aDatavalue for further calculation or display in Power Apps, use this formula:
DateAdd( Date( 1970,1,1 ), UnixTime, Seconds )

servidor SQL

SQL Server temDatetime, Datetime2, and other date/time data typesthey do not include a time zone offset and do not indicate which time zone they are in. Canvas apps assume these values ‚Äč‚Äčare stored in UTC and treat them aslocal user. If the values ‚Äč‚Äčmust be time zone independent, correct the UTC translations using theTimeZoneOffsetFunction.

Canvas apps use the time zone information included in thedate and time offsetfields when converting a value to the application's internal UTC representation. Applications always use UTC as the time zone (zero time zone offset) when writing data.

(Video) How to ask Power Query to auto detect data types

Canvas apps read and write values ‚Äč‚Äčfrom theTempodata type in SQL Server as strings inISO 8601 duration format. For example you would parse this format to string and use theTempofunction to convert string to text"PT2H1M39S"yetTempovaleria:

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 displayed in a label control, use the Text function to display the seconds)

Combine date and time information

Data,Tempo, youDate and timeThey have different names, but they all contain the same date and time information.

ADataThe value can include time information, which is typically midnight. FORTempoThe value can contain date information, which is typically January 1, 1970. Dataverse also stores time information with ajust datebut it only shows the date information by default. Likewise, canvas apps sometimes distinguish between these data types to determine default formats and controls.

Adding and subtracting date and time values ‚Äč‚Äčdirectly is not recommended because time zone and other conversions can lead to confusing results. Or use theValentinafunction to convert date/time values ‚Äč‚Äčto milliseconds first and take into account the application user's time zone, or use the functionadd dateyCloseDifffunctions to add or subtract from one of these values.

Options and Yes/No

Options and two-option data types provide two or more options for an application user to select. for example, oneorder statusthe choice could offer the optionsNovo,Sent,Invoiced, youCerrado. The two-option data type provides only two options.

Both data types display their labels in a string context. For example, a label control displays one of the order status options if the controlTextThe property is set to a formula that references that choice. Option labels can be localized for app users in different locations.

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

Labels are for display purposes only. You cannot do direct comparisons with tags because they are language specific. Instead, each option has an enumeration that works with the underlying number or boolean value. For example, you cannot use this formula:

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

But you can use this formula:

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

For global options (which share tables), the option set enumeration name matches the global option name. For local options (that are scoped to a table), the name can contain the name of the table. This behavior prevents conflicts if multiple tables have options with the same name. For example, heaccountsthe table can have aorder statuschoose, and your name can beOrderStatus (accounts). This name contains one or more spaces and parentheses, so you must enclose it in single quotation marks if you reference it in a formula.

Furthermore, the values ‚Äč‚Äčof two options can also behave like boolean values. For example, a two-option value calledtax situationcould have labelsTaxableyno taxation, which belong toTRUEyFALSErespectively. To demonstrate it, you can use this formula:

If( ThisArticle.Taxable = TaxStatus.Taxable, ...

You can also use this equivalent formula:

If(ThisItem.Taxable,...

Videos

1. Dataverse for Teams Data Types Overview
(Microsoft Power Platform)
2. ūü§Ė Microsoft Power Automate Desktop Tutorial - How to use variables and data types [DAY 5]
(RPA Champion)
3. Microsoft Dataverse Intro in 20 Minutes #Dataverse
(April Dunnam)
4. Power Apps Types | Canvas Vs Model Driven Vs Portals | Beginner Tutorial
(Reza Dorrani)
5. Introduction to Microsoft Dataverse in Power Apps | Build Tables & Relationships | Beginners Guide
(Reza Dorrani)
6. How to use Power Platform's advanced data backend for all your apps | Microsoft Dataverse
(Microsoft Mechanics)
Top Articles
Latest Posts
Article information

Author: Melvina Ondricka

Last Updated: 02/28/2023

Views: 5674

Rating: 4.8 / 5 (48 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Melvina Ondricka

Birthday: 2000-12-23

Address: Suite 382 139 Shaniqua Locks, Paulaborough, UT 90498

Phone: +636383657021

Job: Dynamic Government Specialist

Hobby: Kite flying, Watching movies, Knitting, Model building, Reading, Wood carving, Paintball

Introduction: My name is Melvina Ondricka, I am a helpful, fancy, friendly, innocent, outstanding, courageous, thoughtful person who loves writing and wants to share my knowledge and understanding with you.