Mettre bout à bout (enchainer) des sources de données dans Excel avec Power Query (Data Explorer)

Fleid

Un cas d’usage qui pouvait être reproché à Excel, et à Power Pivot également, c’était l’impossibilité de prendre 2 ou plus sources de données identiques et de les mettre bout à bout (en mode UNION pour les SQLeux). Typiquement : j’ai un fichier CSV par mois dans un répertoire, et j’aimerais tous les mettre dans le même tableau Excel pour travailler dessus. Jusqu’alors, la seule solution c’était les copiés/collés successifs à la main, avec tous les risques que ça comporte.

Heureusement Power Query (anciennement Data Explorer) vient à la rescousse ! C’est quoi ? C’est un add-in Excel gratuit, édité par Microsoft, qui sert de mini-ETL à Excel. Sincèrement, c’est peut-être l’outil de BI Self-Service qui enflamme le plus les consultants décisionnel Microsoft en ce moment, alors allez l’installer!

Le mieux pour vous convaincre étant sans doute de passer à la pratique, voici comment employer la chose :

Scénario 1 : 3 tableaux dans le même fichier Excel

  • Pour l’exemple j’ai créé 3 tableaux dans Excel 2013, dans la même feuille ou pas, ça ne change rien, qui contiennent des données à la structure identique:

Append_1

  • Le but : créer une « Query », une requête dans Power Query, sur chacune des tables, pour ensuite les associer en un seul résultat
  • Etape 1 (à répéter 3 fois, une fois par tableau) : a. Je clique dans un tableau (si ce n’est pas fait, n’oubliez pas de « Mettre sous Forme de Tableau » vos données), b. Je vais dans l’onglet Power Query, c. Je créé une Query depuis un Tableau (From Table). Notez que si l’onglet Power Query n’apparait pas, le mieux est de valider qu’il est bien activé (Fichier > Options > Compléments > Gérer : Compléments COM : « Microsoft Power Query for Excel » doit être coché)

Append_2

  • En bleu : l’interface de transformation de données de Power Query, ici on a besoin de rien, donc on valide (Done):

Append_3

  • La Query est créée, elle correspond à un tableau dans un nouvel onglet Excel:

Append_4

  • On répète l’opération 3 fois, une fois par tableau pour obtenir les onglets suivants (oui mon Excel est en anglais, Sheet ou Feuille c’est pareil ;)) :

Append_5

  • Etape 2 : a. On va dans l’onglet Power Query, et b. on choisit « Append »

Append_6

  • Dans l’interface, je choisis 2 Queries, l’ordre n’importe pas ici. Vous noterez que les noms affichés sont les noms des Queries, qui par hasard correspondent ici aux noms des Tableaux. Si vous avez renommé vos Queries, vous retrouverez les nouveaux noms ici:

Append_7

  • Encore une fois, on passe par l’interface de transformation des données de Power Query, encore une fois ici on ne change rien, on valide (Done) :

Append_8

  • Et hop, je me retrouve avec une nouvelle Query qui est la mise bout à bout des 2 premières tables :

Append_9

  • Je répète l’opération, cette fois on prenant dans l’Append le résultat de mon premier Append (Append 1) et le tableau que je n’avais pas pris la première fois :

Append_10

  • Je valide dans l’éditeur et hop, me voilà avec la table complète !

Append_11

  • Chouette non? Alors on peut se dire que c’était beaucoup d’étapes, alors que 3 copiés/collers suffisaient à faire la même chose. Certes. Mais l’avantage énorme des Queries, c’est qu’elles se mettent à jour (cliquer pour zoomer) :

Append_12

  • C’est quand même bien fait non ? 🙂

Scénario 1 – Avancé : Optimisation sur 3 tableaux dans le même fichier Excel

Alors ok, c’est mignon, mais avec la manipulation précédente on se retrouve quand même avec 5 feuilles, et si on ajoute d’autres sources ça va rapidement faire beaucoup trop (2n-1 feuilles avec n sources). Évidemment on peut faire bien mieux, mais il va falloir remonter les manches 😉

  • D’abord il va falloir activer le mode avancé

Append_13

  • Et tant qu’on y est, activez le Fast Combine, ça vous évitera des soucis plus tard :

Append_14

  • Désormais, vous allez pouvoir directement éditer les formules que Power Query utilise pour transformer les données. On retourne donc dans l’interface de transformation des données de Power Query en utilisant une de ces 2 méthodes (ab,ab’c’), ou en créant une nouvelle Query :

Append_15

  • Et dans cette interface, si vous êtes bien passé en mode avancé (voir plus haut), vous devez avoir un petit parchemin à cliquer :

Append_16

  • Qui amène à l’interface de saisie des formules :

