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...
Need Help
  • 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
stuntmanpatch's avatar
stuntmanpatch
Regular Visitor
1 year ago

Apologies if my description is vague.  I am learning as I go and it's difficult for me to formalize the problem.  I'll take another stab.  The posts thus far have gotten me close.

 

I have a single table that includes settings for various PIDS.

The settings that are in PID 25416 are considered the default settings.  Ultimatley, I am trying to put a table or display that would compare the settings say from PID 101 or PID 105, to the setting values in PID 25416.

The new column should return the respective value for PID 25416, for the respective Name, ie, setting 1, 2, etc.

Then I'm thinking in a display I can show these columns side by side to do a quick audit from any PID to the default values.

 

I've gotten to this point.  The isse with the below is that I do not know how to do the dynamic piece to pull in the relative value based on the Name.  

As in the below, I am pull in the respective value, but it is the value for Setting1 across the board.

What can i do to make the below pull in the value for the respective Name.

[Name] = [Name]  does not work.

Hope this makes it a little more clear.

Thank you all for the inputs.

 

 

let
Source = Prod_Custom_Toggle_Config,
AMB = 25416,
#"Added Custom" = Table.AddColumn(Source, "StandardAMB", each List.Max(Table.SelectRows(Source, each AMB = [PID] and [Name]= "Setting1")[IsEnabled]))
in
#"Added Custom"

 

 

 

  • jgeddes's avatar
    jgeddes
    Super User
    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
    • stuntmanpatch's avatar
      stuntmanpatch
      Regular Visitor
      1 year ago

      Hi jgeddes,  You helped me a lot with your solution you added.  It works great.  I'm hoping I can expand the requirement a little.  The code you shared assumed that defaultPID was a fixed 25416.

      What if I had 3 options , 25416, 25418, 25420.
      The user will select in the display one of the above.  Is is even possible to have the table column dynamically update based on which option the user selected.

      The PID is a variable.  Technically, I guess I can add 3 new custom columns, one for each PID but I don't think that would force me to have to include more columns in the display that I am trying to avoid.

      Thank you for any insights.

      • jgeddes's avatar
        jgeddes
        Super User
        1 year ago

        You can modify the Power Query code to allow for user selection but if you were to allow your users to pick a PID option from a list in report view they would have to be able to refresh the report in order for the Power Query to update.
        That is not usually an option for most reports so you may be able to recreate the solution in DAX so it can exist in the report view without needing to refresh the dataset with each option selection.
        One way to do this in DAX would be to create another table that contains the PID options. (25416, 25418, 25420 - make sure the data type matches the type in the original table).

        You can then use the selected value of the new table in a measure that mimics the Power Query code. 
        Here is an example of DAX code that does this...

        _DefaultEnabled = 
var _rTable = 
SELECTCOLUMNS(
    FILTER(
        all(Query1),
        [PID] = SELECTEDVALUE('Table'[PID])
    ),
    "Name", [Name],
    "Enabled", [Enabled]
)
var _lTable = 
SELECTCOLUMNS(
    Query1,
    "PID", [PID],
    "Name", [Name]
)
var _jTable = 
NATURALLEFTOUTERJOIN(_lTable, _rTable)
RETURN
MINX(_jTable,[Enabled])

        NOTE: Table[PID] is the new table created with the PID options.