Microsoft tables Power Fx

Microsoft tables Power Fx organisent les données en enregistrements, champs et colonnes à partir de sources telles que Microsoft Excel, SharePoint et SQL Server. Découvrez ces structures pour écrire des formules qui accèdent, forment et manipulent efficacement les données tabulaires.

• Un enregistrement contient une ou plusieurs catégories d’informations sur une personne, un lieu ou une chose. Par exemple, un enregistrement peut contenir le nom, l’adresse e-mail et le numéro de téléphone d’un client donné. Les autres outils désignent un enregistrement comme une « ligne » ou un « article ».
• Une table contient un ou plusieurs enregistrements qui contiennent les mêmes catégories d’informations. Par exemple, un tableau peut contenir le nom, l’adresse e-mail et le numéro de téléphone de 50 clients.

Vous pouvez créer diverses formules qui prennent le nom d’une table en tant qu’argument, à l’image d’une formule dans Excel qui prend une ou plusieurs références de cellule en tant qu’arguments. Certaines formules dans Power Fx retournent une table qui reflète les autres arguments que vous spécifiez. Par exemple, vous pouvez créer une formule pour les raisons suivantes :

• Mettre à jour un enregistrement dans un tableau en spécifiant ce tableau comme étant un des arguments de la fonction Patch
• Ajouter, supprimer et renommer les colonnes d’un tableau en spécifiant ce tableau en tant qu’argument de la fonction AddColumns, DropColumns ou RenameColumns. Aucune de ces fonctions ne modifie le tableau d’origine. Au lieu de cela, la fonction retourne un autre tableau basé sur les autres arguments que vous spécifiez.

Éléments d’une table

Enregistrements

Chaque enregistrement contient au moins une catégorie d’informations pour une personne, un lieu ou une chose. L’exemple précédent montre un enregistrement pour chaque produit (Chocolat, Pain et Eau) et une colonne pour chaque catégorie d’informations (Prix, Quantité à la main et Quantité sur commande).

Dans une formule, vous pouvez référencer un enregistrement par lui-même, en dehors du contexte d’un tableau, en utilisant des accolades. Par exemple, l’enregistrement { Name: "Strawberries", Price: 7.99 } n’est associé à aucun tableau. Notez que les noms de champs, tels que Name et Price dans cet exemple, ne sont pas placés entre guillemets.

Champs

Un champ est un élément d’information particulier dans un enregistrement. Vous pouvez visualiser ce type de champ en tant que valeur dans la colonne d’un enregistrement particulier.

Tout comme avec un contrôle, vous faites référence à un champ d’un enregistrement à l’aide de l’opérateur. sur l’enregistrement. Par exemple, First(Products).Name retourne le champ Name pour le premier enregistrement du tableau Products.

Un champ peut contenir un autre enregistrement ou une autre table (voir l’exemple de la fonction GroupBy). Vous pouvez imbriquer autant de niveaux d’enregistrements et de tableaux que vous le souhaitez.

Colonnes

Une colonne fait référence au même champ d’un ou plusieurs enregistrements dans une table. Dans l’exemple précédent, chaque produit a un champ de prix et ce prix se trouve dans la même colonne pour tous les produits. Le tableau précédent comporte quatre colonnes, affichées horizontalement en haut :

• Nom
• Prix
• Quantité disponible
• Quantité en commande

Le nom de la colonne reflète les champs qu’elle contient.

Toutes les valeurs au sein d’une colonne sont du même type de données. Dans l’exemple précédent, la colonne « Quantity on Hand » contient toujours un nombre et ne peut pas contenir de chaîne, telle que « 12 unités », pour un enregistrement. La valeur d’un champ peut également être vide.

Vous avez peut-être appelé les colonnes des « champs » dans d’autres outils.

Tableau

Un tableau comprend un ou plusieurs enregistrements, chacun disposant de plusieurs champs qui ont des noms cohérents entre les enregistrements.

Un tableau stocké dans une source de données ou une collection a un nom, que vous utilisez pour faire référence au tableau et le transmettre à des fonctions qui utilisent des tableaux en tant qu’arguments. Les tableaux peuvent également être le résultat d’une fonction ou d’une formule.

Comme dans l’exemple suivant, vous pouvez faire référence à un tableau dans une formule à l’aide de la fonction Table avec un jeu d’enregistrements, que vous insérez entre des accolades :

