sio/libpq

Detach your M code from workbooks to reuse it! Import modules from local or web storage (unlimited number of sources)

libpq's Issues

Unit testing

Add a basic framework for writing and executing unit tests

Documentation for LibPQPath

LibPQPath is more of an API than a constant. This needs to be documented.

• Create new docs page
• Formal API declaration
• Example use cases:
  • Load from text file: #3 (comment)
  • Use table instead of a record: http://blog.blauservices.com/?p=1963

LibPQ disabling Power Query intellisense?

Originally posted by @B-D-T in #12 (comment)

I apologize if I'm missing something, but it appears that adding LibPQ breaks the Power Query intellisense. In Excel workbooks where I don't have LibPQ/LibPQPath, I get the intellisense function list, arguments, etc. But for those where I've integrated LibPQ, the intellisense only reads objects that were defined within the existing module.

Almost all my work is done directly in M in the Advanced Editor. Having intellisense makes that process easier, but now that I've discovered the wonderful LibPQ, I don't want to give that up either! Any suggestions?

Overwrites metadata defined on type

Currently LibPQ overwrites metadata that was defined on the type of the value when processing docstring

Release LibPQ as *.mez file

Idea

1. Create a build script that packages LibPQ into *.mez file
2. Build and test LibPQ as Power Query Extension
   • Decide where to store LibPQPath (or maybe introduce sensible defaults)
   • Decide whether to include only LibPQ or to package all default modules as well

More information

• http://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/
• https://github.com/Hugoberry/PowerQueryExtensions/tree/master/build
• https://github.com/Microsoft/DataConnectors/tree/master/samples/Github
• https://github.com/migueesc123/PowerBIRESTAPI/blob/master/Power%20BI%20API.mez

Multiple key fields in Table.CountDuplicates.pq

Add support for column-or-listofcolumns style value for key_field parameter, like that of many functions in standard library

Allow for Microsoft-Format unittests to be run by the unit testing framework

Hi,

Microsoft have also release a unit testing framework for powerquery, though it requires the framework to be in the same file, by the looks of things.

It would be good if the unittest module could also cope with unit tests expressed as Facts, to make it easy for people who started in that framework to port it to the LibPQ way of doing things

Unit Testing - Tests with coding errors disappear from test run results

When a unit test file has an error in it, the entire file's tests disappear from the UnitTest Results. To Reproduce do the following:

1. With LibPQ configured to run the LibPQ Unit Tests, Run UnitTest.Discover()
2. Notice that the Test.MicrosoftUnitTestDemo tests are listed.
3. Edit Tests\Test.MicrosoftUnitTestDemo.pq and comment out line 12 (UnitTesting.Returns123...)
4. Save and refresh the UnitTest.Discover()

The Test.MicrosoftUnitTestDemo tests are no longer listed. It would be more desirable to list the file as a single row with an error. I've looked through the code, and tried a number of things, without success, so thought it was worthy of an "Issue".

I'm glad to help with resolving, if someone could give me some pointers.

Path as an argument to LibPQ

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.

Better error reporting when importing

If module import errors out, show last error message that is not "file not found"

Move test code into a separate directory

As the number of tests grows, the test modules create unnecessary clutter in the Modules/ directory.

We need to:

• Move tests into a separate directory
• Decide on another prefix for tests, because Tests/Tests.Name.pq seems redundant but tests module names still should not collide with normal module names
• Update LibPQPath.sample.pq to enable importing tests

Add metadata on import

Add some metadata to imported queries/functions:

• module name
• source path (file path or web url)
• generate description (treat first comment as a docstring)

Do not overwrite existing metadata

See: https://ssbi-blog.de/technical-topics-english/writing-documentation-for-custom-m-functions-part1/

VBA to update ThisWorkbook's path and directory

Create VBA function (on Workbook_Open) to update the path to the current directory and the current workbook in the custom query.

Idea:

ThisWorkbook = 
[
  folder = "C:/Docs/",
  path = "C:/Docs/Workbook.xlsm",
  filename = "Workbook.xlsm"
]

Inspired by http://excelunplugged.com/2018/01/23/modifying-power-query-m-code-with-vba/

Undocumented: multiline comment as a docstring

Undocumented feature: LibPQ treats multiline comment on top of the source code as a docstring

Feedback from users

Hello! If you are using LibPQ (or have used, or have tried to use it but something didn't work out for you) - please leave some feedback in comments below.

• Does LibPQ suit your needs? Were you able to successfully use it in any workbooks/reports?
• Was the documentation sufficient? What topics were unclear or missing?
• What LibPQ features do you consider most important?

Any feedback beside the three points above is also greatly appreciated! Thank you!

Add support for subtests

Single test (one function): testName = () => Assert
Subtests (list of functions): testName = {()=>Assert, ()=>Assert, ()=>Assert, ...}

Changes can be stitched into the line below. We can invoke single test function or run all test functions in a list of subtests if needed.

LibPQ/Modules/UnitTest.Run.pq

Line 44 in 4d4e1e0

{"Result", each try Record.Field(Suite, [Test])()},

Compatibility with PowerBI service

Looks like LibPQ can not be used in PowerBI service because of providing dynamic arguments to Web.Content(). This page is the place to discuss, develop and test possible workarounds.

Important

I have no account with PowerBI service, so I will need outside help to do the tests

Roadmap

• Describe the testing sequence (@sio)
• Test LibPQ 1.2.0 with PowerBI service (@spythan?)
• Modify loader to use static base URL with Web.Contents (@sio)
• Test modified version of LibPQ with PowerBI service (not assigned)
• Write documentation on using LibPQ with Power BI service

Update relative links after moving Unittesting docs

Update relative links after moving Unittesting docs

Docstrings in LibPQ: sample mismatch

Code does not match the screenshot