Forum Discussion
Needing some help with how to build this table. Custom Columns.
- 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
Let's assume a set-up in Excel as shown below. Table1 contains your data, selection is a named range for the selected PID.
The following M-code will generate the PQ result table:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each [PID] = selection or [PID] = "P4"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"PID", type text}}, "en-001"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"PID", type text}}, "en-001")[PID]), "PID", "Enabled")
in
#"Pivoted Column"
where selection is a parameter based on the named range.
let
Source = Excel.CurrentWorkbook(){[Name="selection"]}[Content],
Column1 = Source{0}[Column1]
in
Column1