Excel Forum

Solution for dirty PDF data with variable number of columns?

Simon Smith

Member

Members

Forum Posts: 29

Member Since:
May 19, 2021

Offline

December 5, 2021 - 7:38 am

Hi Mynda, Phil and fellow learners,

I'm extracting data from PDFs downloaded from https://www.morningstar.co.uk/uk/ a finance and investment website. I've hit a snag because sometimes the table I want has 6-columns and sometimes 5. The attached PDFs, table 7, BNKR-port2.pdf has 5, BGFD-port2.pdf has 6. The 'extra' column in the latter is column1 it has no useful data, In my custom function scraping the data for "Stock Sector Weightings %", which in the BGFD file is in Column2 and Cloumn5 or in BNKR Column1 and Column4 causes an error when the script can not find a Column6.

What I am trying to do is to put a step in after Source and Table007 steps to identify if Column1 is full of null and [image] then delete Column1 else do nothing and move on to the #Changed Type step.

Do you know of a way to do this using if or try? Or is there an easier path?

I hope that you are enjoying your summer, a bit cold & dark here - but your courses are a great distraction.

Kind regards,

Simon

PS - looking forwards to seeing the new PQ course videos!

See Answer

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1845

Member Since:
November 8, 2013

Offline

December 5, 2021 - 3:16 pm

Hi Simon,

Here is a solution (incomplete, just the relevant steps):

Table7Data = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
Col1= List.Distinct(List.RemoveNulls(Table7Data[Column1])),
Custom1 = if List.Count(Col1)=1 and Col1{0}="[image]" then Table.RemoveColumns(Table7Data,{"Column1"}) else Table7Data

In the step Col1, if you refer to a column from a table, you can apply list functions on that column, knowing that a column from a table is a List object, therefore Table7Data[Column1] is a list.

Use the RemoveNulls function, then Distinct function should reduce the list to only the "[image]" item, if Col1 list matches this scenario, you can remove column 1 (did that in Custom1 step above).

Col1{0} refers to the first item in list, if there is nothing in the list after removing nulls this part might generate an error, so you can avoid that with a try..otherwise statement:

Custom1 = if List.Count(Col1)=1 and (try Col1{0}="[image]" otherwise false) then Table.RemoveColumns(Table7Data,{"Column1"}) else Table7Data