{"id":2338,"date":"2021-07-22T22:35:19","date_gmt":"2021-07-22T22:35:19","guid":{"rendered":"https:\/\/portfolio.accrualhub.com\/powergi2\/?p=621"},"modified":"2026-05-14T16:35:09","modified_gmt":"2026-05-14T16:35:09","slug":"reading-pdf-data-from-excel-or-power-bi-with-power-query","status":"publish","type":"post","link":"https:\/\/powergi.net\/de\/blog\/reading-pdf-data-from-excel-or-power-bi-with-power-query\/","title":{"rendered":"Reading PDF Data from Excel or Power BI with Power Query"},"content":{"rendered":"
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\n

We work with PDF files in day-to-day tasks, and we are in constant need of reading or processing the data in those PDFs (Bank Statements, Invoices, Exported data available in PDF format only, etc). We usually think of paid software or OCR systems to help us on this task, but did you know that both Power BI and MS Excel have an option to get data or tables from PDFs? The results aren\u2019t always perfect but with a couple of transformation steps the output can be really useful!<\/p>\n\n

For example, I receive monthly statements from by Bank with details of charges on my credit card, and I spend some time reviewing it, wouldn\u2019t it be great if I could both review it and analyze my historical transactions in Excel or Power BI?<\/p>\n

This is where solutions like Power Apps Beratungsdienste<\/strong><\/a> can further enhance automation and data processing.<\/p>\n

\u00a0<\/p>

\t\t
\n\t\t\t
\n\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Verwandeln Sie Ihre Ideen in digitale L\u00f6sungen<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

Unser Team leitet Sie Schritt f\u00fcr Schritt beim Erstellen benutzerdefinierter Apps in Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n\n

Step by step guide how to read PDF data with Power Query.<\/h3>\n\n

Note: Steps are very similar between Power BI and MS Excel.<\/p>\n\n

Go to data tab, then Get Data > From File > From PDF.<\/p>\n\n

Step1<\/strong>: Browse to the PDF file you want to read, select it, and click Import.<\/p>\n\n

\"\"<\/figure>\n\n

Step2:<\/strong> In the next screen you will both the pages and the tables identified by Power Query, you can select one or multiple items, depending on the analysis you\u2019re building.<\/p>\n\n

\"\"<\/figure>\n\n

Step3:<\/strong> Select the item and in the right, you\u2019ll see a preview of the data recognized for that table. When you find the table you\u2019re interested in, just select it and click on \u201ctransform data\u201d<\/p>\n\n

\"\"<\/figure>\n\n

This will take you to the query editor, you will notice some columns are showing split into<\/strong><\/a> two, in below case, Power Query recognized the transaction description in two columns instead of 1. So we need to aply a quick transformation step<\/p>\n\n

\"\"<\/figure>\n\n

Step4:<\/strong> Perform a \u201cmerge column\u201d operations to data put into one single column, by selecting both columns, then right click, and finally clicking on \u201cMerge Columns\u201d.<\/p>\n\n

\"\"<\/figure>\n\n
\"\"<\/figure>\n\n

Step5:<\/strong> Remove any nulls from the Date column to clean up some rows<\/p>\n\n

\"\"<\/figure>\n\n

Step6:<\/strong> click \u201cClose and Load\u201d<\/p>\n\n

\"\"<\/figure>\n\n

Wait a couple of seconds until the data has loaded to the Sheet.<\/p>

\t\t
\n\t\t\t
\n\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Automatisieren Sie Aufgaben, die Sie verlangsamen<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

Geben Sie Ihrem Team mehr Zeit und erm\u00f6glichen Sie ihm, sich auf strategische Aufgaben zu konzentrieren, dank digitaler und robotergest\u00fctzter Automatisierung.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n\n

You just read data from a PDF! Very easy and convenient, isn\u2019t it!<\/p>\n

This is where solutions like Power Platform Beratungsleistungen<\/strong><\/a> can further enhance automation and data processing. Our consultants specialize in streamlining tasks, making your data journey efficient and insightful<\/p>\n

\u00a0<\/p>\n\n

Benefits of Reading PDF data from excel with Power Query<\/h3>\n\n
    \n
  • It\u2019s for free: the functionality is available in all the recent versions of Excel at no additional cost, and no additional software.<\/li>\n
  • You can read several pages at the same time by expanding all pages in the navigation.<\/li>\n
  • You can apply calculations if needed to any column or data point present in the file.<\/li>\n
  • You can transform the text into different final formats using all the Power Query features.<\/li>\n
  • It\u2019s very simple: just a couple of clicks and transformations, and you\u2019re done!<\/li>\n<\/ul>\n\n

    \u00a0<\/p>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>","protected":false},"excerpt":{"rendered":"

    We work with PDF files in day-to-day tasks, and we are in constant need of reading or processing the data in those PDFs (Bank Statements, Invoices, Exported data available in PDF format only, etc). We usually think of paid software or OCR systems to help us on this task, but did you know that both […]<\/p>\n","protected":false},"author":2,"featured_media":2621,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"glossary_letter":"a","footnotes":""},"categories":[35,29,25],"tags":[],"class_list":["post-2338","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-excel","category-power-bi-cat","category-power-query-cat"],"glossary_letter":"a","rank_math_title":"Read PDF Data via Excel & Power BI | Power GI","rank_math_description":"Discover how to extract PDF data directly from Excel or Power BI using Power Query. Step-by-step tutorial on the Power GI blog.","rank_math_focus_keyword":null,"_links":{"self":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts\/2338","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/comments?post=2338"}],"version-history":[{"count":1,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts\/2338\/revisions"}],"predecessor-version":[{"id":13468,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/posts\/2338\/revisions\/13468"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/media\/2621"}],"wp:attachment":[{"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/media?parent=2338"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/categories?post=2338"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powergi.net\/de\/wp-json\/wp\/v2\/tags?post=2338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}