Forum Discussion
Needing some help with how to build this table. Custom Columns.
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
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"
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_tableHi 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.
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.
Thank you for all your help. I've been trying to solve for this one last item but cannot seem to get it right. I was able to replicate your setup and that helped a ton. I am able to choose one of the model PIDs and the _defaultenabled column will update accordingly.
Ultimately, I'm hoping to compare one PID on the left against one of the model PIDs.
The piece I cannot figure out is now how can I select a PID on the left to compare against.An option to select say PID 105, and have it filter down to PID 105 compared against PID 25418 in the _defaultenabled column.
I think I know the problem, just can seem to get it right. If I put a PID Slicer from the full table, anything I select will blank out the compare table. I'm guessing because we're explicitly filtering on a PID. Or, another way, trying to filter based on two PIDs. I've been trying to edit interactions but not getting anywhere.
Is is possible now to add another PID filter for all the PIDs so that a user can choose the PID, then choose the model PID, and see the display as one against the other.
For context, there are over 2000 PIDs, 3 Model PIDs, and 177 Settings, making this a really large display.
I appreciate your help a ton...