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!
Hi Simon,
Here is a solution (incomplete, just the relevant steps):
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
Catalin,
That's really helpful and very well explained, so you have taught as well as fixed!
Many thanks,
Simon
