ATTENTION : Cette page n'est pas un "Tuto"...
Il s'agit d'un "recueil de notes techniques" de Power Query, regroupées ici pour mémoire.
Contacter Olivier si le sujet vous intéresse.
et choisir la feuille (valider avec bouton Charger)
Acceder ensuite aux requetes crées via Données requetes & Connexion (Clic droit sur la source + ruban Requete / modifier)
Après avoir intégré la source : utiliser la 1ere ligne
Cliquer sur Choisir les Colonnes... :-)
( si colonnes selectionnées)
via Clic Droit En-tete
=> supprimer les autres colonnes
Plein de commandes via clic-droit sur un en tête...
(clis droit sur une valeur...)
OU
clic droit sur l'en tête
Certains exports ont des lignes identiques à la précédente, mais non remplie
(exemple colonne 3 avec une valeur 'essai' et 'essai 2' non reprise sur lignes suivantes)
EXEMPLE : Passer de plusieurs lignes...
... à
1) Pivoter une colonne
(créer autant de colonnes que de valeur...)
2 colonnes sont créées... (1 colonne par occurence)
Sélectionner la colonne à grouper
(notion de colonnes à garder)
utiliser Grouper colonnes
=>utiliser bouton Ajouter agrement pour les colonnes à garder
=> utiliser bouton Aggréger les colonnes non sélectionnées pour les colonnes à compter...
(exemple remplacer des écoles)
1) Créer une table de remplacement 'ecoles'
(attention au respecte majuscule !)
2) Dans la table 'Onde'
utiliser 'Fusionner des requêtes'
sélectionner la colonne à remplacer dans 'Onde'...
(ne pas modifier jointure => ext à gauche)
3) cliquer sur le picto dans l'en tête de la nouvelle colonne créée...
(la colonne porte le nom de la table)
... choisir la colonne...
... la valeur de la colonne sélectionnée apparaitsu une correspondance est trouvée
OU Colonne Conditionnelle
Exemple : créer 1 colonne qui indiquera "OK" ou "Non" ou Peut être" selon le contenu d' 1 autre colonne (avec 3 valeurs différentes)
1) Sélectionner la colonne sur laquelle s'appuyer...
(dans l'exemple, 'etatCO' contient RAD ou INSCR ou PRE
2) saisir les scénarios...
entrer le 1er scénario => quand 'Rad' vous voulez 'non'
valider avec Tab
Renseigner ensuite un autre cas...
entrer le 2nd scénario => quand "Inscr" vous voulez 'ok'
valider avec ok
SI excel comprend la logique il grise des résultats...
Excel commence à proposer un traitement au dessus de la table...
défiler et entrer le 3eme scénario => quand "Pré" vous voulez 'peut-etre'
Si terminé, utiliser le bouton OK (ecran ci dessus)
Une fois l'exemple créé (même incomplet)
utiliser lle paramétrage de l'action pour le personnaliser ou l'enrichir
OU editer directement le code power query. si vous êtes habitué au code ou aux formules d'XL (on reconnait la logique, on respecte les syntaxes)..
Ci dessous un exemple avec :
plusieurs conditions (usage de l'opérateur 'and')à)
un exemple sur une colonne qui contient des chiffres (opérateur logiques Supérieur, inférieur à, etc...)
En éditant le code... { } => à l'intérieur d'autre { } séparés par de " virgules ")
{ { "colonne 1", ordre.xxx} , { "colonne 2", ordre.xxx} }
Il est souvent possible de créer des conditions avec l'Ajout colonne par exemple (alimenter avec plusieurs scénarios)
Si ca échoue, ou par facilité => editer le code... Voici un exemple :
"MONTEST" est le nom de la colonne qui sera créée...
[test] le nom de la colonne qui est testée/analysée
#multitest = Table.AddColumn(#"ligne_precedente", "MONTEST",
each if [test] = null then "pas de QF"
else if [test] > 10000 then "Score max"
else if [test] > 999 then "score 9"
else if [test] > 899 then "score 8"
( rajouter les tests ici)
else if [test] > 99 then "score 1"
else if [test] > 0 then "score 0"
else "vérifier PowerQuery", type text),
respecter la syntaxe :-)
Avec certains exports, les cellules ne sont pas considérées comme 'vides' dans Excel...
le code en ligne 2 corrige le probleme
PRE REQUIS :
2 tables distinctes avec 1 colonne en commun
1 table sur les 2 doit avoir un "ID Unique"
1 ) Créer TCD en Ajoutant au modèle de données
Les 2 tables doivent êtres ajoutées au modèle
NOTE : L'association d'un modèle intègre de fait la possibilité d'utiliser une fonction "Distincte"
2) construre TCD
Tableau avec ID unique à prioriser, puis mixer avec donnée d'une autre table
NOTE : a ce stade, les chiffres affichés dans TCD identiques : il faut finaliser les liens de l'étape (3) pour que les chiffres soient justes !
3) Créer Liaisons d'association
Le tableau associé est celui avec l'ID Unique !!!!
4) clis cdroit Ajouter 1 mesure
Possibilité ajouter des mesures complexes et utiliser dans TCD (dans l'exemple '% reusssite' n'est qu'un
Ci contre :
Fabrique une colonne ADL...
transforme toute la ligne (Sauf 'Matricule') en minuscule
regarde si l'un ou l'autre est dedans...
renvoie trouvé si présent (then) ,
sinon pas trouvé (else)
#"Lignes filtrées"= (...)
#"surlaligne" = Table.AddColumn(#"Lignes filtrées", "ADL", each if List.ContainsAny(List.Transform(Record.ToList(Record.RemoveFields(_, {"Matricule"})), Text.Lower), {"Paul" , "contractuel"})
then "Trouvé"
else "pas trouvé"),
1.
Faire une table nommée sur feuille Excel
2.
Données / récupérer et transformer données / A partit d'une table ou plage
3.
Dans la requête Power query créée :
Ouvrir Editeur avancé
coller le code suivant :
"paramACM" est le nom de la table Excel
pACM = Source{0}[Colonne1]
le nom donné à la 1ère valeur de la colonne1
Nommer ensuite la requête avec le même nom paramACM
La Variable est maintenant créée...
Elle s'utilisera avec le nom de la Requête paramACM (voir 4.)
4.
La récupération de la valeur de la variable s'utilise ensuite directement dans le code PowerQuery
Ci dessous un exemple qui :
#"Personnalisée ajoutée1" = Table.AddColumn(#"Lignes filtrées", "ADL", each if List.ContainsAny(List.Transform(Record.ToList(Record.RemoveFields(_, {"Matricule"})), Text.Lower), {paramACM , "contractuel"}) then paramACMelse ""), #"Lignes filtrées1" = Table.SelectRows(#"Personnalisée ajoutée1", each ([ADL] = paramACM))Exemple : Synthétiser un classeur avec 1 feuille par mois...
Conseils / Prérequis :
Dans le classeur qui accueillera la synthèse :
choisir Données (1) / Obrenir Données (2) / A partir d'un classeur (3)
Cocher Sélectionner +ieurs éléments (en haut) (1)
Sélectionner le feuilles souhaitées (2)
Cliquer sur "Transformer les données"
Power Query s'ouvre avec les feuilles ajoutées...
Dans Accueil, cliquer sur Ajouter des requêtes (1)/ Ajouter... comme étant nouvelles
cocher Au moins 3 feuilles
Faire passer à droite les requêtes
OK
Vérifier le résultat... (Les requêtes sont mises bout à bout, vérifier bien les données et les colonnes)
...et travailler la requête créée (si nécessaire)
Quand finalisé,, cliquer sur le sous-menu du 1er bouton : "Fermer et Charger dans..." (onglet Accueil)
choisir la méthode d'utilisation de la requête...
Selon votre choix, des feuilles sont crées pour chaque requête
Dans la gestion des Requêtes, il est possible de ne pas vouloir visualiser certaines requêtes...
Survoler la Requête...
...et aller dans Modifier / Charger Dans...
... hoisir "Ne créer que la connexion"
Celà aura pour effet de pour retirer le tableau de la feuille choisie :-)
(vous pouvez ensuite supprimer la feuille)