Table( { Value: "Strawberry" }, { Value: "Vanilla" } )

Vous pouvez également définir un tableau à une seule colonne avec des crochets. Méthode équivalente pour écrire la formule ci-dessus :

[ "Strawberry", "Vanilla" ]

Formules de tableau

Dans Excel et Power Fx, vous utilisez des formules pour manipuler des nombres et des chaînes de texte de la même manière :

• Dans Excel, tapez une valeur, telle que 42, dans la cellule A1, puis tapez une formule, telle que A1+2, dans une autre cellule, pour afficher la valeur 44.
• Dans Power Apps, définissez la propriété Default de Slider1 sur 42 et définissez la propriété Text d’une étiquette sur Slider1.Value + 2 pour afficher la valeur 44.

Dans les deux cas, la valeur calculée change automatiquement si vous modifiez les valeurs des arguments (par exemple, le nombre indiqué dans la cellule A1 ou la valeur de Slider1).

De même, vous pouvez utiliser des formules pour atteindre et manipuler des données dans des tableaux et des enregistrements. Vous pouvez utiliser des noms de tableaux en tant qu’arguments dans des formules, comme Min(Catalog, Price) pour afficher la valeur la plus petite dans la colonne Price du tableau Catalog. D’autres formules fournissent des tableaux entiers comme valeurs de retour, tels que RenameColumns(Catalog, "Price", "Cost"), qui retourne tous les enregistrements du tableau Catalog, mais remplace le nom de la colonne Price par Cost.

Tout comme avec des nombres, les formules qui impliquent des tableaux et des enregistrements sont recalculées automatiquement à mesure que le tableau ou l’enregistrement sous-jacent change. Si la valeur de coût d’un produit du tableau Catalog passe en dessous du minimum précédent, la valeur de retour de la formule Min change automatiquement.

Fonctions de tableau et propriétés de contrôle

Prendre en compte la fonction Lower. Si la variable bienvenue contient la chaîne de texte "Hello, World", la formule Lower( welcome ) renvoie "hello, world". Cette fonction ne modifie pas la valeur dans cette variable. Lower est une fonction pure en ce qu’elle ne traite que les entrées et produit les sorties. C’est tout ; cela n’a pas d’effets secondaires. Toutes les fonctions dans Excel et la plupart des fonctions dans Power Fx sont des fonctions pures qui permettent de recalculer automatiquement le classeur ou l’application.

Power Fx offre un ensemble de fonctions qui fonctionnent sur les tables de la même manière. Ces fonctions prennent les tableaux en entrée et filtrent, trient, transforment, réduisent et résument des tableaux entiers de données. En réalité, Lower et de nombreuses autres fonctions qui prennent généralement une seule valeur peuvent également prendre une table à une seule colonne en entrée.

De nombreuses fonctions prennent une table à une seule colonne comme entrée. Si une table entière n’a qu’une seule colonne, vous pouvez la spécifier par son nom. Si une table a plusieurs colonnes, vous pouvez spécifier l’une de ces colonnes en utilisant la syntaxe Table.Colonne. Par exemple, Products.Name renvoie la table à colonne unique de valeurs Nom uniquement de la table Produits.

Vous pouvez remodeler complètement un tableau comme vous le souhaitez en utilisant les fonctions AddColumns, RenameColumns, ShowColumns ou DropColumns . Encore une fois, ces fonctions ne modifient que leur sortie, pas leur source.

Formules comportementales

D’autres fonctions sont spécifiquement conçues pour modifier les données et avoir des effets secondaires. Étant donné que ces fonctions ne sont pas pures, vous devez les créer avec soin et elles ne peuvent pas participer au recalcul automatique des valeurs dans l’application. Utilisez ces fonctions ensemble uniquement dans les formules comportementales.

Étendue de l’enregistrement

Certaines fonctions fonctionnent en évaluant une formule sur tous les enregistrements d’une table, un enregistrement à la fois. Le résultat de la formule est utilisé de différentes façons :

• AddColumns - Formule fournit la valeur de la nouvelle colonne.
• Average, Max, Min, Sum, StdevP, VarP - La formule fournit les valeurs à agréger.
• Filter, Lookup - La formule détermine si l’enregistrement doit être inclus dans la sortie.
• Concat - La formule détermine les chaînes à concaténer.
• Distinct - La formule retourne une valeur, utilisée pour identifier les enregistrements en double.
• ForAll - La formule peut retourner une valeur, éventuellement avec des effets secondaires.
• Sort - La formule fournit la valeur sur laquelle trier les enregistrements.
• With - La formule peut retourner une valeur, éventuellement avec des effets secondaires.

