Forum Discussion
PDF header inconstistency across pages
It appears that you don't have any header rows. Were they taken out in the transform process or do they not exist? I would first select only the tables, and get rid of the pages. Then you should be able to promote the headers. Then you can choose the column names that you want to keep. Having the table with the greatest number of columns in the first row means that none of the subsequent tables' column names will be missing. After that, try to filter out any tables that you don't need--you know how sometimes on PDFs there will be some random three column table that you don't want as part of your dataset. Assuming the data you need is in table format in the PDFs, you should be able to filter to just the tables you need. Sometimes it's a pattern like every third table--you'll figure that part out. My point is that you need to get some headers in those tables before you expand them.
If there are missing fields when you reorder, the MissingFields.Ignore parameter handles that.
These are the steps I always take when combining PDFs.
--Nate
True but now that you know that you are going to have all of the columns, you can just (before you expand the tables) you can either add a column or transform the existing table column like
= Table.AddColumn(PriorStepOrTableName, "Ordered", each Table.ReorderColumns(Table.PromoteHeaders([TableColumnName]), {"Column1", "Column2", "Column3"}, MissingFields.Ignore))
So you have to sort by number of columns, then promote the headers of the tables, and finally re-order the columns. Then expand the tables.
The purpose of sorting descending by number of columns then promoting headers is to make sure that the table with the most columns can be at the top of the list of tables, thus ensuring that there are names for all of your columns when you expand.
--Nate
Hi Nate,
I am not sure if I understood you correctly. But in actually scenario there are around more than 10 pages, and unwanted columns(Columns with null values) can appear at any position. how does the reordering technique work when the position of columns with null value are uncertain? Would you be so kind to explain further.
The table with most columns -- there are more columns because those are unwanted columns which has to be removed. Eg: Column 8 in Page 14 is extra and is unwanted. So I dont understand the need for sorting also.
It appears that you don't have any header rows. Were they taken out in the transform process or do they not exist? I would first select only the tables, and get rid of the pages. Then you should be able to promote the headers. Then you can choose the column names that you want to keep. Having the table with the greatest number of columns in the first row means that none of the subsequent tables' column names will be missing. After that, try to filter out any tables that you don't need--you know how sometimes on PDFs there will be some random three column table that you don't want as part of your dataset. Assuming the data you need is in table format in the PDFs, you should be able to filter to just the tables you need. Sometimes it's a pattern like every third table--you'll figure that part out. My point is that you need to get some headers in those tables before you expand them.
If there are missing fields when you reorder, the MissingFields.Ignore parameter handles that.
These are the steps I always take when combining PDFs.
--Nate