How to import External Power Query from text files.md

importing-local-powerquery.md

Example "lib"

Instead of a table, end up with a record, which can contain any number of functions

let
    // attempt to convert any type to Csv string
    ToCsv = (source as list) as text =>
        let
            text_list = List.Transform(
                source,
                each try Text.From(_) catch (e) => "<error>"
            )
        in
            Text.Combine( text_list, ", " ),

    AnotherFunc = ...,

    myLib = [
        ToCsv = ToCsv,
        AnotherFunc = AnotherFunc
    ]
in
    myLib

Method 1: A record of functions

More reliable because there's no external files, it's all inline.

let 
    ToCsv = Lib[ToCsv], // optional, but makes it easier to read


    // # then later

    mixed = ToCsv({ 20, "foo", #date(2022,1,1) })
in 
    mixed

Method 2: Import from a local text file

Easier to edit queries (like in VS Code), you still have to hit refresh.

let 
    path = "c:\foo\test.pq",
    bytes = File.Contents(path),
    lib = Expression.Evaluate( bytes, #section[Section1] ),
    ToCsv = lib[ToCsv],

    // later
    mixed = ToCsv({ 20, "foo", #date(2022,1,1) })
in 
    mixed