Power Query get data from pdf

%3CLINGO-SUB%20id%3D%22lingo-sub-2310432%22%20slang%3D%22en-US%22%3EPower%20Query%20get%20data%20from%20pdf%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310432%22%20slang%3D%22en-US%22%3ELatest%20version%20of%20Excel%20via%20Office%20365%20on%20WINDOWS.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20Pdf%20that%20has%20a%20single%20table%20split%20into%20dozens%20of%20tables%20by%20page%20breaks.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20the%20first%20time%20I%20have%20used%20Get%20Data%20and%20Power%20Query.%20I%20selected%20the%20Pdf%20file%20and%20then%20the%20pages%20I%20wanted.%20As%20it%20is%20a%20single%20data%20table%20over%20several%20pages%2C%20the%20columns%20on%20each%20page%20are%20the%20same.%20However%2C%20individual%20tables%20may%20have%20columns%20where%20no%20data%20is%20present.%20Transform%20or%20Load%20are%20removing%20these%20columns%20when%20empty%20so%20different%20page%20loads%20have%20different%20numbers%20if%20columns%2C%20depending%20on%20whether%20that%20column%20has%20no%20values%20on%20a%20particular%20page.%20This%20means%20I%20cannot%20merge%20tables%20into%20one%20Excel%20worksheet.%20Hiw%20do%20I%20ensure%20all%20columns%20are%20represented%20on%20every%20table%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20mentioned%20earlier%2C%20I%20am%20new%20to%20Power%20Query%20so%20please%20do%20not%20assume%20I%20have%20detailed%20knowledge%20of%20this%20functionality.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2310432%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2310869%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20get%20data%20from%20pdf%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2310869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041728%22%20target%3D%22_blank%22%3E%40Stain555%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENobody%20have%20detailed%20knowledge%2C%20if%20only%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25060%22%20target%3D%22_blank%22%3E%40Guy%20Hunkin%3C%2FA%3E%26nbsp%3B.%20In%20general%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowerquery-m%2Fpdf-tables%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPdf.Tables%20-%20PowerQuery%20M%20%7C%20Microsoft%20Docs%3C%2FA%3E%26nbsp%3Bhas%20an%20option%20to%20combine%20tables%20from%20different%20pages%20which%20shall%20work%20by%20default%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20918px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277063iD6E019566CE9B422%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EHowever%2C%20I%20assume%20that's%20only%20if%20you%20have%20headers%20for%20both%20parts%20of%20the%20table.%20If%20your%20parts%20returned%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20679px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277064i0F2166E023C579DA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20496px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277065iBFAFD99F55499AD7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20append%20one%20to%20another%20to%20another%20to%20receive%20resulting%20table.%20Yes%2C%20that's%20required%20some%20manual%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Latest version of Excel via Office 365 on WINDOWS.

I have a Pdf that has a single table split into dozens of tables by page breaks.

This is the first time I have used Get Data and Power Query. I selected the Pdf file and then the pages I wanted. As it is a single data table over several pages, the columns on each page are the same. However, individual tables may have columns where no data is present. Transform or Load are removing these columns when empty so different page loads have different numbers if columns, depending on whether that column has no values on a particular page. This means I cannot merge tables into one Excel worksheet. Hiw do I ensure all columns are represented on every table?

As mentioned earlier, I am new to Power Query so please do not assume I have detailed knowledge of this functionality.

Cheers
2 Replies

@Stain555 

Nobody have detailed knowledge, if only @Guy Hunkin . In general Pdf.Tables - PowerQuery M | Microsoft Docs has an option to combine tables from different pages which shall work by default

image.png

However, I assume that's only if you have headers for both parts of the table. If your parts returned as

image.png

and

image.png

you may append one to another to another to receive resulting table. Yes, that's required some manual work.

I actually discovered, by saving the Pdf as a word document, that tables had been saved in the pdf with tabs that bypassed null columns, so, although there were column headers, Power Query was always going to build the columns from actual data. So I need to manipulate extracted data myself to insert columns where missing. That's life.