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]

I did all (I hope) for Type column and some for Style column, the rest you need to do yourself

Type	Style	Fund Name	Ticker Symbol
CANADIAN STOCKS	Broad Market	iShares Core S&P/TSX Capped Composite Index ETF	XIC
CANADIAN STOCKS	Broad Market	iShares S&P/TSX 60 Index ETF	XIU
CANADIAN STOCKS	Broad Market	iShares S&P/TSX Completion Index ETF	XMD
CANADIAN STOCKS	Broad Market	iShares S&P/TSX SmallCap Index ETF	XCS
CANADIAN STOCKS	Dividend	iShares Core MSCI Canadian Quality Dividend Index ETF	XDIV
CANADIAN STOCKS	Dividend	iShares S&P/TSX Composite High Dividend Index ETF	XEI
CANADIAN STOCKS	Dividend	iShares Canadian Select Dividend Index ETF	XDV
CANADIAN STOCKS	Dividend	iShares S&P/TSX Canadian Dividend Aristocrats Index ETF	CDZ
CANADIAN STOCKS	Factor	iShares MSCI Min Vol Canada Index ETF	XMV
CANADIAN STOCKS	Factor	iShares MSCI Multifactor Canada Index ETF	XFC
CANADIAN STOCKS	Factor	iShares Canadian Growth Index ETF	XCG
CANADIAN STOCKS	Factor	iShares Canadian Value Index ETF	XCV
CANADIAN STOCKS	Factor	iShares Canadian Fundamental Index ETF	CRQ
CANADIAN STOCKS	ESG	iShares ESG Advanced MSCI Canada Index ETF	XCSR
CANADIAN STOCKS	ESG	iShares ESG Aware MSCI Canada Index ETF	XESG
CANADIAN STOCKS	ESG	iShares Jantzi Social Index ETF	XEN
CANADIAN STOCKS	Sectors	iShares Equal Weight Banc & Lifeco ETF	CEW
CANADIAN STOCKS	Sectors	iShares S&P/TSX Capped Consumer Staples Index ETF	XST
CANADIAN STOCKS	Sectors	iShares S&P/TSX Capped Energy Index ETF	XEG
CANADIAN STOCKS	Sectors	iShares S&P/TSX Capped Financials Index ETF	XFN
CANADIAN STOCKS	Sectors	iShares S&P/TSX Capped Information Technology Index ETF	XIT
CANADIAN STOCKS	Sectors	iShares S&P/TSX Capped Materials Index ETF	XMA
CANADIAN STOCKS	Sectors	iShares S&P/TSX Capped REIT Index ETF	XRE
CANADIAN STOCKS	Sectors	iShares S&P/TSX Capped Utilities Index ETF	XUT
U.S. STOCKS	Broad Market	iShares Core S&P U.S. Total Market Index ETF	XUU
U.S. STOCKS	Broad Market	iShares Core S&P U.S. Total Market Index ETF	XUU.U
U.S. STOCKS	Broad Market	iShares Core S&P U.S. Total Market Index ETF (CAD-Hedged)	XUH
U.S. STOCKS	Broad Market	iShares Core S&P 500 Index ETF	XUS
U.S. STOCKS	Broad Market	iShares Core S&P 500 Index ETF	XUS.U
U.S. STOCKS	Broad Market	iShares Core S&P 500 Index ETF (CAD-Hedged)	XSP
U.S. STOCKS	Broad Market	iShares S&P U.S. Mid-Cap Index ETF	XMC
U.S. STOCKS	Broad Market	iShares S&P U.S. Mid-Cap Index ETF	XMC.U
U.S. STOCKS	Broad Market	iShares S&P U.S. Mid-Cap Index ETF (CAD-Hedged)	XMH
U.S. STOCKS	Broad Market	iShares S&P U.S. Small-Cap Index ETF	XSMC
U.S. STOCKS	Broad Market	iShares S&P U.S. Small-Cap Index ETF (CAD-Hedged)	XSMH
U.S. STOCKS	Broad Market	iShares NASDAQ 100 Index ETF (CAD-Hedged)	XQQ
U.S. STOCKS	Broad Market	iShares U.S. Small Cap Index ETF (CAD-Hedged)	XSU
U.S. STOCKS	Dividend	iShares Core MSCI US Quality Dividend Index ETF	XDU
U.S. STOCKS	Dividend	iShares Core MSCI US Quality Dividend Index ETF	XDU.U
U.S. STOCKS	Dividend	iShares Core MSCI US Quality Dividend Index ETF (CAD-Hedged)	XDUH
U.S. STOCKS	Dividend	iShares U.S. High Dividend Equity Index ETF	XHU
U.S. STOCKS	Dividend	iShares U.S. High Dividend Equity Index ETF (CAD-Hedged)	XHD
U.S. STOCKS	Dividend	iShares US Dividend Growers Index ETF (CAD-Hedged)	CUD
U.S. STOCKS	Factor	iShares MSCI Min Vol USA Index ETF	XMU
U.S. STOCKS	Factor	iShares MSCI Min Vol USA Index ETF	XMU.U
U.S. STOCKS	Factor	iShares MSCI Min Vol USA Index ETF (CAD-Hedged)	XMS
U.S. STOCKS	iShares MSCI USA Momentum Factor Index ETF	iShares MSCI USA Momentum Factor Index ETF	XMTM
U.S. STOCKS	iShares MSCI USA Quality Factor Index ETF	iShares MSCI USA Quality Factor Index ETF	XQLT
U.S. STOCKS	iShares MSCI USA Value Factor Index ETF	iShares MSCI USA Value Factor Index ETF	XVLU
U.S. STOCKS	iShares MSCI USA Value Factor Index ETF	iShares MSCI Multifactor USA Index ETF	XFS
U.S. STOCKS	iShares MSCI USA Value Factor Index ETF	iShares MSCI Multifactor USA Index ETF	XFS.U
U.S. STOCKS	iShares MSCI USA Value Factor Index ETF	iShares MSCI Multifactor USA Index ETF (CAD-Hedged)	XFA
U.S. STOCKS	iShares MSCI USA Value Factor Index ETF	iShares US Fundamental Index ETF	CLU
U.S. STOCKS	iShares MSCI USA Value Factor Index ETF	iShares US Fundamental Index ETF	CLU.C
U.S. STOCKS	ESG	iShares ESG Advanced MSCI USA Index ETF	XUSR
U.S. STOCKS	ESG	iShares ESG Aware MSCI USA Index ETF	XSUS
U.S. STOCKS	Broad Market	iShares Core MSCI EAFE IMI Index ETF	XEF
U.S. STOCKS	Broad Market	iShares Core MSCI EAFE IMI Index ETF	XEF.U
U.S. STOCKS	Broad Market	iShares Core MSCI EAFE IMI Index ETF (CAD-Hedged)	XFH
U.S. STOCKS	Broad Market	iShares MSCI EAFE Index ETF (CAD-Hedged)	XIN
U.S. STOCKS	Factor	iShares MSCI Min Vol EAFE Index ETF	XMI
U.S. STOCKS	Factor	iShares MSCI Min Vol EAFE Index ETF (CAD-Hedged)	XML
U.S. STOCKS	Factor	iShares MSCI Multifactor EAFE Index ETF	XFI
U.S. STOCKS	Factor	iShares MSCI Multifactor EAFE Index ETF (CAD-Hedged)	XFF
U.S. STOCKS	Factor	iShares International Fundamental Index ETF	CIE
U.S. STOCKS	Factor	iShares MSCI Europe IMI Index ETF	XEU
U.S. STOCKS	Factor	iShares MSCI Europe IMI Index ETF (CAD-Hedged)	XEH
U.S. STOCKS	Factor	iShares Japan Fundamental Index ETF (CAD-Hedged)	CJP
U.S. STOCKS	ESG	iShares ESG Advanced MSCI EAFE Index ETF	XDSR
U.S. STOCKS	ESG	iShares ESG Aware MSCI EAFE Index ETF	XSEA
EMERGING MARKETS STOCKS	Broad Market	iShares Core MSCI Emerging Markets IMI Index ETF	XEC
EMERGING MARKETS STOCKS	Broad Market	iShares Core MSCI Emerging Markets IMI Index ETF	XEC.U
EMERGING MARKETS STOCKS	Broad Market	iShares MSCI Emerging Markets Index ETF	XEM
EMERGING MARKETS STOCKS	Factor	iShares Emerging Markets Fundamental Index ETF	CWO
EMERGING MARKETS STOCKS	Factor	iShares MSCI Min Vol Emerging Markets Index ETF	XMM
EMERGING MARKETS STOCKS	ESG	iShares ESG Aware MSCI Emerging Markets Index ETF	XSEM
EMERGING MARKETS STOCKS	ESG	iShares China Index ETF	XCH
EMERGING MARKETS STOCKS	ESG	iShares India Index ETF	XID
GLOBAL STOCKS	Broad Market	iShares Core MSCI All Country World ex Canada Index ETF	XAW
GLOBAL STOCKS	Broad Market	iShares Core MSCI All Country World ex Canada Index ETF	XAW.U
GLOBAL STOCKS	Broad Market	iShares MSCI World Index ETF	XWD
GLOBAL STOCKS	Dividend	iShares Core MSCI Global Quality Dividend Index ETF	XDG
GLOBAL STOCKS	Dividend	iShares Core MSCI Global Quality Dividend Index ETF	XDG.U
GLOBAL STOCKS	Dividend	iShares Core MSCI Global Quality Dividend Index ETF (CAD-Hedged)	XDGH
GLOBAL STOCKS	Dividend	iShares Global Monthly Dividend Index ETF (CAD-Hedged)	CYH
GLOBAL STOCKS	Factor	iShares MSCI Min Vol Global Index ETF	XMW
GLOBAL STOCKS	Factor	iShares MSCI Min Vol Global Index ETF (CAD-Hedged)	XMY
GLOBAL STOCKS	Sectors	iShares S&P/TSX Global Base Metals Index ETF	XBM
GLOBAL STOCKS	Sectors	iShares S&P/TSX Global Gold Index ETF	XGD
GLOBAL STOCKS	Sectors	iShares Global Water Index ETF	CWW
GLOBAL STOCKS	Sectors	iShares Global Healthcare Index ETF (CAD-Hedged)	XHC
GLOBAL STOCKS	Sectors	iShares S&P Global Consumer Discretionary Index ETF (CAD-Hedged)	XCD
GLOBAL STOCKS	Sectors	iShares S&P Global Industrials Index ETF (CAD-Hedged)	XGI
GLOBAL STOCKS	Sectors	iShares Global Agriculture Index ETF	COW
GLOBAL STOCKS	Sectors	iShares Global Infrastructure Index ETF	CIF
GLOBAL STOCKS	Sectors	iShares Global Real Estate Index ETF	CGR
CANADIAN BONDS	Broad Market	iShares Core Canadian Universe Bond Index ETF	XBB
CANADIAN BONDS	Broad Market	iShares High Quality Canadian Bond Index ETF	XQB
CANADIAN BONDS	Broad Market	iShares Canadian Government Bond Index ETF	XGB
CANADIAN BONDS	Broad Market	iShares Canadian Corporate Bond Index ETF	XCB
CANADIAN BONDS	Broad Market	iShares Canadian HYBrid Corporate Bond Index ETF	XHB
CANADIAN BONDS	Broad Market	iShares Core Canadian Short Term Bond Index ETF	XSB
CANADIAN BONDS	Broad Market	iShares Core Canadian Short Term Corporate Bond Index ETF	XSH
CANADIAN BONDS	Broad Market	iShares Short Term High Quality Canadian Bond Index ETF	XSQ
CANADIAN BONDS	Broad Market	iShares Core Canadian Long Term Bond Index ETF	XLB
CANADIAN BONDS	Broad Market	iShares Canadian Real Return Bond Index ETF	XRB
CANADIAN BONDS	Broad Market	iShares 1-10 Year Laddered Government Bond Index ETF	CLG
CANADIAN BONDS	Broad Market	iShares 1-5 Year Laddered Government Bond Index ETF	CLF
CANADIAN BONDS	Broad Market	iShares 1-10 Year Laddered Corporate Bond Index ETF	CBH
CANADIAN BONDS	Broad Market	iShares 1-5 Year Laddered Corporate Bond Index ETF	CBO
CANADIAN BONDS	ESG	iShares ESG Canadian Aggregate Bond Index ETF	XSAB
CANADIAN BONDS	ESG	iShares ESG Canadian Short Term Bond Index ETF	XSTB
CANADIAN BONDS	ESG	iShares Floating Rate Index ETF	XFR
CANADIAN BONDS	ESG	iShares Premium Money Market ETF	CMR
CANADIAN BONDS	ESG	iShares Convertible Bond Index ETF	CVD
GLOBAL BONDS	ESG	iShares U.S. IG Corporate Bond Index ETF (CAD-Hedged)	XIG
GLOBAL BONDS	ESG	iShares Global Government Bond Index ETF (CAD-Hedged)	XGGB
GLOBAL BONDS	ESG	iShares J.P. Morgan USD Emerging Markets Bond Index ETF (CAD-Hedged)	XEB
GLOBAL BONDS	ESG	iShares U.S. High Yield Fixed Income Index ETF (CAD-Hedged)	CHB
GLOBAL BONDS	ESG	iShares U.S. High Yield Bond Index ETF (CAD-Hedged)	XHY
GLOBAL BONDS	ESG	iShares Short Duration High Income ETF (CAD-Hedged)	CSD
GLOBAL BONDS	ESG	iShares Conservative Short Term Strategic Fixed Income ETF	XSC
GLOBAL BONDS	ESG	iShares Conservative Strategic Fixed Income ETF	XSE
GLOBAL BONDS	ESG	iShares Short Term Strategic Fixed Income ETF	XSI
SPECIALTY	ESG	iShares Gold Bullion ETF (CAD-Hedged)	CGL
SPECIALTY	ESG	iShares Gold Bullion ETF	CGL.C
SPECIALTY	ESG	iShares Silver Bullion ETF (CAD-Hedged)	SVR
SPECIALTY	ESG	iShares Silver Bullion ETF	SVR.C
SPECIALTY	ESG	iShares S&P/TSX Canadian Preferred Share Index ETF	CPD
SPECIALTY	ESG	iShares S&P/TSX North American Preferred Stock Index ETF (CAD-Hedged)	XPF
SPECIALTY	ESG	iShares Core Income Balanced ETF Portfolio	XINC
SPECIALTY	ESG	iShares Core Conservative Balanced ETF Portfolio	XCNS
SPECIALTY	ESG	iShares Core Balanced ETF Portfolio	XBAL
SPECIALTY	ESG	iShares Core Growth ETF Portfolio	XGRO
SPECIALTY	ESG	iShares Core Equity ETF Portfolio	XEQT
SPECIALTY	ESG	iShares ESG Conservative Balanced ETF Portfolio	GCNS
SPECIALTY	ESG	iShares ESG Balanced ETF Portfolio	GBAL
SPECIALTY	ESG	iShares ESG Growth ETF Portfolio	GGRO
SPECIALTY	ESG	iShares ESG Equity ETF Portfolio	GEQT
SPECIALTY	ESG	iShares Diversified Monthly Income ETF	XTR
SPECIALTY	ESG	iShares Canadian Financial Monthly Income ETF	FIE

 

