Path as an argument to LibPQ

[sio]

Maybe:

Allow optional second argument to LibPQ() that passes the path record to override LibPQPath

I was considering allowing to override the values provided by LibPQPath for some calls, e.g. if you'd want LibPQ("CoolModule") to return one thing in one report and another thing in another one - both in the same workbook. I have not encountered a use case for this idea, so I've just left it at that.

[spythan]

I needed that myself so implemented using Lines.FromBinary(#"Parameter Name"), and just used the parameter to store local path to text file.

Using with ignore privacy levels option currently, since it caused a Formula.Firewall error, haven't looked much into how to work around it with privacy levels on as of yet.

[sio]

Thanks for your feedback!

Unfortunately I don't quite get what you mean: did you encounter a use case where you've needed to override module path for a single LibPQ call? Why was it not ok to add that path to the workbook's LibPQPath record with a higher priority? I'm trying to understand your use case better to be able to design a convenient API that would help. I have not encountered such case myself yet.

[spythan]

Hello again!

First what I have done.

My LibPQPath query in Power BI looks like this:

let
    LibPQPath = [
        Local = Lines.FromBinary(File.Contents(#"LibPQPathLocal Location")),
        
        Web = {
            "https://raw.githubusercontent.com/sio/LibPQ/master/Modules/",
            "https://raw.githubusercontent.com/tycho01/pquery/master/"
        }
]
in
    LibPQPath

Then in #"LibPQPathLocal Location" (a parameter) I assign the local path to a text file:
C:\........\ProjectName\LibPQPathLocal.txt

And finally LibPQPathLocal.txt looks like this:

C:\........\ProjectName\LibPQ-master\Modules
C:\........\ProjectName\LibPQ-master\Tests
C:\........\ProjectName
C:\........\ProjectName\Database
C:\........\ProjectName\Demand

It just felt better to me something that is project specific to be abstracted out of your project's code, and following an easy given format imported from a project file.

That way you can:

• edit the file in the editor of your choice
• don't have to change the .pbix/.pbit every time you add a new path, with commit's changed files being more explicit about what it is that changed
• have version control of the path strings independently and review more easily if needed (don't have to open .pbix/.pbit file to review)
• possibly add auto-descovery functionality with any way it can be achieved, I myself don't currently know how exactly this would be done but I can imagine several ways and may implement it later myself
• if you share a project with someone (maybe a client) and you use LibPQ to manage your queries outside Power BI and then import them with LibPQ(), the paths must be exactly the same on each computer, which can produce problems - using a parameter for where the project's paths file is located, I believe you have a cleaner/quicker solution for the recipient to enter just one parameter when opening the file and everything works as expected (in this situation I believe you want your paths to be constructed dynamically using the parameter as root and joining the following path from file line by line - haven't implemented myself yet).

I don't fully understand the relevance concerning Power BI Service at the moment as I have not yet set-up a development environement there, since I am building my first project with these tools and am still in the Data Modeling phase of it. If/when I come upon more relevant information I will share here.

Thank you so much again for your wonderful project.

[sio]

Now I understand what you've meant :) It's a valid concern even though it's not what this issue was about.

I was considering allowing to override the values provided by LibPQPath for some calls, e.g. if you'd want LibPQ("CoolModule") to return one thing in one report and another thing in another one - both in the same workbook. I have not encountered a use case for this idea, so I've just left it at that.

Now about your case. LibPQPath is decoupled from the main LibPQ code exactly to allow hacks and customizations like yours. You can fetch and/or calculate the LibPQPath value in any way you think is best for your project. File imports are a tricky matter though, and while Formula.Firewall errors are not a big deal for you, they might be for some other users - that's why I think the default behavior should not be changed.

Looks like your case and other possibilities of customizing LibPQPath should be documented. I'll try to do that some time :) Thank you for your feedback!

[spythan]

I truly agree both about the customization it allows and the default behavior having to be as is.

Do you think automatic discovery of sub-folders of mentioned paths in LibPQPath, for by-default supporting importing modules from project's sub-folders, would be possible, worthwhile, or of interest? Is it possible with M or are there limitations? If there are, what about R and Python scripting? Only asking for your thoughts since you will have more experience with this kind of thing than me, not an implementation, with much respect for your time 😛

[sio]

M provides enough tools to implement file discovery in subfolders. By parsing the output of Folder.Files and Folder.Contents you could add all subfolders of a given folder to LibPQPath if you want to go that way. If by project subfolders you meant paths relative to the location of workbook, then it's not as simple. You would need to invoke some external mechanism to calculate the path to current workbook. See example here.

I've briefly considered the tree-like structure for modules when I first started with LibPQ, but I've decided not to go that way. It would require making too much assumptions and would result in too much implicit behavior that the user would need to read the docs to understand.

For example, resolving the name collisions. As it is now, if you have two folders in LibPQPath and you have modules with the same name in both of them, it is very simple to understand that the folder that comes first will be used. It would not be as explicit if those were automatically discovered subfolders of an item in LibPQPath. What sort order would we use? Alphanumeric? In which locale? Or maybe we would require user to explicitly say which subfolder to use? But that would not be much different from what we have now, except we would need to define the separator character - again implicitly and counterintuitive for some users.

PS: While I was writing this reply I've discovered that LibPQ already supports import from subfolders :-D You can try it with LibPQ("Subdir/Module"). This behavior is entirely accidental and should not be relied upon though.