Generating tables PowerQuery

func SummarizeList.pq

let
    /* summarize list of numbers or anything as a csv-like value

    Example:
        SummarizeList({0..3})

    Output:
        {0, 1, 2, 3}

    Example:
        SummarizeList( {"a", 34, 234.5, DateTime.LocalNow() } )

    Output:
        { a, 34, 234.5, 7/12/2020 7:45:00 PM }

    */
    SummarizeList = (source as list) as text =>
        let
            TextList = List.Transform(
                source,
                (element) => Text.From( element )
            ),
            String = Text.Combine(TextList, ", "),
            Result = "{ " & String & " }"
        in
            Result

in
    SummarizeList

func TableToJson.pq

let
    /* converts existing table to Json
    May require extra cleanup depending on datatype compatibility with Json

    Usage:
        TableToJson( TableName )

    */
    TableToJson = (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
in
    TableToJson

gen Datetimes.pq

let
    // generate a configurable amount of datetimes
    Config = [
        DateTimeStart = #datetime(2020, 1, 1, 0, 0, 0),
        DateTimeStep =  #duration(0, 7, 47, 59),
        StepCount = 200
    ],

    listDatetimes = List.DateTimes(
        Config[DateTimeStart], Config[StepCount], Config[DateTimeStep]
    )
in
    listDatetimes

manual TableToJson.pq

let
    // manually convert a table to Json
    // see: `func TableToJson.pq`
    Source = SourceTable,

    bin = Json.FromValue(
        Source,
        TextEncoding.Utf8
    ),

    #"CopyPasteable Json" = Text.FromBinary(
        bin,
        TextEncoding.Utf8
    )
in
    #"CopyPasteable Json"

RandomSales.pq

let
    Source = #"gen DateTimes",

    TableDates = Table.FromList(
        Source,
        Splitter.SplitByNothing(),
        {"SaleDate"},
        null,
        ExtraValues.Error
    ),

    // warning: Random tables in PowerQuery require index or
    // other operations that force evaluation
    // otherwise lazy-evaluation ends up with the same value
    // preview mode can appear correct until you hit apply

    #"Random Sales" = Table.AddColumn(
        TableDates,
        "Value",
        (row) => Number.RandomBetween(-40, 23),
        Currency.Type
    ),
    AddIndex = Table.AddIndexColumn(
        #"Random Sales", "SaleId", 0, 1
    )
in
    AddIndex

screenshot.png

Table Data.json

[
    {
        "State": "California",
        "Population": 39512223
    },
    {
        "State": "Hawaii",
        "Population": 1415872
    },
    {
        "State": "Wyoming",
        "Population": 578759
    }
]

Table From Json.pq

let
    PathJson = "C:\Users\username\Documents\Power BI\Table Data.json",
    SourceJson = Json.Document(File.Contents(PathJson)),

    Source = Table.FromList(
        SourceJson,
        Splitter.SplitByNothing(),
        null, null, ExtraValues.Error
    ),

    FinalTable = Table.ExpandRecordColumn(
        Source,
        "Column1",
        {"State", "Population"},
        {"State", "Population"}
    )
in
    FinalTable

Table From Record.pq

let
    RecordList = {
        [State = "California", Population = 39512223 ],
        [State = "Hawaii", Population = 1415872 ],
        [State = "Wyoming", Population = 578759 ]
    },

    Table = Table.FromRecords(
        RecordList,
        {"State", "Population"}
    ),
    Source = Table.TransformColumnTypes(
        Table,
        {
            {"Population", Int64.Type}
        }
    )

in
    Source

Table literal.pq

let
    /*
    param1: column names
    param2: a list of lists (values)
    */

    source = #table(
        { "Name", "Id"},
        {
            { "North", 1 },
            { "West", 2 },
            { "South", 1 }
        }
    )
in
    source

TransformLists.pq

let
    /*
    Start with a generated list, then transform it
    output:

        { 0, 1, 2, 3, 4, 5, 6, 7 }
        { 1, 2, 4, 8, 16, 32, 64, 128 }

    */
    NumberList = {0..7},
    PowersOfTwo = List.Transform(
        NumberList,
        (power) => Number.Power(2, power)
    ),
    Results = [
        Numbers = SummarizeList( NumberList ),
        PowersOfTwo = SummarizeList( PowersOfTwo )
    ]
in
    Results