[sandy666]

let
    Source = Pdf.Tables(File.Contents("path_to_pdf"), [Implementation="1.1"]),
    Table001 = Source{[Id="Table001"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true]),
    #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "Type", each if Text.Contains([Fund Name], "CANADIAN") then [Fund Name] else if Text.Contains([Fund Name], "U.S. STOCKS") then [Fund Name] else if [Fund Name] = "GLOBAL STOCKS" then [Fund Name] else if [Fund Name] = "CANADIAN BONDS" then [Fund Name] else if [Fund Name] = "EMERGING MARKETS STOCKS" then [Fund Name] else if [Fund Name] = "GLOBAL BONDS" then [Fund Name] else if [Fund Name] = "SPECIALTY" then [Fund Name] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Style", 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),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column1",{"Type", "Style", "Fund Name", "Ticker#(lf)Symbol", "Mgmt Fee#(lf)(%)", "Net#(lf)Assets#(lf)($mln)", "YTD (%)", "1#(lf)Month#(lf)(%)", "3#(lf)Month#(lf)(%)", "6#(lf)Month#(lf)(%)", "1 Year#(lf)(%)", "3 Year#(lf)(%)", "5 Year#(lf)(%)", "10 Year#(lf)(%)", "Since#(lf)Inception#(lf)(%)", "Inception Date"}),
    #"Filled Down" = Table.FillDown(#"Reordered Columns",{"Type", "Style"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"Ticker#(lf)Symbol"] <> null))
in
    #"Filtered Rows"

 

[DRSteele]

Sorry for the late reply - I did not see you'd responded.

That last bit does the trick. Thanks for that.

But I need a general solution. I can't use a solution where the user must search through the data and come up with each new Type and Style. I have other PDFs with scores of pages and dozens of Types and Styles.

I suspect that what I seek is not achievable.

 

[sandy666]

Look for option with parameter, where parameter is a list of your PDFs with full path to them (if you want use all PDFs at once). It will be your source line
also you need to create distinct list from Type and from Style (automatically) then somehow join it to the source, and so on...
this is idea only but imho it's doable

 

[sandy666]

or create list of PDFs and use it in DataValidation List, so it will be one PDF at time

I don't know if there is the same structure of data or each pdf is different from the rest
so user will use DVList and refresh. I think this is not too hard

 

[sandy666]

@post#15
see this link
there is 56 logs and I can choose one to display (use) various tables