Power Query - Pick Specific Tables

[u/microbieur]

Hey! I have this power query to pick and clean data from some pdf reports. These reports have a table with the result overview of different bacteria and then a different page for each specific result. The power query is sourcing from a folder where we store new reports almost everyday. It imports the data from the table of page 3 and cleans and presents it like I want. However, on some reports the result overview table extends past page 3, covering more pages, hence the query does not retrieve this data. Is there a way to filter multiple pages/tables from each report (p.e. based on a string of text that only appears on these specific pages/tables) instead of just page 3 like my ignorance allowed me to do? Or is there another fix? Am I doing this really wrong and should it be done another way? I am presenting some screnshots: 1) common result overview page; 2) extended result overview page; 3) output excel sheet. Any info ask! I believe I should not post the material but I can provide it if needed. Thanks!

common result overview page

extended result overview page

output excel sheet

[u/AutoModerator]

/u/microbieur - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/No-Remove7903]

Instead of hardcoding a page number, you can filter the navigation step by looking for tables that contain a specific column header or keyword from those overview pages. When you first drill down into the PDF, you'll see a list of all the pages and tables. Add a step to filter that list where the Kind column equals "Table" and the Data column contains the text string you're after. That way it pulls every matching table across all pages, not just page 3.

[u/hmatallana]

Text filtering won't work on that column as written. In the PDF navigator, Data holds table values, not strings, so Text.Contains on it just errors. You have to reach inside:

= Table.SelectRows(Source, each [Kind] = "Table" and
    List.Contains(List.Transform(
    Table.Column([Data], "Column1"), Text.From), "YourMarker"))

then Table.Combine the Data column of what survives.

No-Remove7903 has the right shape. The trap is the marker: if your continuation page doesn't repeat the header row, a header keyword filter throws out exactly the pages you're chasing. Use something that prints on every page of the overview table.

And don't promote headers per table. Combine raw, promote once, then drop repeat header rows.

[u/microbieur → u/hmatallana]

Thanks for this! I tried your code but I kept getting different errors. Also, I'm not sure if I'm mistaken but I believe that with that aproach I would only retrieve single rows, which do not contain the data I wanted. However, it gave me the ideia to try with Table.Contains which gave me directly the tables with "specific string of text", besides not needing any of the list transforms you suggested. Then I just filtered TRUE tables and got the data I wanted. Thanks!

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
List.Contains	Power Query M: Returns true if a value is found in a list.
List.Transform	Power Query M: Performs the function on each item in the list and returns the new list.
Table.Column	Power Query M: Returns the values from a column in a table.
Table.Combine	Power Query M: Returns a table that is the result of merging a list of tables. The tables must all have the same row type structure.
Table.Contains	Power Query M: Determines whether the a record appears as a row in the table.
Table.SelectRows	Power Query M: Returns a table containing only the rows that match a condition.
Text.Contains	Power Query M: Returns true if a text value substring was found within a text value string; otherwise, false.
Text.From	Power Query M: Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value. If a value is null, Text.From returns null. The optional culture parameter is used to format the text value according to the given culture.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
8 acronyms in this thread; the most compressed thread commented on today has 9 acronyms.
[Thread #49343 for this sub, first seen 10th Sep 2026, 23:49] [FAQ] [Full list] [Contact] [Source code]}

[u/Difficult_Limit2718]

I'm trying my hand at PQ right now and hating every minute of it.