Forum Discussion
SharePoint Library extract table from pdf page for all docs
One way to do this without using the helper queries would be...
If you have your query with the 'Content' and 'Name' columns add a column using Pdf.Tables()= Table.AddColumn(yourPreviousStep, "pdf_content", each Pdf.Tables([Content]), type table)Depending on how your pdf file is structured you will see something like the following when viewing the resulting tables...
Since you want the Page number info, you would want to filter the nested tables to only include Pages.
= Table.TransformColumns(add_pdf_content, {{"pdf_content", each Table.SelectRows(_, each [Kind] = "Page"), type table}})Resulting in...
You can remove the 'Content' column as it is no longer needed.
= Table.RemoveColumns(select_nested_pdf_pages,{"Content"})Expand the 'pdf_content' column getting the 'Name' and 'Data' columns. (You can rename the 'Name' column to 'Page' at this point.)
= Table.ExpandTableColumn(remove_binary, "pdf_content", {"Name", "Data"}, {"Page", "Data"})The resulting tables require the headers to be promoted...
= Table.TransformColumns(expand_nested_pdf, {{"Data", each Table.PromoteHeaders(_), type table}})The nested tables are now ready to be expanded...
= Table.ExpandTableColumn(promote_nested_headers, "Data", {"Column1", "Column2"}, {"Column1", "Column2"})Ending up with the final result of...
Hope this helps.
One way to do this without using the helper queries would be...
If you have your query with the 'Content' and 'Name' columns add a column using Pdf.Tables()
= Table.AddColumn(yourPreviousStep, "pdf_content", each Pdf.Tables([Content]), type table)Depending on how your pdf file is structured you will see something like the following when viewing the resulting tables...
Since you want the Page number info, you would want to filter the nested tables to only include Pages.
= Table.TransformColumns(add_pdf_content, {{"pdf_content", each Table.SelectRows(_, each [Kind] = "Page"), type table}})Resulting in...
You can remove the 'Content' column as it is no longer needed.
= Table.RemoveColumns(select_nested_pdf_pages,{"Content"})Expand the 'pdf_content' column getting the 'Name' and 'Data' columns. (You can rename the 'Name' column to 'Page' at this point.)
= Table.ExpandTableColumn(remove_binary, "pdf_content", {"Name", "Data"}, {"Page", "Data"})The resulting tables require the headers to be promoted...
= Table.TransformColumns(expand_nested_pdf, {{"Data", each Table.PromoteHeaders(_), type table}})The nested tables are now ready to be expanded...
= Table.ExpandTableColumn(promote_nested_headers, "Data", {"Column1", "Column2"}, {"Column1", "Column2"})Ending up with the final result of...
Hope this helps.
Thanks for the well laid out steps. It leads me to some new questions.
What is the difference of
Pdf.Tables([Content]), type table)and
#"Transform File"([Content]))
I will look into on the webs but for continuity, I ask here.
Also what is going on the makes the Promoted Headers necessary? Each file had a specific name for Column3, most likely a header for the Document ID. Thankfully, Power Query seemed to ignore it, which worked out in my favor. I was worried I might have 100s of unique columns for column 3. š±