{"id":1431,"date":"2021-01-31T17:22:54","date_gmt":"2021-01-31T23:22:54","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=1431"},"modified":"2022-12-15T16:24:06","modified_gmt":"2022-12-15T22:24:06","slug":"power-query-custom-functions-cheat-sheet","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2021\/01\/31\/power-query-custom-functions-cheat-sheet\/","title":{"rendered":"Power Query Custom Functions – Cheat Sheet"},"content":{"rendered":"\n\n\n

Converting Types<\/h2>\n\n\n\n

DateTime.FromText using format strings<\/h3>\n\n\n\n
let dt =  DateTime.FromText("2001-01-01T05:09:23", [Format = "yyyy-MM-ddTHH:mm:ss", Culture = "en-us"]) in dt<\/code><\/pre>\n\n\n\n
For format strings syntax, see: dotnet\/custom-date-and-time-format-strings<\/a><\/p>\n\n\n\n
Web Requests<\/h2>\n\n\n\n
Check out the new tutorial at https:\/\/blog.powp.co\/my-power-query-web-contents-cheat-sheet-6a5bbfdce5eb <\/p>\n\n\n\n
Web.Contents without Refresh Errors<\/h3>\n\n\n\n
The main cause of Web.Contents<\/a> not refreshing can be fixed by adding the options[Query]<\/code> and options[RelativeaPath]<\/code> parameters. (The UI doesn’t create them for you)

This example uses https:\/\/www.metaweather.com\/api\/location\/search?query=london<\/a> See more: Skip Test Connection<\/a><\/p>\n\n\n\n
\/\/ lookup city GUID - simplified\nlet\n    exampleUrl = "https:\/\/www.metaweather.com\/api\/location\/search?query=london",\n    Headers = [\n        Accept="application\/json"\n    ],\n    BaseUrl = "https:\/\/www.metaweather.com",\n    Options = [\n        RelativePath = "\/api\/location\/search",\n        Headers = Headers,\n        Query = [\n            query = "london"\n        ],\n        ManualStatusHandling = {400, 404}\n    ],\n    \/\/ wrap 'Response' in 'Binary.Buffer' if you are using it multiple times\n    response = Web.Contents(BaseUrl, Options),\n    buffered = Binary.Buffer(response),\n    response_metadata = Value.Metadata(response),\n    status_code = response_metadata[Response.Status],\n    final_result =\n        if List.Contains({400, 404}, status_code) then response_metadata\n        else buffered,\n    from_json = Json.Document(final_result),\n    #"weather record" = from_json{0}\nin\n    #"weather record"<\/code><\/pre>\n\n\n\n
Handling Specific HTTP Error Status Codes<\/h3>\n\n\n\n
You can filter results based on the status code, instead of getting errors.
See more: override default error handling<\/a>, and the example wait-for loop<\/a><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\/* web request, act based on the HTTP Status Code returned\nsee more:\n    override default error handling: https:\/\/docs.microsoft.com\/en-us\/power-query\/handlingstatuscodes\n    example wait-for loop: https:\/\/docs.microsoft.com\/en-us\/power-query\/waitretry#manualstatushandling\n*\/\nlet\n    WikiRequest = (pageName as text) as any =>\n        let\n            BaseUrl = "https:\/\/en.wikipedia.org\/wiki",\n            Options = [\n                RelativePath = pageName,\n                ManualStatusHandling = {400, 404}\n            ],\n            \/\/ wrap 'Response' in 'Binary.Buffer' if you are using it multiple times\n            response = Web.Contents(BaseUrl, Options),\n            buffered = Binary.Buffer(response),\n            response_metadata = Value.Metadata(response),\n            status_code = response_metadata[Response.Status],\n            final_result = [\n                buffered = buffered,\n                response_metadata = response_metadata\n            ]\n        in\n            final_result,\n    Queries = {"Cat", "DoesNot Exist fake page"},\n    Items = List.Transform(\n        Queries,\n        each WikiRequest( _ )\n    ),\n    ResponseTable = Table.FromRecords(\n        Items,\n        type table[buffered = binary, response_metadata = record], MissingField.Error\n    ),\n\n    #"Expanded HTTP Status Codes" = Table.ExpandRecordColumn(ResponseTable, "response_metadata", {"Response.Status"}, {"Response.Status"}),\n    #"Changed Type" = Table.TransformColumnTypes(#"Expanded HTTP Status Codes",{{"Response.Status", Int64.Type}})\nin\n    #"Changed Type"<\/code><\/pre>\n\n\n\n
WebRequest<\/code>: Wrapper with Better Defaults<\/h3>\n\n\n\n
You can get the full file with extra comments: WebRequest.pq<\/a><\/p>\n\n\n\n
    let\n        \/*\n        Example using this url:\n            (https:\/\/www.metaweather.com\/api\/location\/search?lattlong=36.96,-122.02)\n\n            WebRequest(\n                "https:\/\/www.metaweather.com",\n                "api\/location\/search",\n                [ lattlong = "36.96,-122.02" ]\n            )\n\n        Details on preventing "Refresh Errors", using 'Query' and 'RelativePath':\n            - Not using Query and Relative path cause refresh errors:\n                (https:\/\/blog.crossjoin.co.uk\/2016\/08\/23\/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi\/)\n\n            - You can opt-in to Skip-Test:\n                (https:\/\/blog.crossjoin.co.uk\/2019\/04\/25\/skip-test-connection-power-bi-refresh-failures\/)\n\n            - Debugging and tracing the HTTP requests\n                (https:\/\/blog.crossjoin.co.uk\/2019\/11\/17\/troubleshooting-web-service-refresh-problems-in-power-bi-with-the-power-query-diagnostics-feature\/)\n\n        *\/\n        WebRequest = (\n            staticPath as text,                 \/\/ domain\n            relativePath as text,               \/\/ basically use everything after ".com" to "?"\n            optional query as nullable record,  \/\/ url query string\n            optional asRaw as nullable logical, \/\/ use true if content is not Json\n            optional headers as nullable record \/\/ optional HTTP headers as a record\n        ) as any =>\n            let\n                query = query ?? [],\n                asRaw = asRaw ?? false, \/\/ toggles calling Json.Document() or not\n                headers = headers ?? [\n                    Accept="application\/json"\n                ],\n\n                baseUrl = staticPath,\n                options = [\n                    RelativePath = relativePath,\n                    Headers = headers,\n                    Query = query\n                    \/\/ optionally toggle handling errors for specific HTTP Status codes\n                    \/\/ ManualStatusHandling = {400, 404}\n                ],\n                \/\/ wrap 'Response' in 'Binary.Buffer' if you are using it multiple times\n                response = Web.Contents(staticPath, options),\n                metadata = Value.Metadata(response),\n                buffered = Binary.Buffer(response),\n                result = Json.Document(buffered)\n            in\n                [\n                    response = if asRaw then buffered else result,\n                    status_code = metadata[Response.Status],\n                    metadata = metadata\n                ]\n    in\n        WebRequest<\/code><\/pre>\n\n\n\n
Chaining Web.Contents to Merge Many Queries<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n
    let \n        response_locations = WebRequest(\n            "https:\/\/www.metaweather.com",\n            "api\/location\/search",\n            [ lattlong = "36.96,-122.02" ]\n        ),\n        location_schema = type table[\n            distance = number, title = text,\n            location_type = text, woeid = number, latt_long = text\n        ],\n        cityListing = Table.FromRecords(response_locations[response], location_schema, MissingField.Error),\n        \n        city_mergedRequest = Table.AddColumn(\n            cityListing,\n            "LocationWeather",\n            (row as record) as any =>\n                let\n                    woeid = Text.From(row[woeid]),\n                    response = WebRequest(\n                        "https:\/\/www.metaweather.com",\n                        "api\/location\/" & woeid,\n                        []\n                    )\n                in\n                    response,\n            type any\n        )\n    in\n        city_mergedRequest<\/code><\/pre>\n\n\n\n
Html.Table – Parsing with CSS Selectors<\/code><\/h2>\n\n\n\n
Select a Single Element: an Image, url, text, etc…<\/h3>\n\n\n\n
This fetches the current heading text on the blog<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
let\n    Url = "https:\/\/powerbi.microsoft.com\/en-us\/blog\/",\n    Response = Web.Contents( Url ),\n    \/*\n    note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors\n    Non-tabular scraping like Images or any single elements, does not use a "RowSelector"\n\n    This CSS Selector finds exactly one element, the Page's Header Text\n\n        .section-featured-post .text-heading1 a\n    *\/\n    HeaderText = Html.Table(\n        Response,\n        {\n            { "Page Header", ".section-featured-post .text-heading1 a" }\n        }\n    )\nin\n    HeaderText<\/code><\/pre>\n\n\n\n
Parsing Element’s Attributes<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n
let\n    Url = "https:\/\/powerbi.microsoft.com\/en-us\/blog\/",\n    Response = Web.Contents( Url ),\n    \/*\n    The 3rd argument in "columnNameSelectorPairs" is the transformation function.\n    by default it uses:\n\n            each _[TextContent]\n    *\/\n    HeaderAsElement = Html.Table(\n        Response,\n        { { "Link", ".section-featured-post .text-heading1 a", each _ } }\n    ),\n\n    ElementRecord = Table.ExpandRecordColumn(\n        HeaderAsElement, "Link",\n        {"TagName", "TextContent", "Attributes"}, {"TagName", "TextContent", "Attributes"}\n    ),\n\n    ExpandedAttributes = Table.ExpandRecordColumn(\n        ElementRecord, "Attributes",\n        {"href", "rel", "title"}, {"attr.href", "attr.rel", "attr.title"}\n    )\nin\n    ExpandedAttributes<\/code><\/pre>\n\n\n\n
Select Tables using your own CSS Selectors<\/h3>\n\n\n\n
\"CSS
Results of the RowSelector<\/code> <\/figcaption><\/figure>\n\n\n\n
\"CSS
Results of columnNameSelectorPairs<\/code> <\/figcaption><\/figure>\n\n\n\n
\/\/ Docs on Enum\nlet\n    Source = "https:\/\/docs.microsoft.com\/en-us\/previous-versions\/dynamics\/ax-2012\/reference\/gg841505(v=ax.60)",\n    \/\/ note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors\n    Response = Web.BrowserContents( Source ),\n    \/*\n\n        Think of "RowSelector" as selecting a table row\n        Then for every row, you select "columns" using the "columnNameSelectorPairs" selector\n        The combination gives you a table cell.\n\n        For more on CSS Selectors, see: <https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/CSS\/CSS_Selectors>\n    *\/\n\n    columnNameSelectorPairs = {\n        \/\/ column names don't matter here, since I'm using .PromoteHeaders\n        { "Column1", "TABLE.table > * > TR > :nth-child(1)" },\n        { "Column2", "TABLE.table > * > TR > :nth-child(2)" },\n        { "Column3", "TABLE.table > * > TR > :nth-child(3)" }\n    },\n\n    t1 = Html.Table(\n            Response, columnNameSelectorPairs,\n            [RowSelector = "TABLE.table > * > TR"]\n    ),\n\n    t2 = Table.PromoteHeaders( t1, [PromoteAllScalars = true] ),\n    FinalTable = Table.TransformColumnTypes(\n            t2,\n            { { "Name", type text }, { "Value", Int64.Type }, { "Description", type text} }\n        )\nin\n    FinalTable<\/code><\/pre>\n\n\n\n
SQL Native Query<\/h2>\n\n\n\n
Parameterized SQL queries with Value.NativeQuery()<\/code><\/h3>\n\n\n\n
let\n    Source = Sql.Database("localhost", "Adventure Works DW"),\n    Test = Value.NativeQuery(\n        Source,\n        "SELECT * FROM DimDate \n        WHERE EnglishMonthName=@MonthName",\n        [\n            MonthName = "March",\n            DayName = "Tuesday"\n        ]\n    )\nin\n    Test<\/code><\/pre>\n\n\n\n
Other<\/h2>\n\n\n\n
Cartesian Product<\/h3>\n\n\n\n
\"Table<\/figure>\n\n\n\n
let    \n    #"Add Column Pair2" = Table.AddColumn(\n        Table_Pairs1, "Pairs2",\n        each Table_Pairs2,\n        Table.Type\n    ),\n    #"Expanded Pairs" = Table.ExpandTableColumn(\n        #"Add Column Pair2",\n        "Pairs2",\n        {"Color", "Property"},\n        {"Color", "Property"}\n    )\nin\n    #"Expanded Pairs"<\/code><\/pre>\n\n\n\n
Details: https:\/\/radacad.com\/cartesian-product-in-power-query-multiply-all-sets-of-all-pairs-in-power-bi<\/a><\/p>\n\n\n\n
Using Multiple `Enter Data` Tables in One Query<\/h3>\n\n\n\n
You can make start with ‘enter data’, then duplicate that line. You end up with two steps, each are their own enter data<\/code> <\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
let\n    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEvOLFbSUfLJrEosSgEyTJRidaKVHHNSKxJTijITgSLuqcnZ+UDaEiwTkgpS5ZtfkgGkjJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Species = _t, Age = _t]),\n    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krNU9JRciotKUktSsupBLINlWJ1opWCE4sSM4A858QSIGlkDBYMSU0Bclzy04GkiYFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Species = _t, Age = _t]),\n    Final = Table.Combine( { Source, Source2 } )\nin\n    Final<\/code><\/pre>\n\n\n\n
Convert Table to JSON<\/code><\/h3>\n\n\n\n
TableToJson = (source as table, optional encoding as nullable number) as text =>\n        let\n            encoding = encoding ?? TextEncoding.Utf8,\n            bin = Json.FromValue(source, encoding),\n            jsonAsText = Text.FromBinary(bin, encoding)\n        in\n            jsonAsText<\/code><\/pre>\n\n\n\n
Animals = #table(\n    {"Animal", "Id"},\n    {{"Cat", 1}, {"Turtle", 2}} ),\nTableTo_Json( Animals )<\/code><\/pre>\n\n\n\n
Json output:<\/p>\n\n\n\n
[{"Animal":"Cat","Id":1},{"Animal":"Turtle","Id":2}]<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
Converting a list<\/code> of items to a CSV<\/code> string<\/h3>\n\n\n\n
let\n    \/\/ Converts a list of any type to text. Works well on most types\n    \/\/ although to support all cases, it requires more logic\n    mixedList = {4, "cat", #date(1999,5,9), 0.4},\n    ListAsText = List.Transform(mixedList, each Text.From(_)),\n    CsvText = Text.Combine( ListAsText, ", ")\nin\n    \/\/output: "4, cat, 5\/9\/1999, 0.4"\n    CsvText <\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
Functions<\/h2>\n\n\n\n
Using Optional Parameters with Default Values<\/h3>\n\n\n\n
The Null_coalescing_operator ??<\/code><\/a> simplifies default values.
encoding<\/code> will be set to what the user passed, unless it’s null<\/code>. In that case, it is set to TextEncoding.Utf8<\/code><\/p>\n\n\n\n
let\n    Read_Json = (json as any, encoding as nullable number) as any =>\n        \/\/ calls Json.Document, using UTF8 by default\n        let\n            encoding = encoding ?? TextEncoding.Utf8,\n            result = Json.Document(json, encoding)\n        in\n            result\nin\n    Read_Json<\/code><\/pre>\n\n\n\n
Mapping Function Calls based on Type<\/h3>\n\n\n\n
Caller chooses which type of conversioin to use, based on type names { date, datetime, datetimezone }<\/code><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
let\n    \/\/ 1] get a `type` from caller\n    \/\/ 2] return a difference function based on the type\n    GetTransformByType = (_type as type) as function =>\n        let\n            \/\/ originally from: <https:\/\/docs.microsoft.com\/en-us\/power-query\/helperfunctions#tablechangetype>\n            func_transform = \n                 if (Type.Is(_type, type date)) then Date.From\n            else if (Type.Is(_type, type datetime)) then DateTime.From\n            else if (Type.Is(_type, type datetimezone)) then DateTimeZone.From\n            else (t) => t \/\/ else return self\n        in\n            func_transform,\n\n    nowDtz = DateTimeZone.LocalNow(),    \n\n    \/\/ invoke in 2 steps\n    toDate = GetTransformByType(type date),\n    callDate = toDate( DateTimeZone.FixedLocalNow() ),\n\n    \/\/ create, and invoke functions\n    Results = [\n        asDate = (GetTransformByType(type date))( nowDtz ),\n        asDateTime = (GetTransformByType(type datetime))( nowDtz ),\n        asDateTimeZone = (GetTransformByType(type datetimezone))( nowDtz )\n    ]\nin\n    Results<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Declaring optional parameters<\/code> in custom functions, combine multiple<\/b> Web.Contents<\/code> queries, without refresh errors<\/b>. Using Html.Table<\/code> with custom CSS Selectors<\/code><\/p>\n","protected":false},"author":1,"featured_media":1513,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[65,13,9,14],"tags":[10,61,79,6,7,15,81,78],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1431"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=1431"}],"version-history":[{"count":86,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1431\/revisions"}],"predecessor-version":[{"id":2640,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1431\/revisions\/2640"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/1513"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=1431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=1431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=1431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}