Ninmonkey

Web Requests

Web.Contents without Refresh Errors

The main cause of Web.Contents not refreshing can be fixed by adding the options[Query] and options[RelativeaPath] parameters. (The UI doesn’t create them for you)

This example uses https://www.metaweather.com/api/location/search?query=london

// lookup city GUID - simplified
let
    exampleUrl = "https://www.metaweather.com/api/location/search?query=london",
    Headers = [
        Accept="application/json"
    ],
    BaseUrl = "https://www.metaweather.com",
    Options = [
        RelativePath = "/api/location/search",
        Headers = Headers,
        Query = [
            query = "london"
        ],
        ManualStatusHandling = {400, 404}
    ],
    // wrap 'Response' in 'Binary.Buffer' if you are using it multiple times
    response = Web.Contents(BaseUrl, Options),
    buffered = Binary.Buffer(response),
    response_metadata = Value.Metadata(response),
    status_code = response_metadata[Response.Status],
    final_result =
        if List.Contains({400, 404}, status_code) then response_metadata
        else buffered,
    from_json = Json.Document(final_result),
    #"weather record" = from_json{0}
in
    #"weather record"

WebRequest: Wrapper with Better Defaults

let
    /*    
    Example using the url:
        https://www.metaweather.com/api/location/search?lattlong=36.96,-122.02

        WebRequest(
            "https://www.metaweather.com",
            "api/location/search",
            [ lattlong = "36.96,-122.02" ]
        )

    */
    WebRequest = (
        staticPath as text,                 // domain
        relativePath as text,               // basically use everything after ".com" to "?"    
        optional query as nullable record,  // url query string
        optional asRaw as nullable logical, // use true if content is not Json
        optional headers as nullable record 
    ) as any =>
        let
            query = query ?? [],
            asRaw = asRaw ?? false, // toggles calling Json.Document() or not
            headers = headers ?? [
                Accept="application/json"
            ],

            baseUrl = staticPath,
            options = [
                RelativePath = relativePath,
                Headers = headers,
                Query = query
                // ManualStatusHandling = {400, 404}
            ],
            // wrap 'Response' in 'Binary.Buffer' if you are using it multiple times            
            response = Web.Contents(staticPath, options),
            metadata = Value.Metadata(response),
            buffered = Binary.Buffer(response),
            result = Json.Document(buffered)
        in
            [
                response = if asRaw then buffered else result,
                status_code = metadata[Response.Status],
                metadata = metadata
            ]
in
    WebRequest

Chaining Web.Contents to Merge Many Queries

    let 
        response_locations = WebRequest(
            "https://www.metaweather.com",
            "api/location/search",
            [ lattlong = "36.96,-122.02" ]
        ),
        location_schema = type table[
            distance = number, title = text,
            location_type = text, woeid = number, latt_long = text
        ],
        cityListing = Table.FromRecords(response_locations[response], location_schema, MissingField.Error),
        
        city_mergedRequest = Table.AddColumn(
            cityListing,
            "LocationWeather",
            (row as record) as any =>
                let
                    woeid = Text.From(row[woeid]),
                    response = WebRequest(
                        "https://www.metaweather.com",
                        "api/location/" & woeid,
                        []
                    )
                in
                    response,
            type any
        )
    in
        city_mergedRequest

Html.Table – Parsing with CSS Selectors

Select a Single Element: an Image, url, text, etc…

This fetches the current heading text on the blog

let
    Url = "https://powerbi.microsoft.com/en-us/blog/",
    Response = Web.Contents( Url ),
    /*
    note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors
    Non-tabular scraping like Images or any single elements, does not use a "RowSelector"

    This CSS Selector finds exactly one element, the Page's Header Text

        .section-featured-post .text-heading1 a
    */
    HeaderText = Html.Table(
        Response,
        {
            { "Page Header", ".section-featured-post .text-heading1 a" }
        }
    )
in
    HeaderText

Parsing Element’s Attributes

let
    Url = "https://powerbi.microsoft.com/en-us/blog/",
    Response = Web.Contents( Url ),
    /*
    The 3rd argument in "columnNameSelectorPairs" is the transformation function.
    by default it uses:

            each _[TextContent]
    */
    HeaderAsElement = Html.Table(
        Response,
        { { "Link", ".section-featured-post .text-heading1 a", each _ } }
    ),

    ElementRecord = Table.ExpandRecordColumn(
        HeaderAsElement, "Link",
        {"TagName", "TextContent", "Attributes"}, {"TagName", "TextContent", "Attributes"}
    ),

    ExpandedAttributes = Table.ExpandRecordColumn(
        ElementRecord, "Attributes",
        {"href", "rel", "title"}, {"attr.href", "attr.rel", "attr.title"}
    )
