Forum Discussion
stuntmanpatch
1 year agoRegular Visitor
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...
- 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
jgeddes
10 months agoSuper User
To do this you would need two separate tables for slicing PIDs. The table for selecting the default PID should not be related to the main table. The table for selecting the comparison PID can be related to the main table.
I attached a pbix file with this example model.
stuntmanpatch
10 months agoRegular Visitor
Thank you!!! jgeddes That did the trick. My err was trying to associate the new Default PID table to the matrix. Once I pulled out that mapping it all flows as expected. Thanks again.