{"id":1438,"date":"2023-10-30T16:44:51","date_gmt":"2023-10-30T15:44:51","guid":{"rendered":"http:\/\/declick.be\/?page_id=1438"},"modified":"2024-12-02T14:04:00","modified_gmt":"2024-12-02T13:04:00","slug":"11-excel-power-query","status":"publish","type":"page","link":"https:\/\/declick.be\/index.php\/11-excel-power-query\/","title":{"rendered":"11. Excel &#8211; Power Query"},"content":{"rendered":"\n<p>La fiche programme du cours se trouve ici: <a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/Excel-Power-Query-1j-Fiche-programme.docx\">Excel Power Query &#8211; 1j &#8211; Fiche programme<\/a><\/p>\n\n\n\n<p>Ce cours est une initiation \u00e0 l&rsquo;\u00e9tude de l&rsquo;utilisation de <strong>Power Query<\/strong>.<\/p>\n\n\n\n<p>    Cet outil puissant de type <strong>ETL (Extract, Transform, Load)<\/strong> permet :<\/p>\n\n\n<ol>\n<li>\n<p>De connecter et r\u00e9cup\u00e9rer des donn\u00e9es sous diff\u00e9rents formats via l&rsquo;interface Power Query.<\/p>\n<\/li>\n<li>\n<p>Les donn\u00e9es connect\u00e9es peuvent alors \u00eatre retravaill\u00e9es de fa\u00e7on \u00e0 les rendre plus propices \u00e0 un traitement correct en leur donnant une structure et un format ad\u00e9quats.<\/p>\n<\/li>\n<li>\n<p>Les donn\u00e9es \u00ab\u00a0nettoy\u00e9es\u00a0\u00bb peuvent alors \u00eatre charg\u00e9es sur des feuilles de calcul sous forme de tableaux de donn\u00e9es.<\/p>\n<\/li>\n<li>\n<p>Ces m\u00eames tableaux pourront alors servir \u00e0 tout traitement ult\u00e9rieur de donn\u00e9es connect\u00e9es \u00e0 l&rsquo;aide des tableaux crois\u00e9s dynamiques notamment.<\/p>\n<\/li>\n<\/ol>\n<p>Ce cours permet un apprentissage par l&rsquo;exemple. Ceci se fera par l&rsquo;interm\u00e9diaire de la r\u00e9alisation d&rsquo;applications se trouvant dans les fichiers t\u00e9l\u00e9chargeables ci-dessous.<\/p>\n<p><span style=\"font-size: revert; color: initial;\">Les fichiers qui suivent sont sp\u00e9cifiques \u00e0 l&rsquo;utilisation de Power Query. Ils doivent \u00eatre t\u00e9l\u00e9charg\u00e9s dans le dossier c:\\Power Query pour que les solutions puissent fonctionner<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2024\/11\/PowerQuery-Exercice01.xlsx\">PowerQuery &#8211; Exercice01.xlsx<\/a> : Exemples de <strong><span style=\"text-decoration: underline;\">diff\u00e9rents connexions via Power Query<\/span><\/strong> : CSV, XLSX, Tableau de donn\u00e9es, Site Web, OneDrive perso, One Drive Organisation (SharePoint), Fichier Teams, Dossier SharePoint<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"padding-left: 80px;\">Et les fichiers contenant les donn\u00e9es :<\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic2.xlsx\">PowerQuery &#8211; Exercice01-Applic2.xlsx<\/a><\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic3.pdf\">PowerQuery &#8211; Exercice01-Applic3.pdf<\/a><\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic4.png\">PowerQuery &#8211; Exercice01-Applic4.png<\/a><\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic6.xlsx\">PowerQuery &#8211; Exercice01-Applic6.xlsx<\/a><\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic7.xlsx\">PowerQuery &#8211; Exercice01-Applic7.xlsx<\/a><\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic7.xlsx\">PowerQuery &#8211; Exercice01-Applic8.xlsx<\/a><\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic9-Clients1.xlsx\">PowerQuery &#8211; Exercice01-Applic9 &#8211; Clients1.xlsx<\/a><\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-Applic9-Clients2.xlsx\">PowerQuery &#8211; Exercice01-Applic9 &#8211; Clients2.xlsx<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>\u00c9tudes de <span style=\"text-decoration: underline;\"><strong>diff\u00e9rents cas de transformation de donn\u00e9es<\/strong><\/span> :<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>Une <a href=\"https:\/\/declick.be\/wp-content\/uploads\/2023\/12\/PowerQuery-VideoJP.mp4\" target=\"_blank\" rel=\"noopener\">petite vid\u00e9o<\/a> faite par un grand homme qui nous a quitt\u00e9s &#8230; pour une retraite au soleil. Merci JP<\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2024\/11\/PowerQuery-Exercice02-Applic1.xlsx\">PowerQuery-Exercice02-Applic1.xlsx<\/a> : Exercice de r\u00e9organisation de donn\u00e9es et de cr\u00e9ation d&rsquo;op\u00e9rations de synth\u00e8se sur ces donn\u00e9es r\u00e9organis\u00e9es<\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2024\/11\/PowerQuery-Exercice02-Applic2.xlsx\">PowerQuery-Exercice02-Applic2.xlsx<\/a> : Nettoyage de donn\u00e9es (issues de tableaux Wikip\u00e9dia) et combinaison de tableaux<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2024\/12\/PowerQuery-Exercice03-1-1.xlsx\">PowerQuery &#8211; Exercice03.xlsx<\/a> : Exercices divers de nettoyage et de r\u00e9organisation de donn\u00e9es<\/li>\n<li><a href=\"https:\/\/declick.be\/wp-content\/uploads\/2024\/04\/PowerQuery-Exercice04.xlsm\">PowerQuery &#8211; Exercice04.xlsm<\/a> : R\u00e9alisation d&rsquo;une application de connexion \u00e0 une base de donn\u00e9es, transformation des donn\u00e9es, ainsi que les traitements finaux sur ces donn\u00e9es (filtres avanc\u00e9s, TCD, automatisation par macro)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><!-- \/wp:post-content --><\/p>\n<p><!-- wp:file {\"id\":1527,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-3.xlsx\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:file {\"id\":1534,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2023\/11\/PowerQuery-Exercice01-4.xlsx\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:file {\"id\":1541,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2023\/12\/PowerQuery-Exercice01-1.xlsx\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:file {\"id\":1556,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2023\/12\/PowerQuery-VideoJP.mp4\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:file {\"id\":1594,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2023\/12\/PowerQuery-Exercice02-Applic2.xlsx\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:file {\"id\":1604,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2023\/12\/PowerQuery-Exercice04-1.xlsm\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:file {\"id\":1678,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2024\/04\/PowerQuery-Exercice04.xlsm\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:file {\"id\":1925,\"href\":\"https:\/\/declick.be\/wp-content\/uploads\/2024\/11\/PowerQuery-Exercice03.xlsx\"} --><\/p>\n<p><!-- \/wp:file --><\/p>\n<p><!-- wp:paragraph --><\/p>\n<p><!-- \/wp:paragraph --><\/p>","protected":false},"excerpt":{"rendered":"<p>La fiche programme du cours se trouve ici: Excel Power Query &#8211; 1j &#8211; Fiche programme Ce cours est une initiation \u00e0 l&rsquo;\u00e9tude de l&rsquo;utilisation de Power Query. Cet outil puissant de type ETL&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1438","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/pages\/1438","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/comments?post=1438"}],"version-history":[{"count":88,"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/pages\/1438\/revisions"}],"predecessor-version":[{"id":1936,"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/pages\/1438\/revisions\/1936"}],"wp:attachment":[{"href":"https:\/\/declick.be\/index.php\/wp-json\/wp\/v2\/media?parent=1438"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}