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. š±
The promote headers step is not required. I chose the wrong word. You can promote the headers at that point, but it is not required that you do so. You can delete that step and then deal with the headers afterwards if you choose.
#"Transform File"([Content]))is a function call to the "Transform File" function where the [Content] is supplied as input to the function.
The "Transform File" function likely looks like something like...
let Source = (Parameter1) => let Source = Pdf.Tables(Parameter1, [Implementation="1.3"]), Page1 = Source{[Id="Page001"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]) in #"Promoted Headers" in SourceYou can see in this case that the first row that the Pdf.Tables() function is present and would accept the input [Content]. The major difference is that the following two steps are applied before returning the output of the function to the table.
Page1 = Source{[Id="Page001"]}[Data],This step is taking data from Rows that are "Page001" only. (This is what prevents you from seeing multiple "pages" from a single file.
There is nothing stopping you from making code changes inside the "Transform File" function and continuing to use it and the rest of the helper queries. It becomes a matter of choice and asthetic preference.
On some larger models there may be performance differences, but most users are not dealing with that size of model.
Hope this helps a bit.