Hello Mynda, Phil and fellow travellers,
I'm building a PQ/PP to analyse an investment portfolio to reveal where a collection of collective funds has led to higher or lower than intended concentrations in (e.g.) China, Financials or big-cap organisations.
The data is from Morningstar in the form of .PDFs which I download to my PC.
It's dirty data. So the first task is to clean it, which I do with a query called ROOT.
Then there are 5-subsequent queries referenced to ROOT to provide geographical, size, sector allocations, (etc).
Now I have built the query for ROOT and the 5-analyses referenced from ROOT for one investment PDF, tested it and it works for all the target investments individually. I want to convert the set to a set of custom functions to build the data for every investment in the portfolio so I can use pivot tables and charts across the complete portfolio. So, for example, there will be 30-rows with x% China, y% large investees and z% retail ... and all the other allocations (Europe, USA... Medium, small... Finance, mining, etc).
Am I right in thinking you can't actually build a custom function from referenced queries, but I have to re-design them into 5 full queries where the first 20-steps are common, but each is stand-alone in its integrity? ROOT produces a 5-column, 40-row output for each investment, I don't understand how PQ would handle 30-arrays like this to then allow a downstream query (e.g. geography) to just select the % investment in each territory and store it in a row by EPIC code and another to add columns for % investment by size of investee organisation.
As always, grateful for any advice and assistance but completely understanding if you have better things to occupy your time than the dull boy at the back of the class.
Kind regards and happy 2022!
Simon
Hi Simon,
When you reference a query, it normally looks like this:
let
Source = Table1,
in
Source
(where Table1 is a reference to a worksheet table with the initial data)
If you want to convert this to a function, just add a line above the first let statement with your parameters that needs to be passed to the referenced table:
(Category as text)=>
let
Source = Table1,
#"Filtered Rows" = Table.SelectRows(Source, each ([Major Heading] = Category))
in
#"Filtered Rows"
This function can be called in other queries passing the required parameters.
Thanks for your help Catalin, sadly I don't think I'm familiar enough with M to see how to apply your suggestion in my case. Here is an example of the working code that references from the query "Port2(root)"
let
Source = #"Port2(root)",
#"Removed Columns" = Table.RemoveColumns(Source,{"Country", "Fund"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([#"SectorWt%"] = "Assets in Top 10 Holdings %" or [#"SectorWt%"] = "Top 10 Holdings" or [#"SectorWt%"] = "Total Number of Bond Holdings" or [#"SectorWt%"] = "Total Number of Equity Holdings")),
#"Transposed Table4" = Table.Transpose(#"Filtered Rows1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table4", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Top 10 Holdings", type date}, {"Assets in Top 10 Holdings %", type number}, {"Total Number of Equity Holdings", Int64.Type}, {"Total Number of Bond Holdings", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Top 10 Holdings", "DataDate"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Assets in Top 10 Holdings %", Percentage.Type}}),
#"Divided Column" = Table.TransformColumns(#"Changed Type", {{"Assets in Top 10 Holdings %", each _ / 100, Percentage.Type}})
in
#"Divided Column"
But as the source documents (a folder of PDFs) is not addressed in the above script, but in the Port2(root), I don't see where I could put the function variable. Port2(root) starts with:
let
Source = Pdf.Tables(File.Contents("C:pdfsCTY-port2.pdf"), [Implementation="1.3"]),
... followed by 18 lines of transformation steps
And in the functions I've invoked that are working I have changed this to:
(pagestart as text) =>
let
Source = Pdf.Tables(File.Contents("C:pdfs"&pagestart&"-port2.pdf"), [Implementation="1.3"]),
But to get this to work, I've stopped the referencing relationship and embedded the Port2(root) steps into the beginning of all the custom functions that build the data model.
Any thoughts gratefully received.
Kind regards,
Simon
Hi Simon,
Hard to imagine your structure without a clear image.
From your descriptions, I assume that there is a set of port2.pdf files, are there other portx.pdf files?
From your code:
"C:pdfs"&pagestart&"-port2.pdf"
I assume there are multiple subfolders in C:pdfs folder?
I think we will have to setup an online meeting to clarify these things in a live session, will contact you by mail.
Hi Catalin,
Thank you for your time and heroic efforts to solve my query. Let's leave it here as I am pretty sure that the answer is that it is not possible to form a set of custom functions from a query split in two and joined by a reference. I have solved the use case by cutting & pasting the M code in the root query into the first part of the 5-downstream queries and made custom functions out of them.
To answer your specific questions, there are no sub-folders to C:pdfs but there are 30 files in that folder with the name convention 'AAA-port2.pdf' where AAA is the EPIC code for the underlying security. Invoking the 5-custom functions lets power query access each of the pdfs in turn, perform cleansing as the data-tables in the pdfs are not identical, and then extract data (such as the proportion of the fund invested in UK or USA, etc) and store it in the data model where a pivot table shows for the entire portfolio how much is invested in (e.g.) UK, USA, etc.
When complete the data model will hold about 100 columns of data extracted from different tables in the pdfs.
Thanks again for your time and thoughtful consideration.
Simon
