using CSS selectors in Power BI.pq

using CSS selectors in Power BI.md

New Date.FromText feature

Recently they added a custom date format strings. ( They've had format strings on ToText ) Now they have format strings on FromText )

let
    dt =  DateTime.FromText("2001-01-01T05:09:23",
        [Format = "yyyy-MM-ddTHH:mm:ss", Culture = "en-us"])
in
    dt

Using JSON

It requires you to know or find out which url to use, url, but it'll work in Excel because it's JSON but it works in Excel.

let 
    Source = Json.Document(Web.Contents("https://www.ameren.com/api/ameren/promotion/RtpHourlyPricesbyDate?SelectedDate="&"2021-12-11")),
    jsonTable = Table.FromRecords( Source[hourlyPriceDetails] ),
    transformTypes = Table.TransformColumnTypes( jsonTable, { {"hour", Int64.Type}, {"date", type datetime}, {"price", Currency.Type}}),
    dropTimes = Table.TransformColumns( transformTypes, {{"date", DateTime.Date, type date}})

in
    dropTimes

Using CSS Selectors

I was super verbose with indenting, to highlight the selectors are mostly exactly equal. The only difference is which column to use

let
    /*        
    requrires Power BI for "Web.Contents"
    */
    Source = Web.BrowserContents("https://www.ameren.com/illinois/account/customer-service/bill/power-smart-pricing/prices"),

    rootSelector = "TABLE.table.table-hover.table-striped > * > TR",
    columnsSelector = {
        { 
          "Column1", rootSelector & " > :nth-child(1)"
        },
        {
           "Column2", rootSelector & " > :nth-child(2)"
        }
    },
    #"Extracted Table From Html" = Html.Table(
        Source, columnsSelector,
        [
            RowSelector = rootSelector
        ]
    ),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hour", type time}, {"Price", type text}})
in
    #"Changed Type"

original example

let 
    Source = Json.Document(Web.Contents("https://www.ameren.com/api/ameren/promotion/RtpHourlyPricesbyDate?SelectedDate="&"2021-12-11")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"hourlyPriceDetails"}),
    #"Expanded hourlyPriceDetails" = Table.ExpandListColumn(#"Removed Other Columns", "hourlyPriceDetails"),
    #"Expanded hourlyPriceDetails1" = Table.ExpandRecordColumn(#"Expanded hourlyPriceDetails", "hourlyPriceDetails", {"hour", "date", "price"}, {"hour", "date", "price"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded hourlyPriceDetails1",{{"hour", Int64.Type}, {"price", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "date", Splitter.SplitTextByEachDelimiter({"T"}, QuoteStyle.Csv, false), {"date.1", "date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"date.1", type date}, {"date.2", type time}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"date.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"date.1", "date"}})
in
    #"Renamed Columns"

tips

After you import JSON, it's just like any nested table, where you can drill down.

Drill down until you get a list of records. That's why I used Source[hourlyPriceDetails]

Datetime (even though it's zero), is the standard format, so PQ will convert it. If you

If you use the column notation, like Source[ColName] it returns a type list of records.

• Table.SelectColumns() : returns table
• Table.Column() : returns list
• Source[ColName] : returns list

When you have table-like-data, but you aren't splitting text on all values, you probably want Table.FromRecords()

• record key names determine column names
• record values become the values