Adding Columns for section title in a PDF

[DRSteele]

Can someone please help me?

I used PQ to import a PDF (this one). I want to create two new columns, one for all the broad section headings (e.g., CANADIAN STOCKS) and another for the all sub-headings (e.g., Broad Market). Like this example I manually manipulated in Excel:

How can I do that in PQ?

 

[sandy666]

maybe use Conditional Columns with Contain from Fund Name then FillUp / FillDown (what will be easier for you) and after all filter null from second column
this is for CANADIAN STOCKS and Broad Market only. You need define more for each Conditional Column

let
    Source = Pdf.Tables(File.Contents("path_to\performance-can-en-ca.pdf"), [Implementation="1.1"]),
    Table001 = Source{[Id="Table001"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true]),
    #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "CANADIAN STOCKS", each if Text.Contains([Fund Name], "CANADIAN") then [Fund Name] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Broad Market", each if Text.Contains([Fund Name], "Broad Market") then [Fund Name] else null)
in
    #"Added Conditional Column1"

 

[sandy666]

some should be contains but some equals. Each mistake can be easy fixed

let
    Source = Pdf.Tables(File.Contents("C:\Users\shade\Desktop\performance-can-en-ca.pdf"), [Implementation="1.1"]),
    Table001 = Source{[Id="Table001"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true]),
    #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "CANADIAN STOCKS", each if Text.Contains([Fund Name], "CANADIAN") then [Fund Name] else if Text.Contains([Fund Name], "U.S. STOCKS") then [Fund Name] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Broad Market", each if Text.Contains([Fund Name], "Broad Market") then [Fund Name] else if [Fund Name] = "Dividend" then [Fund Name] else if Text.Contains([Fund Name], "Factor") then [Fund Name] else if [Fund Name] = "ESG" then [Fund Name] else if Text.Contains([Fund Name], "Sectors") then [Fund Name] else null)
in
    #"Added Conditional Column1"

 

[DRSteele]

Sandy, thanks for your help.

That code does not really work. Even if it did, it requires user input. Next time I download that PDF and try to parse it the headings may have changed entirely - there might be new ones, altered ones, deleted ones.

What I need is a more general answer so that I can apply it to other PDFs. The PDF I used as an example has two annoying section headings (one broad one and the other a sub-heading) that span all the columns, which might be handy given that all the other columns in those rows are blanks.

I really hope this can be done.

 

[sandy666]

result of the code above (small part)

CANADIAN STOCKS	Broad Market	Fund Name	Ticker
			Symbol
CANADIAN STOCKS	null	CANADIAN STOCKS	null
null	Broad Market	Broad Market	null
null	null	iShares Core S&P/TSX Capped Composite Index ETF	XIC
null	null	iShares S&P/TSX 60 Index ETF	XIU
null	null	iShares S&P/TSX Completion Index ETF	XMD
null	null	iShares S&P/TSX SmallCap Index ETF	XCS
null	Dividend	Dividend	null
null	null	iShares Core MSCI Canadian Quality Dividend Index ETF	XDIV
null	null	iShares S&P/TSX Composite High Dividend Index ETF	XEI
null	null	iShares Canadian Select Dividend Index ETF	XDV
null	null	iShares S&P/TSX Canadian Dividend Aristocrats Index ETF	CDZ
null	Factor	Factor	null
null	null	iShares MSCI Min Vol Canada Index ETF	XMV
null	null	iShares MSCI Multifactor Canada Index ETF	XFC
null	null	iShares Canadian Growth Index ETF	XCG
null	null	iShares Canadian Value Index ETF	XCV
null	null	iShares Canadian Fundamental Index ETF	CRQ
null	ESG	ESG	null
null	null	iShares ESG Advanced MSCI Canada Index ETF	XCSR
null	null	iShares ESG Aware MSCI Canada Index ETF	XESG
null	null	iShares Jantzi Social Index ETF	XEN
null	Sectors	Sectors	null
null	null	iShares Equal Weight Banc & Lifeco ETF	CEW
null	null	iShares S&P/TSX Capped Consumer Staples Index ETF	XST
null	null	iShares S&P/TSX Capped Energy Index ETF	XEG
null	null	iShares S&P/TSX Capped Financials Index ETF	XFN
null	null	iShares S&P/TSX Capped Information Technology Index ETF	XIT
null	null	iShares S&P/TSX Capped Materials Index ETF	XMA
null	null	iShares S&P/TSX Capped REIT Index ETF	XRE
null	null	iShares S&P/TSX Capped Utilities Index ETF	XUT
U.S. STOCKS	null	U.S. STOCKS	null
null	Broad Market	Broad Market	null

did you "import" this pdf?

 

[DRSteele]

That does not match the sample I provided. Each row needs a Type and a Style and the original headings need to be removed.

 

[sandy666]

it doesn't matter, you can define headers as you want in Conditional Column, btw you didn't read post#2

how well do you know Power Query?

maybe it will help

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[DRSteele]

It does matter. Each fund has to have two new columns attached, one that says CANADIAN STOCKS, for example, and one that says DIVIDEND, for example. And the rows those field cam from must be deleted.

 

[sandy666]

eg. you need 2 new columns: Type and Style so name Conditional Columns suitably (post#7), define all things what you need in these columns then fill these columns Down then use filter for null on Ticker column. If in Type and Style will be any null value use FillUp
so... it doesn't matter
I gave you an example only (working for me)

 

[DRSteele]

I don't want every single record in the new Type column to say CANADIAN STOCKS. The heading changes to U.S.Stocks, then to INTERNATIONAL STOCKS, then to ... well, could be anything. The point is that it changes. The same goes for the new Style columns. I can't just fill on down. It has to fill down a word a correct number of times and then fill down a different word.