PowerQuery - PDF file with varying number of columns

[Spinoza]

Hi everyone !

I have a problem with a very simple Query that I'v made using PowerQuery. Let me explain the Query...

- I import a PDF file that contains normally 6 columns, one columns contains the name of the account while the 5 others contains financial data for the last 5 fiscal year.
- When I refresh the data with another PDF file that contains 5 fiscal years, I do not have any problem, my Query works perfectly.
- You see me coming ? My problem is when my a refresh the data with a PDF file that contains less than 5 fiscal years, I got an error message that I missed column #6.

Anybody could help me to resolve that issue please ?

Thanks a lot and wonderful day to you !

Francis

 

[John_w]

You could use the Table.SelectColumns command with the MissingField.Ignore parameter, something like this:

let   
    Source = Pdf.Tables(File.Contents("C:\path\to\Data.pdf"), [Implementation="1.3"]),
    Page1 = Source{[Id="Page001"]}[Data],
    Table1 = Table.SelectColumns(Page1,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, MissingField.Ignore),
    Table2 = Table.PromoteHeaders(Table1, [PromoteAllScalars=true])
in
    Table2