Append_17

  • Je vous laisse avec le guide de référence pour voir tout ce qui est possible de faire, perso j’ai la même démarche que dans cet article, je fais toutes les étapes unitairement, et j’essaye d’optimiser ensuite à la main. Dans notre cas :

Append_18

  • En version copiable, à adapter en fonction des noms de vos tableaux (Table1/Table2/Table3) :
let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Source = Table.Combine({Source1,Source2,Source3})
in
Source

Avec cette formule on fait tout en une seule Query, donc un seul onglet, peu importe le nombre de source. Pas mal non ? 🙂

Scénario 2 : 3 fichiers Excel distincts, toujours avec le même format de données

  • Alors de la même manière, on peut faire les fichiers Excel. Je vous donne directement le script, à vous de jouer en manuel pour bien comprendre !

Append_19

let
Source2006 = Excel.Workbook(File.Contents("C:\Temp\2006.xlsx")),
Dataset2006 = Source2006{[Name="Sheet1"]}[Data],
FirstRowAsHeader2006 = Table.PromoteHeaders(Dataset2006),
Source2007 = Excel.Workbook(File.Contents("C:\Temp\2007.xlsx")),
Dataset2007 = Source2007{[Name="Sheet1"]}[Data],
FirstRowAsHeader2007 = Table.PromoteHeaders(Dataset2007),
Source2008 = Excel.Workbook(File.Contents("C:\Temp\2008.xlsx")),
Dataset2008 = Source2008{[Name="Sheet1"]}[Data],
FirstRowAsHeader2008 = Table.PromoteHeaders(Dataset2008),
SourceComplete = Table.Combine({FirstRowAsHeader2006 ,FirstRowAsHeader2007 ,FirstRowAsHeader2008 })
in
SourceComplete

Scénario 3 : 3 fichiers CSV, toujours avec le même format de données

  • Je vous donne directement le script :

Append_20

let
Source2006 = Csv.Document(File.Contents("C:\Temp\2006.csv")),
FirstRowAsHeader2006 = Table.PromoteHeaders(Source2006),
Source2007 = Csv.Document(File.Contents("C:\Temp\2007.csv")),
FirstRowAsHeader2007 = Table.PromoteHeaders(Source2007),
Source2008 = Csv.Document(File.Contents("C:\Temp\2008.csv")),
FirstRowAsHeader2008 = Table.PromoteHeaders(Source2008),
SourceComplete = Table.Combine({FirstRowAsHeader2006 ,FirstRowAsHeader2007 ,FirstRowAsHeader2008 }),
ChangedType = Table.TransformColumnTypes(SourceComplete ,{{"Region", type text}, {"Categorie", type text}, {"Annee", type number}, {"Valeur", type text}})
in
ChangedType
  • Une petite remarque en passant : attention avec les CSV, pour le moment l’interface de Power Query ne permet pas de gérer les séparateurs différents de la virgule, ni nos encodages européens facilement. Pour se faire, il faut modifier sa formule source comme suit:
Csv.Document(File.Contents("C:\Users\Florian\Desktop\2006.csv"), null, ";" , null, 1252)

Avec : votre séparateur, et votre encodage (par défaut 1252 sur Windows, voir le PDF de référence pour les autres – recherchez TextEncoding).

Et après ?

Soit on utilise directement le tableau résultat des Queries Append dans un tableau croisé dynamique, soit on l’ajoute au Data Model et c’est parti pour Power Pivot !

Amusez-vous bien, et n’hésitez pas à venir partager vos trouvailles 😉