in
    ExpandedAttributes

Select Tables using your own CSS Selectors

// Docs on Enum
let
    Source = "https://docs.microsoft.com/en-us/previous-versions/dynamics/ax-2012/reference/gg841505(v=ax.60)",
    // note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors
    Response = Web.BrowserContents( Source ),
    /*

        Think of "RowSelector" as selecting a table row
        Then for every row, you select "columns" using the "columnNameSelectorPairs" selector
        The combination gives you a table cell.

        For more on CSS Selectors, see: 
    */

    columnNameSelectorPairs = {
        // column names don't matter here, since I'm using .PromoteHeaders
        { "Column1", "TABLE.table > * > TR > :nth-child(1)" },
        { "Column2", "TABLE.table > * > TR > :nth-child(2)" },
        { "Column3", "TABLE.table > * > TR > :nth-child(3)" }
    },

    t1 = Html.Table(
            Response, columnNameSelectorPairs,
            [RowSelector = "TABLE.table > * > TR"]
    ),

    t2 = Table.PromoteHeaders( t1, [PromoteAllScalars = true] ),
    FinalTable = Table.TransformColumnTypes(
            t2,
            { { "Name", type text }, { "Value", Int64.Type }, { "Description", type text} }
        )
in
    FinalTable

SQL Native Query

Parameterized SQL queries with Value.NativeQuery()

let
    Source = Sql.Database("localhost", "Adventure Works DW"),
    Test = Value.NativeQuery(
        Source,
        "SELECT * FROM DimDate 
        WHERE EnglishMonthName=@MonthName",
        [
            MonthName = "March",
            DayName = "Tuesday"
        ]
    )
in
    Test

Other

Cartesian Product

let    
    #"Add Column Pair2" = Table.AddColumn(
        Table_Pairs1, "Pairs2",
        each Table_Pairs2,
        Table.Type
    ),
    #"Expanded Pairs" = Table.ExpandTableColumn(
        #"Add Column Pair2",
        "Pairs2",
        {"Color", "Property"},
        {"Color", "Property"}
    )
in
    #"Expanded Pairs"

Details: https://radacad.com/cartesian-product-in-power-query-multiply-all-sets-of-all-pairs-in-power-bi

Convert Table to JSON

TableTo_Json = (source as table, optional encoding as nullable number) as text =>
        let
            encoding = if encoding <> null then encoding else TextEncoding.Utf8,
            bin = Json.FromValue(source, encoding),
            jsonAsText = Text.FromBinary(bin, encoding)
        in
            jsonAsText

Animals = #table(
    {"Animal", "Id"},
    {{"Cat", 1}, {"Turtle", 2}} ),
TableTo_Json( Animals )

Json output:

[{"Animal":"Cat","Id":1},{"Animal":"Turtle","Id":2}]

Converting a list of items to a CSV string

let
    // Converts a list of any type to text. Works well on most types
    // although to support all cases, it requires more logic
    mixedList = {4, "cat", #date(1999,5,9), 0.4},
    ListAsText = List.Transform(mixedList, each Text.From(_)),
    CsvText = Text.Combine( ListAsText, ", ")
in
    //output: "4, cat, 5/9/1999, 0.4"
    CsvText 

Functions

Using Optional Parameters with Default Values

The Null_coalescing_operator ?? simplifies default values.
encoding will be set to what the user passed, unless it’s null. In that case, it is set to TextEncoding.Utf8

let
    Read_Json = (json as any, encoding as nullable number) as any =>
        // calls Json.Document, using UTF8 by default
        let
            encoding = encoding ?? TextEncoding.Utf8,
            result = Json.Document(json, encoding)
        in
            result
in
    Read_Json

Mapping Function Calls based on Type

Caller chooses which type of conversioin to use, based on type names { date, datetime, datetimezone }

let
    // 1] get a `type` from caller
    // 2] return a difference function based on the type
    GetTransformByType = (_type as type) as function =>
        let
            // originally from: 
            func_transform = 
                 if (Type.Is(_type, type date)) then Date.From
            else if (Type.Is(_type, type datetime)) then DateTime.From
            else if (Type.Is(_type, type datetimezone)) then DateTimeZone.From
            else (t) => t // else return self
        in
            func_transform,

    nowDtz = DateTimeZone.LocalNow(),    

    // invoke in 2 steps
    toDate = GetTransformByType(type date),
    callDate = toDate( DateTimeZone.FixedLocalNow() ),

    // create, and invoke functions
    Results = [
        asDate = (GetTransformByType(type date))( nowDtz ),
        asDateTime = (GetTransformByType(type datetime))( nowDtz ),
        asDateTimeZone = (GetTransformByType(type datetimezone))( nowDtz )
    ]
in
    Results