À l’intérieur de ces formules, vous pouvez référencer les champs de l’enregistrement qui est en cours de traitement. Chacune de ces fonctions crée une « étendue d’enregistrement » dans laquelle la formule est évaluée et où les champs de l’enregistrement sont disponibles en tant qu’identificateurs de niveau supérieur. Vous pouvez également référencer les propriétés de contrôle et d’autres valeurs à partir de votre application.

Par exemple, prenez une table de Produits placée dans une variable globale :

Set( Products,
    Table(
        { Product: "Widget",    'Quantity Requested': 6,  'Quantity Available': 3 },
        { Product: "Gadget",    'Quantity Requested': 10, 'Quantity Available': 20 },
        { Product: "Gizmo",     'Quantity Requested': 4,  'Quantity Available': 11 },
        { Product: "Apparatus", 'Quantity Requested': 7,  'Quantity Available': 6 }
    )
)

Pour déterminer si l’un de ces produits a plus demandé que ce qui est disponible :

Filter( Products, 'Quantity Requested' > 'Quantity Available' )

Le premier argument à filtrer (Filter) est le tableau d’enregistrements à utiliser et le deuxième argument est une formule. Filter crée une étendue d’enregistrement pour l’évaluation de cette formule où les champs de chaque enregistrement sont disponibles, dans ce cas Product, Quantity Requested et Quantity Available. Le résultat de la comparaison détermine si chaque enregistrement doit être inclus dans le résultat de la fonction :

En ajoutant à cet exemple, vous pouvez calculer la quantité de chaque produit à commander :

AddColumns( 
    Filter( Products, 'Quantity Requested' > 'Quantity Available' ), 
    "Quantity To Order", 'Quantity Requested' - 'Quantity Available'
)

Ici, vous ajoutez une colonne calculée au résultat. AddColumns a sa propre étendue d’enregistrement qu’il utilise pour calculer la différence entre ce qui est demandé et ce qui est disponible.

Enfin, vous pouvez réduire la table de résultats aux colonnes souhaitées :

ShowColumns(
    AddColumns(
        Filter( Products, 'Quantity Requested' > 'Quantity Available' ),
        "Quantity To Order", 'Quantity Requested' - 'Quantity Available'
    ),
    "Product",
    "Quantity To Order"
)

