Forum Discussion

stuntmanpatch's avatar
stuntmanpatch
Regular Visitor
1 year ago
Solved

Needing some help with how to build this table. Custom Columns.

I am struggling with a solution for this challenge, hoping for some inputs.  I'm trying to build a new table that consolidates the rows down to the uniques Names, then custom column to pull in the da...
  • jgeddes's avatar
    jgeddes
    1 year ago

    This something like this what you are looking for?

    let
        defaultPID = "25416",
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRCk4tKcnMSwcxI5ViddCEjbALGwOZfpjCJkiqTVHN9sMUNsIubIzdEBO4aiNTE0MzbC5HkzDCpcMYlw6oHbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PID = _t, Name = _t, Enabled = _t]),
        set_types = 
        Table.TransformColumnTypes(
            Source,
            {
                {"PID", Int64.Type}, 
                {"Name", type text}, 
                {"Enabled", type text}
            }
        ),
        defaultTable = 
        Table.SelectRows(Source, each [PID] = defaultPID),
        merge_tables = 
        Table.NestedJoin(
            Source, 
            {"Name"}, 
            defaultTable, 
            {"Name"}, 
            "Source", 
            JoinKind.LeftOuter
        ),
        expand_default_enabled = 
        Table.ExpandTableColumn(
            merge_tables, 
            "Source", 
            {"Enabled"}, 
            {"DefaultEnabled"}
        ),
        sort_table = 
        Table.Buffer(
            Table.Sort(
                expand_default_enabled,
                {
                    {"PID", Order.Ascending}, 
                    {"Name", Order.Ascending}
                }
            )
        )
    in
        sort_table