13 commentaires sur « Mettre bout à bout (enchainer) des sources de données dans Excel avec Power Query (Data Explorer) »

  1. Bonjour Florian, merci pour cet article, je débute pour ce qui est « Power Query » et ça reste tout de même assez clair! La seule zone d’ombre et le moment où je suis perdue c’est une fois que j’ai « enabled » le Fast Combine, l’étape après. En effet dans ma barre de formule je n’ai pas le « let » ni le « in » et je ne comprends pas pourquoi tu redirige vers « Source »

    Au moment où je veux écrire la formule, je ne peux pas aller à la ligne
    Je suis un peu larguée en somme.

    Pour le moment je fais des essais pour commencer à me familiariser avec l’outil.
    Mais l’idée c’est que j’ai un fichier « source » qui va engranger quotidiennement des données, je veux que ça s’automatise juste en ouvrant le fichier plutôt que de faire Copy/paste tous les jours pour envoyer les données vers le fameux fichier « source ». Je me suis dit que cette solution avec Power query était adéquate mais pour le moment je n’arrive pas à voir comment je vais faire pour « relier » ces deux fichiers entre eux. Et c’est un peu comme si je cherchais la lumière au bout du tunnel !

    Je ne suis pas non plus une pro d’excel. On m’a parlé de « macro » mais lorsque je cherche sur le net des infos des exemples et mises en situation… ça ne correspond pas à ce que je recherche ! ou bien je ne vois pas comment l’utiliser dans mon cas !

    Merci pour tout commentaire qui pourrait m’éclairer ne serait-ce qu’un peu !!! 🙂

    Réponse

    1. Bonjour Mag!

      C’est très bien d’avoir pensé à Power Query dans ce scénario, il est fait pour ça 🙂

      Dans ton cas, je me demande si tu as bien activé le mode avancé. C’est lui qui fait apparaître le petit parchemin et qui permet de jouer avec les lignes de commande.

      Pour réprendre il va te falloir:
      – Dans l’onglet Power Query, aller dans Options, et valider que tu as bien coché le mode avancé.
      – Ensuite importer des données depuis un fichier CSV.
      – Une fois la Query ouverte, passer en mode avancé (parchemin), et corriger la ligne d’import comme indiqué à la toute fin de l’article : Csv.Document(File.Contents(« xxx.csv »), null, « ; » , null, 1252)

      Si tu as bien fait toutes les étapes tu devrais avoir un bon résultat. Je t’ai fait un petit GIF de démo juste ici: https://fleid.files.wordpress.com/2013/10/power-query-csv.gif

      N’hésite pas à m’envoyer des captures d’écran par mail si besoin (l’adresse est à droite sur le site) 🙂

      Réponse

      1. J’ai bel et bien activé le mode avancé, je vois le parchemin également, c’est juste les noms de chemin, je ne sais pas comment les rédiger (en plus je ne peux pas aller à la ligne dans cette fenêtre !)

  2. Tu as bien cliqué sur le parchemin, cela doit normalement ouvrir une nouvelle fenêtre?

    C’est un peu contre-intuitif, vu qu’on a l’habitude de saisir directement dans la ligne de formule (« f(x)=… »), mais pour Power Query il faut bien cliquer sur le parchemin et ça doit ouvrir la fenêtre avancée.

    Réponse
  3. Ping: Aspirer des données depuis un site web avec Excel, Power Query et Kimono | La BI ça vous gagne!

  5. Bonjour, j’ai telecharger PowerQuery, maintenant, j’aimerais pouvoir l’integrer a Excel

    Réponse

  6. Bonjour Florian,

    je débute sur Power Query et j’ai des difficultés à créer une base consolidant des données de plusieurs classeurs fermés.
    Je m’explique : j’ai environ 12 classeurs composés des mêmes colonnes (que j’ai référencés à l’aide d’une table excel pour avoir quelque chose dynamique).

    Je voudrais récupérer les données de ces classeurs dans une seule et unique table grâce à Power Query (c’est faisable avec des formules, mais torp lourd, risque de pertes de données si les classeurs ne sont pas ouverts, formules fragiles, etc.).

    Je n’arrive pas à modifier la requête de telle sorte que Power Query combine mes différentes tables de mes différents classeurs.

    Merci d’avance de ton retour et du chemin dans lequel tu pourrais me diriger.

    ps : ci joint ce que j’obtiens dans ma requête

    let
    Source = Excel.Workbook(File.Contents(« C:\Users\quentin.schultz\Desktop\Test Fichier Révision Budgétaire\Budget V2-V0_AD Test.xlsx »), null, true),
    Tab_AD_Table = Source{[Item= »Tab_AD »,Kind= »Table »]}[Data],
    # »Type modifié » = Table.TransformColumnTypes(Tab_AD_Table,{{« Nom du centre de coût », type text}, {« Code budgétaire », type text}, {« Libellé SAP », type text}, {« Destination Niv1 », type text}, {« Destination Niv2 », type text}, {« ARC 2014 », type number}, {« Prestations Externalisées V1 2015 », Int64.Type}, {« Frais de déplacement et de réception (yc location de salle) V1 2015 », Int64.Type}, {« Véhicules (Location & entretien) V1 2015 », Int64.Type}, {« Documentation, impression, Fournitures & Petit Equipement V1 2015 », Int64.Type}, {« Budget V1 2015 sur ordres », Int64.Type}, {« OPEX V1 2015 sur EOTP », Int64.Type}, {« Total V1 2015 », Int64.Type}, {« CAPEX V1 2015 », Int64.Type}, {« Budget en cours #(lf)2015 », Int64.Type}, {« OPEX en cours#(lf)2015 sur EOTP », Int64.Type}, {« CAPEX en cours #(lf) 2015 sur EOTP », Int64.Type}, {« Engagement sur Budget en cours », type number}, {« Engagement sur OPEX », Int64.Type}, {« Engagement sur CAPEX », Int64.Type}, {« Prestations Externalisées V2 2015 », Int64.Type}, {« Frais de déplacement et de réception (yc location de salle) V2 2015 », type any}, {« Véhicules (Location & entretien) V2 2015 », type any}, {« Documentation, impression, Fournitures & Petit Equipement V2 2015 », type any}, {« Budget V2 2015 sur ordres », Int64.Type}, {« OPEX V2 2015 sur EOTP », type any}, {« Total V2 2015 », Int64.Type}, {« CAPEX V2 2015 », type any}, {« Budget disponible », type number}, {« % Engagement », type number}, {« Prestations Externalisées V0 2016 », Int64.Type}, {« Frais de déplacement et de réception (yc location de salle) V0 2016 », type any}, {« Véhicules (Location & entretien) V0 2016 », type any}, {« Documentation, impression, Fournitures & Petit Equipement V0 2016 », type any}, {« Budget V0 2016 sur ordres », Int64.Type}, {« OPEX V0 2016 sur EOTP », type any}, {« Total V0 2016 », Int64.Type}, {« CAPEX V0 2016 », type any}, {« AEE 2016″, Int64.Type}})

    in
    # »Type modifié »

    il faudrait adapter cela à un autre fichier et remplacer « AD » par « DRI » (le fichier à la même structure, même pour la table nommée « Tab_DRI »)

    Réponse

    1. Hello!

      Malheureusement je ne pourrai pas t’être d’une grande aide en ce moment.

      Si je comprends bien, tu veux paramétrer une intégration de fichiers de même structure. C’est à dire que tu sais faire l’opération à la main, de façon unitaire (2 fichiers ensemble, 3 fichiers ensemble, comme dans cet article), mais que tu voudrais abstraire ça derrière une liste à laquelle tu pourrais ajouter des noms de fichier pour que Power Query les prennent automatiquement en compte dans l’intégration, sans avoir à tout refaire à chaque fois?

      Si c’est bien le cas, sache que c’est une opération relativement complexe, qui peut peut-être se faire à base de paramètre (cherche « power query parameters » dans google), soit en pilotage VBA (cherche « power query VBA »). Dans tous les cas bon courage, et désolé de ne pouvoir t’aider plus!

      Réponse

      1. Bonjour,

        En fait j’aimerai reprendre le scénario 2 de ton billet : plusieurs classeurs, plusieurs sources (plages nommées);
        et tout consolider dans un seul classeur sans pour autant devoir charger chaque classeur. En gros ; un onglet dans un classeur vierge, qui est une liste de toutes les classeurs (union), que l’on peut rafraichir d’un simple clic (Actualiser)

        Pour essayer de faire simple, voila ce que j’obtiens pour une plage de données (Table Excel nommée Tab_AD] dans un classeur :

        let
        Source = Excel.Workbook(File.Contents(« C:\Users\quentin.schultz\Desktop\Test Fichier Révision Budgétaire\Budget V2-V0_AD Test.xlsx »), null, true),
        Tab_AD_Table = Source{[Item= »Tab_AD »,Kind= »Table »]}[Data]
        in
        Tab_AD_Table

        J’aimerai avoir quelque chose, pour une table supplémentaire nommée « Tab_DJU », dont le chemin du fichier est le même mais le nom se terminant par « DJU »
        let
        Source = Excel.Workbook(File.Contents(« C:\Users\quentin.schultz\Desktop\Test Fichier Révision Budgétaire\Budget V2-V0_AD Test.xlsx »), null, true),
        Tab_AD_Table = Source{[Item= »Tab_AD »,Kind= »Table »]}[Data]

        union

        Source1 = Excel.Workbook(File.Contents(« C:\Users\quentin.schultz\Desktop\Test Fichier Révision Budgétaire\Budget V2-V0_DJU Test.xlsx »), null, true),
        Tab_DJU_Table = Source1{[Item= »Tab_DJU »,Kind= »Table »]}[Data]

        in
        SourceCombine ({Source, Source1})

        Mais je ne sais pas quel « terme » utiliser ni comment construire la requête.

        Merci d’avance

      2. Je suis désolé mais comme mentionné plus tôt, je ne peux pas t’aider. En effet je suis en plein déménagement à l’international 🙂
        Je te conseille de demander de l’aide auprès de Paul (http://methodidacte.fr/powerpivotfr/), il devrait pouvoir trouver une solution.
        Courage!

  7. Bonjour !

    Au final j’ai trouvé une solution intermédiaire :
    je charge chaque table de données mais sans l’insérer en temps que feuille dans le classeur, puis je fusionne toutes ces tables en une seule.

    Merci pour tout

    Réponse

Répondre à Pierre Erol GIRAUDY Annuler la réponse.