Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Un compendium de techniques pour Microsoft Excel BI & Microsoft Power BI V0.11

Un compendium de techniques pour Microsoft Excel BI & Microsoft Power BI V0.11 Révision 98 (2020-05-07) Vincent Isoz, Daname Kolani, Laurianne Demarlière Échantillon Vincent ISOZ Microsoft Power BI / Microsoft Excel BI 2/950 Le but de ce fichier est de parcourir avec des exemples simples et concrets le maximum de fonctions DAX de Power BI permettant de bien comprendre l'utilité de chacune (lorsque associée à d'autres fonctions ou pas) et de se préparer aussi à la certification Exam 70-778 sur Power BI. L'idée étant de produire une multitude de petits MWE (Minimal Working Example). Chaque exemple est donné avec un petit résumé fixant l'objectif à atteindre et à la page suivante, la résolution avec la capture d'écran de la formule DAX dans la barre de formule et le résultat tel qu'il apparaît dans le tableau de bord ou à l'écran. Chaque exercice doit être fait dans une feuille Power BI à part! Le participant devra s'aider de la référence en ligne suivante pour faire les exercices: https://msdn.microsoft.com/en-us/library/ee634396.aspx On rappelle : 1. Qu'il n'est pas possible de changer le typage des colonnes à ce jour dans des mesures DAX. Donc pour les colonnes numériques contenant des erreurs ou des textes, on sera obligé de d'abord créer des colonnes en DAX qui effectuent le nettoyage avant de pouvoir faire de mesures avec en DAX!!!!!!!!!!!!! 2. Donc qu'il n'est pas possible de créer des mesures DAX renvoyant des images pour faire des KPI et que vous serez obligé de passer par la création de colonnes DAX! Échantillon Vincent ISOZ Microsoft Power BI / Microsoft Excel BI 3/950 1. Table des matières 1. Table des matières................................................................................................................. 3 2. À propos du rédacteur ........................................................................................................ 12 3. Avertissement ...................................................................................................................... 14 4. Votre avis nous intéresse! ................................................................................................... 15 5. Préambule ............................................................................................................................ 16 6. Copyright ............................................................................................................................. 17 7. Médiagraphie (liens) ........................................................................................................... 18 8. Introduction ......................................................................................................................... 21 Modélisation de bases de données (schémas en étoile ou flocon) .......................................................... 35 9. Nouveautés ........................................................................................................................... 36 10. Licences ................................................................................................................................ 40 11. Limites .................................................................................................................................. 45 12. Meilleures Pratiques (best practices) ................................................................................ 46 13. Moteurs VertiPaq et XVelocity.......................................................................................... 48 14. Data Munging ...................................................................................................................... 52 Data Input ............................................................................................................................................... 53 Data sources ............................................................................................................................................ 54 Tables manuelles ................................................................................................................................................ 54 Tables DAX ....................................................................................................................................................... 56 Microsoft SharePoint ......................................................................................................................................... 61 Bibliothèque d'images (Asset Library) ............................................................................................................................ 61 Liste d'images (liste personnalisée avec colonne Image) ................................................................................................ 66 Microsoft Excel .................................................................................................................................................. 70 Import d'un fichier Microsoft Excel stocké sur SharePoint ............................................................................................. 70 Import (fusion) automatique de toutes les feuilles (incluant les futures) d'un classeur ................................................... 75 Import contenu bibliothéques SharePoint ....................................................................................................................... 78 JSON .................................................................................................................................................................. 87 Microsoft SQL Server ........................................................................................................................................ 92 DirectQuery .................................................................................................................................................................... 94 Query Folding ................................................................................................................................................................. 97 OData ............................................................................................................................................................... 101 Dataflow ........................................................................................................................................................... 106 Streaming Dataset ............................................................................................................................................ 112 Import vs DirectQuery vs Live Connection .......................................................................................... 126 Data Flows (flux de données) ............................................................................................................... 129 Gateways .............................................................................................................................................. 130 15. Refresh (mises à jour des données).................................................................................. 140 Mises à jour des tables dans Power Query ........................................................................................... 141 Mises à jour des tables dans Power BI Desktop ................................................................................... 142 Mises à jour manuelle de toutes les tables ....................................................................................................... 142 Mises à jour manuelle de certaines tables ........................................................................................................ 142 Mises à jour automatique ................................................................................................................................. 143 Mises à jour incrémentielles............................................................................................................................. 145 Mises à jour dans Power BI Service ..................................................................................................... 146 Mises à jour manuelles ..................................................................................................................................... 146 Mises à jour automatiques ................................................................................................................................ 146 16. Data Mashup (Power Query) ........................................................................................... 147 Commentaires M .................................................................................................................................. 148 Échantillon Vincent ISOZ Microsoft Power BI / Microsoft Excel BI 4/950 Icônes Power Query .............................................................................................................................. 149 Lister toutes les fonctions ..................................................................................................................... 150 Tri sur colonnes multiples .................................................................................................................... 154 Construction d'un VLOOKUP approximatif ........................................................................................ 157 Utiliser des variables ............................................................................................................................ 168 Fusion de feuilles multiples d'un unique fichier Microsoft Excel ........................................................ 169 Traîtement batch d'un dossier de fichiers Microsoft Excel avec feuilles identiquement nommées ..... 170 Fusion de fichiers Microsoft Excel d'un dossier avec feuille unique non identiquement nommées .... 182 Fusionner un dossier avec problèmes d'en-têtes de colonnes ............................................................... 184 Ajouter la date de dernières mise à jour sur un rapport ........................................................................ 188 Supprimer automatiquement toutes les lignes après une ligne particulière .......................................... 191 Création d'une Table de calendrier simple avec durée en M ................................................................ 194 Création d'une Table de calendrier avec étendue fixe en M ................................................................. 196 Création d'une Table de calendrier avec variable en M ........................................................................ 197 Création d'une Table de calendrier complète en M .............................................................................. 198 Filtre dynamique en M ......................................................................................................................... 201 Création d'une Fonction simple en M ................................................................................................... 203 Création d'une Fonction avec arguments optionnels en M ................................................................... 204 Création d'une Fonction avec arguments optionnels et typés en M ...................................................... 205 Fonction de nettoyage d'espaces (pour Power Query Excel seulement!) ............................................. 206 Création d'une Liste numérique en M ................................................................................................... 211 Création d'une Séquence numérique en M ........................................................................................... 212 Création d'une Séquence numérique en M avec pas spécifique ........................................................... 213 Création d'une Liste statique en M ....................................................................................................... 214 Création d'une Table statique en M ...................................................................................................... 216 Création d'une Table dynamique en M ................................................................................................. 218 Création d'un simple colonne de valeurs aléatoires .............................................................................. 220 Ajout d'une colonne de valeurs aléatoires numériques ......................................................................... 221 Création d'une colonne de valeurs aléatoires catégorielles ................................................................... 225 Échantillonnage aléatoire simple .......................................................................................................... 226 Traitements des dates ............................................................................................................................ 231 Création d'un paramètre pour chemin de fichiers (ou serveurs) ........................................................... 232 Importer le fichier du jour..................................................................................................................... 240 Compter les mots .................................................................................................................................. 242 Extraire les premiers/derniers de groupes ............................................................................................. 255 Numéroter des groupes ......................................................................................................................... 262 Fusionner de multiples lignes en une seule ligne ................................................................................. 270 Lag de colonnes .................................................................................................................................... 275 Fuseaux horaires ................................................................................................................................... 278 17. DAX (Data Analysis Expression) ..................................................................................... 282 Reverse Query (DAX) .......................................................................................................................... 283 Exemple 1.: Requête EVALUATE .................................................................................................................. 284 Solution ......................................................................................................................................................................... 286 Exemple 2.: Requête ALL................................................................................................................................ 293 Exemple 3.: Requête SELECTCOLUMNS avec table unique......................................................................... 297 Exemple 4.: Requête VALUES ........................................................................................................................ 299 Exemple 5.: Requête DISTINCT ..................................................................................................................... 302 Exemple 6.: Requête SELECTCOLUMNS( ) + DISTINCT( ) + AVERAGE( ) + ROUND( ) ....................... 305 Exemple 7.: Requête CALCULATETABLE ................................................................................................... 306 Exemple 8.: Requête FILTER .......................................................................................................................... 309 Échantillon Vincent ISOZ Microsoft Power BI / Microsoft Excel BI 5/950 Exemple 9.: Requête SAMPLE ........................................................................................................................ 310 Exemple 10.: Requête SUMMARIZE ............................................................................................................. 311 Exemple 11.: Requête SUMMARIZE avec tables multiples ........................................................................... 315 Exemple 12.: Requête GROUPBY et CURRENTGROUP ............................................................................. 319 Exemple 13.: Requête SUMMARIZECOLUMNS .......................................................................................... 320 Exemple 14.: Requête SUMMARIZECOLUMNS et IGNORE ...................................................................... 321 Exemple 15.: Requête TOPN ........................................................................................................................... 322 Exemple 16.: Requête RANK.EQ .................................................................................................................... 324 Exemple 17.: Requête ROW ............................................................................................................................ 325 Exemple 18.: Requête SELECTCOLUMNS avec RELATED ........................................................................ 328 Exemple 19.: Requête GENERATE avec RELATEDTABLE ........................................................................ 332 Exemple 20.: Requête GENERATEALL avec RELATEDTABLE ................................................................ 336 Exemple 21.: Requête NATURALINNERJOIN.............................................................................................. 339 Exemple 22.: Requête NATURALLEFTOUTERJOIN ................................................................................... 344 Exemple 23.: Requête ADDCOLUMNS ......................................................................................................... 350 Exemple 24.: Requête ROLLUP ...................................................................................................................... 352 Exemple 25.: Requête ROLLUPGROUP ........................................................................................................ 353 Exemple 26.: Requête ISSUBTOTAL ............................................................................................................. 355 Exemple 27.: Requête ROLLUPADDISSUBTOTAL ..................................................................................... 357 Data Definition (DAX) ......................................................................................................................... 358 Exercice 28.: Commande CALENDAR ........................................................................................................... 359 Solution ......................................................................................................................................................................... 360 Exercice 29.: Commande CALENDARAUTO ................................................................................................ 363 Solution ......................................................................................................................................................................... 364 Exercice 30.: Commande SUMMARIZE ........................................................................................................ 366 Solution ......................................................................................................................................................................... 367 Exercice 31.: Commande ADDCOLUMNS .................................................................................................... 368 Solution ......................................................................................................................................................................... 369 Exercice 32.: Commande UNION ................................................................................................................... 370 Solution ......................................................................................................................................................................... 371 Exercice 33.: Commande UNION et ROW ..................................................................................................... 372 Solution ......................................................................................................................................................................... 373 Exercice 34.: Commande UNION et GENERATESERIES ............................................................................ 374 Solution ......................................................................................................................................................................... 376 Exercice 35.: Commande DATATABLE ........................................................................................................ 377 Solution ......................................................................................................................................................................... 378 Exercice 36.: Commande INTERSECTION .................................................................................................... 380 Solution ......................................................................................................................................................................... 381 Exemple 37.: Commande CROSSJOIN ........................................................................................................... 382 Exemple 38.: Commande GENERATESERIES avec génération de de séquences ou nombres aléatoires ..... 386 Data Analysis (DAX) ........................................................................................................................... 389 Compatibilités fonctions DAX ......................................................................................................................... 393 Raccourcis claviers DAX ................................................................................................................................. 394 Commentaires DAX ......................................................................................................................................... 395 Icônes DAX ..................................................................................................................................................... 396 Organiser les fonctions.................................................................................................................................................. 397 "Table" de mesures ........................................................................................................................................................................ 398 Dossiers et sous-dossiers de mesures ............................................................................................................................................. 400 Opérateurs DAX .............................................................................................................................................. 403 DAX Catégorie Fonctions d'Informations........................................................................................................ 404 Exercice 1.: Fonction USERNAME .............................................................................................................................. 404 Exercice 2.: Fonction USERPRINCIPALNAME ......................................................................................................... 405 Exercice 3.: Fonction USERCULTURE ....................................................................................................................... 406 DAX Variables ................................................................................................................................................. 407 DAX Catégorie Fonctions Logiques ................................................................................................................ 408 Exercice 4.: Fonction IF (+VALUE) ............................................................................................................................. 408 Échantillon Vincent ISOZ Microsoft Power BI / Microsoft Excel BI 6/950 Solution ......................................................................................................................................................................................... 409 Exercice 5.: Fonction IF, OR (+VALUE) ..................................................................................................................... 410 Solution ......................................................................................................................................................................................... 411 Exercice 6.: Fonction AND ........................................................................................................................................... 412 Exercice 7.: Fonction SWITCH (pour KPI) .................................................................................................................. 413 Solution ......................................................................................................................................................................................... 414 DAX Catégorie de Filtres ................................................................................................................................ 415 Exercice 8.: Fonction RELATED ................................................................................................................................. 415 Solution ......................................................................................................................................................................................... 417 Exercice 9.: Fonction CALCULATE ............................................................................................................................ 419 Solution ......................................................................................................................................................................................... 420 Exercice 10.: Fonctions FILTER et AND dans CALCULATE .................................................................................... 421 Solution ......................................................................................................................................................................................... 422 Exercice 11.: Fonctions FILTER et IN dans CALCULATE ......................................................................................... 423 Solution ......................................................................................................................................................................................... 424 Exercice 12.: Fonction FILTER avec FOUND ou LEFT .............................................................................................. 428 Solutions ........................................................................................................................................................................................ 429 Exercice 13.: Fonction FILTER avec EVALUATE et ORDER BY ............................................................................. 430 Exercice 14.: Fonction ALL .......................................................................................................................................... 431 Solution ......................................................................................................................................................................................... 432 Exercice 15.: Fonction ALL et REMOVEFILTER avec slicer (segment) ignoré et filtre statique supplémentaire ...... 434 Solution ......................................................................................................................................................................................... 435 Exercice 16.: Fonction ALLSELECTED ...................................................................................................................... 436 Exercice 17.: Fonction ALLSELECTED avec calcul ................................................................................................... 438 Solution ......................................................................................................................................................................................... 439 Exercice 18.: Fonction ALLSELECTED avec colonnes multiples ............................................................................... 440 Solution ......................................................................................................................................................................................... 441 Exercice 19.: Fonction ALLSELECTED et EXCEPT( ) ............................................................................................... 443 Solution ......................................................................................................................................................................................... 444 Exercice 20.: Fonction ALLSELECTED et ISONORAFTER pour les Running Total ................................................ 446 uploads/s3/ microsoft-power-bi.pdf