Notez que dans les exemples précédents, vous utilisez des guillemets doubles (") à certains endroits et des guillemets simples (') dans d’autres endroits. Les guillemets simples sont requis lors du référencement de la valeur d’un objet, tel qu’un champ ou une table, si le nom de l’objet contient un espace. Utilisez des guillemets doubles lorsque vous ne référencez pas la valeur d’un objet, mais que vous en parlez plutôt, en particulier dans les situations où l’objet n’existe pas encore, comme dans le cas de AddColumns.

Lever les ambiguïtés

Les noms de champs que vous ajoutez avec l’étendue d’enregistrement remplacent les mêmes noms d’un autre emplacement dans l’application. Lorsque cette substitution se produit, vous pouvez toujours accéder aux valeurs en dehors de l’étendue de l’enregistrement à l’aide de l’opérateur @ de désambiguation :

• Pour accéder aux valeurs à partir d’étendues d’enregistrement imbriquées, utilisez l’opérateur @ avec le nom de la table sur laquelle vous travaillez, en suivant ce modèle :
  Table[@NomChamp]
• Pour accéder à des valeurs globales, telles que des sources de données, des collections et des variables de contexte, utilisez le modèle [@NomObjet] (sans désignation du tableau).

Si la table sur laquelle vous travaillez est une expression, telle que Filter(Table, ... ), vous ne pouvez pas utiliser l’opérateur d’ambiguïté. Seule l’étendue d’enregistrement la plus interne peut accéder aux champs de cette expression de table en n’utilisant pas l’opérateur d’ambiguïté.

Par exemple, imaginez que vous disposez d’une collection X :

Vous pouvez créer cette collection à l’aide de ClearCollect( X, [1, 2] ).

Et une autre collection Y :

Vous pouvez créer cette collection à l’aide de ClearCollect( Y, ["A », « B"] ).

En outre, définissez une variable de contexte nommée Value avec cette formule : UpdateContext( {Value: "!"} )

Résumons. Dans ce contexte, la formule suivante :

Ungroup(
    ForAll( X,
        ForAll( Y,
            Y[@Value] & Text( X[@Value] ) & [@Value]
        )
    ),
    "Value"
)

génère ce tableau :

Que se passe-t-il ici ? La fonction ForAll la plus externe définit une étendue d’enregistrement pour X. Vous pouvez donc accéder au champ Valeur de chaque enregistrement au fur et à mesure qu’il traite. Vous pouvez y accéder simplement à l’aide du mot Valeur ou à l’aide de X[@Value].

La fonction ForAll la plus interne définit une autre étendue d’enregistrement pour Y. Étant donné que cette table a également un champ Valeur défini, l’utilisation de la valeur ici fait référence au champ dans l’enregistrement de Y et non plus à celui de X. Pour accéder au champ Valeur de X, vous devez utiliser la version plus longue avec l’opérateur d’ambiguïté.

Étant donné que Y est l’étendue d’enregistrement la plus interne, l’accès aux champs de cette table ne nécessite pas d’ambiguïté. Vous pouvez donc utiliser cette formule avec le même résultat :

Ungroup(
    ForAll( X,
        ForAll( Y,
            Value & Text( X[@Value] ) & [@Value]
        )
    ),
    "Value"
)

Toutes les étendues d’enregistrement ForAll remplacent l’étendue globale. La variable de contexte Valeur que vous avez définie n’est pas disponible par nom sans l’opérateur d’ambiguïté. Pour accéder à cette valeur, utilisez [@Value].

Ungroup aplatit le résultat, car les fonctions ForAll imbriquées entraînent un tableau de résultats imbriqué.

Tables à une seule colonne

Pour opérer sur une seule colonne d’une table, utilisez la fonction ShowColumns comme dans cet exemple :

ShowColumns( Products, "Product" )

Cette formule produit ce tableau à une seule colonne :

Pour une alternative plus courte, spécifiez Table.Column, qui extrait la table à une seule colonne de Column de Table. Par exemple, cette formule produit exactement le même résultat qu’en utilisant ShowColumns.

Products.Product

Enregistrements en ligne

Vous faites référence à des enregistrements en utilisant des accolades qui contiennent des valeurs de champ nommées. Par exemple, vous pouvez faire référence au premier enregistrement du tableau présenté au début de cette rubrique à l’aide de la formule suivante :

{ Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 }

Vous pouvez également incorporer des formules dans d’autres formules, comme le montre cet exemple :

{ Name: First(Products).Name, Price: First(Products).Price * 1.095 }

Vous pouvez imbriquer des enregistrements en imbriquant des accolades, comme le montre cet exemple :

{ 'Quantity': { 'OnHand': ThisItem.QuantOnHand, 'OnOrder': ThisItem.QuantOnOrder } }

Placez chaque nom de colonne qui contient un caractère spécial, un espace ou un signe deux-points entre des guillemets simples. Pour utiliser un seul guillemet dans un nom de colonne, double-cliquez dessus.

Notez que la valeur de la colonne Price n’inclut pas de symbole monétaire, comme le signe dollar. Cette mise en forme est appliquée lorsque la valeur est affichée.

Tables en ligne

Vous pouvez créer un tableau à l’aide de la fonction Table et d’un jeu d’enregistrements. Vous pouvez exprimer le tableau présenté au début de cette rubrique à l’aide de la formule suivante :

Table( 
   { Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 },
   { Name: "Bread", Price: 4.95, 'Quantity on Hand': 34, 'Quantity on Order': 0 },
   { Name: "Water", Price: 4.95, 'Quantity on Hand': 10, 'Quantity on Order': 0 } 
)

Vous pouvez également imbriquer des tableaux :

Table( 
   { Name: "Chocolate", 
     'Quantity History': Table( { Quarter: "Q1", OnHand: 10, OnOrder: 10 },
                                { Quarter: "Q2", OnHand: 18, OnOrder: 0 } ) 
   }
)

Tables de valeurs en ligne

Vous pouvez créer des tables à une seule colonne en spécifiant des valeurs entre crochets. La table qui en résulte a une seule colonne nommée Value.

Par exemple, [ 1, 2, 3, 4 ] est équivalent à Table( { Value: 1 }, { Value: 2 }, { Value: 3 }, { Value: 4 } ) et renvoie cette table :