Forum Discussion
Selecting rows based on the ranking from another table
- 1 year ago
I think I've come up with a solution. This approach involves defining a default sort list. This is specifically for cases where the Cfg criteria don't match any channels within tvg-id and there are multiple channels to decide between. For each user in Cfg, we generate the full sort for all possible channels where the Cfg selections override the defaults wherever they overlap. We then join the sorts to Channels, select the match with highest priority (i.e. lowest assigned number), and pivot the results.
Given the following tables:
Channels
Chan ID Chan Name tvg-id 211 Channel 2 HD 1042 10027 Channel 2 HD 50 1042 290 Channel 2 HD orig 1042 2420 Channel 2 FHD 1042 530 Channel 2 1042 5071 Channel 4 HD 50 orig 2005 206 Channel 4 HD orig 2005 10016 Channel 4 HD 50 2005 2408 Channel 4 FHD 2005 522 Channel 4 2005 5072 Channel 1 HD 50 orig 2044 2407 Channel 1 FHD 50 orig 2044 10015 Channel 1 HD 50 2044 124 Channel 1 2044 222 Channel 1 HD 2044 343 Channel 5 2045 3234 Channel 7 HD 234 4245 Channel 7 FHD 234 7654 Channel 8 UFHD 2113 3667 Channel 3 4K 965 UserCfg
Note: added some additional users for testing, has some changes from what you provided, I think
User HD FHD orig 4K 50 Regular K 2 1 x 4 x 3 R 2 null null null null 1 P 1 2 x null null 3 Q 1 2 x 3 x 4 Z null null null null null null Y null null x null null null W null null null null x null Here is the M code.
DefaultSort
Note: This is a list, not a table. Not meant to be loaded. I generated this with some code, but you could also manually/simply dictate it with = { "HD", "FHD", "4K", ... }
let Type1 = {"HD", "FHD", "4K", "UFHD", null}, Type2 = {"50", null}, Type3 = {"orig", null}, initRows = List.Count(Type1), Crossjoin = Table.FromColumns( {Type1, List.Repeat({Type2}, initRows), List.Repeat({Type3}, initRows)}, type table [Type1 = text, Type2 = {text}, Type3 = {text}] ), ExpandTypes = Table.ExpandListColumn(Table.ExpandListColumn(Crossjoin, "Type2"), "Type3"), Sort = Table.Sort( ExpandTypes, {{"Type3", Order.Ascending}, {"Type2", Order.Ascending}, {"Type1", Order.Ascending}} ), Combine = Table.CombineColumns( Sort, Table.ColumnNames(Sort), (_) as text => Text.Combine(_, " "), "combined" )[combined] in CombineUserCfgPriorities
let Source = UserCfg, AddCombo = Table.AddColumn(Source, "50 orig", each if [50] = "x" and [orig] = "x" then "x" else null, type text), MergeFirstToAttr = Table.FromRecords( Table.TransformRows( AddCombo, (row) => let firstValPos = List.PositionOf(Record.FieldValues(row), 1), firstVal = Record.FieldNames(row){List.PositionOf(Record.FieldValues(row), 1)}?, first = if firstValPos = -1 or firstVal = "Regular" then "" else firstVal & " " in Record.TransformFields( row, { { "50", each if row[50 orig] <> null then null else if _ = "x" then first & "50" else null }, { "orig", each if row[50 orig] <> null then null else if _ = "x" then first & "orig" else null }, {"50 orig", each if _ = "x" then first & "50 orig" else null}, {"Regular", each if firstValPos = -1 then 1 else _ } } ) ), Value.Type(AddCombo) ), Unpivot = Table.UnpivotOtherColumns(MergeFirstToAttr, {"User"}, "Attribute", "Value"), GroupAndSort = let newType = type table Type.ForRecord( Record.RemoveFields(Type.RecordFields(Type.TableRow(Value.Type(Unpivot))), {"Value"}) & [ Priority = [Type = Int64.Type, Optional = false] ], false ), sortFirstList = {"50 orig", "50", "orig"}, sortFirstSize = List.Count(sortFirstList), defaults = List.Buffer(DefaultSort) in Table.Group( Unpivot, "User", { "groups", each let newSort = Table.Sort( _, each let first = List.PositionOf(sortFirstList, [Attribute]) in if first = -1 then (sortFirstSize + [Value]) else first ), addPriority = Table.AddIndexColumn(newSort, "Priority", 1), mergeCols = Table.CombineColumns( addPriority, {"Attribute", "Value"}, (_) as text => if _{1} is number then if _{0} = "Regular" then "" else _{0} else _{1}, "Attribute" ), leftoverDefaults = List.Difference(defaults, mergeCols[Attribute]), leftoverCount = List.Count(leftoverDefaults), thisUser = List.First([User]), maxVal = List.Max(addPriority[Priority]), leftoverRows = Table.FromColumns( { List.Repeat({thisUser}, leftoverCount), leftoverDefaults, List.Transform(List.Positions(leftoverDefaults), each _ + 1 + maxVal) }, newType ), comboRows = Table.Combine({mergeCols, leftoverRows}) in comboRows, newType } ), Expand = Table.Combine(GroupAndSort[groups]) in ExpandChannelAssignments
let Source = Channels, AddType = Table.AddColumn( Source, "Chan Type", each Text.Trim(Text.AfterDelimiter([Chan Name], " ", 1)), type text ), MergeCfgOnType = Table.NestedJoin( AddType, {"Chan Type"}, UserCfgPriorities, {"Attribute"}, "UserCfg", JoinKind.LeftOuter ), ExpandCfg = Table.ExpandTableColumn( MergeCfgOnType, "UserCfg", {"User", "Priority"}, {"UserCfg.User", "UserCfg.Priority"} ), GroupIdCfg = Table.Group( ExpandCfg, {"tvg-id", "UserCfg.User"}, {{ "rows", each let tbl = Table.Sort(_, {{"tvg-id", Order.Ascending}, {"Chan Name", Order.Descending}}), priorityVal = List.Min([UserCfg.Priority]), flagPriority = Table.TransformColumns( tbl, {{"UserCfg.Priority", each if _ = priorityVal then "x" else null, type text}} ), pivot = Table.Pivot( flagPriority, List.Distinct(flagPriority[UserCfg.User]), "UserCfg.User", "UserCfg.Priority" ), base = Table.FromRecords(Table.ToRecords(pivot), Value.Type(Source)), flags = Table.Column(pivot, List.Last(Table.ColumnNames(pivot))) in [Base = base, FlagCols = flags], type [Base = Value.Type(Source), FlagCols = {text}] }} ), ExpandGroupData = Table.ExpandRecordColumn(GroupIdCfg, "rows", {"Base", "FlagCols"}, {"Base", "FlagCols"}), GroupOnBase = Table.Group( ExpandGroupData, {"Base"}, {{"UserFlags", each [ColNames = [UserCfg.User], Cols = [FlagCols]], type [ColNames = text, Cols = {text}]}} ), CombineOutputParts = Table.Combine( Table.TransformRows( GroupOnBase, (row) => let base = row[Base], flags = row[UserFlags] in Table.FromColumns(Table.ToColumns(base) & flags[Cols], Table.ColumnNames(base) & flags[ColNames]) ) ) in CombineOutputParts - 1 year ago
I think I undertand. The main practical way to incorporate this with the approach we built up would be to:
In UserCfgPriorities, remove all instances of the negative attributes, so for the given user, no attribute with the negative modifier will get merged later on in ChannelAssignments. Mostly, this involves updating GroupAndSort step to filter out the negative attributes.
Additionally, doing this will mess up our latter grouping logic in ChannelAssignments, so we'll have to update the code there, too, but the overall functionality is not changing.
I used -1 as the value to specify to remove an attribute from prioritization. This is just because it can be used in both types of columns, either as -1 in our integer columns (with numbered priorities) or as "-1" in the text columns (with "x"s).
Updated UserCfgPriorities:
let Source = UserCfg, Cleanup_Ints = Table.TransformColumnTypes( Source, { {"HD", Int64.Type}, {"FHD", Int64.Type}, {"4K", Int64.Type}, {"Regular", Int64.Type} } ), Cleanup_Txts = Table.ReplaceValue( Cleanup_Ints, "", null, Replacer.ReplaceValue, {"orig", "50"} ), AddCombo = Table.AddColumn( Cleanup_Txts, "50 orig", each if [50] = "x" and [orig] = "x" then "x" else null, type text ), MergeFirstToAttr = Table.FromRecords( Table.TransformRows( AddCombo, (row) => let firstValPos = List.PositionOf(Record.FieldValues(row), 1), firstVal = Record.FieldNames(row){firstValPos}, first = if firstValPos = -1 or firstVal = "Regular" then "" else firstVal & " " in Record.TransformFields( row, { { "50", each if row[50 orig] <> null then null else if _ = "x" then first & "50" else _ }, { "orig", each if row[50 orig] <> null then null else if _ = "x" then first & "orig" else _ }, { "50 orig", each if _ = "x" then first & "50 orig" else null }, { "Regular", each if _ = -1 then -1 else if firstValPos = -1 then 1 else _ } } ) ), Value.Type(AddCombo) ), Unpivot = Table.UnpivotOtherColumns(MergeFirstToAttr, {"User"}, "Attribute", "Value"), GroupAndSort = let newType = type table Type.ForRecord( Record.RemoveFields( Type.RecordFields(Type.TableRow(Value.Type(Unpivot))), {"Value"} ) & [ Priority = [Type = Int64.Type, Optional = false] ], false ), sortFirstList = {"50 orig", "50", "orig"}, sortFirstSize = List.Count(sortFirstList), defaults = List.Buffer(DefaultSort) in Table.Group( Unpivot, "User", { "groups", each [ splitRemovals = Table.Partition( _, "Value", 2, each Number.From( _ <> -1 and _ <> "-1" ) ), removeAttr = List.ReplaceValue( splitRemovals{0}[Attribute], "Regular", "", Replacer.ReplaceText ), defaultsRemove = List.Select( defaults, each not List.ContainsAny( Text.Split(_," "), removeAttr ) ), newSort = Table.Sort( splitRemovals{1}, each let first = List.PositionOf(sortFirstList, [Attribute]) in if first = -1 then sortFirstSize + [Value] else first ), addPriority = Table.AddIndexColumn(newSort, "Priority", 1), mergeCols = Table.CombineColumns( addPriority, {"Attribute", "Value"}, (_) as text => if _{1} is number then if _{0} = "Regular" then "" else _{0} else _{1}, "Attribute" ), leftoverDefaults = List.Difference( defaultsRemove, mergeCols[Attribute] ), leftoverCount = List.Count(leftoverDefaults), thisUser = List.First([User]), maxVal = List.Max(addPriority[Priority]), leftoverRows = Table.FromColumns( { List.Repeat({thisUser}, leftoverCount), leftoverDefaults, List.Transform( List.Positions(leftoverDefaults), each _ + 1 + maxVal ) }, newType ), comboRows = Table.Combine({mergeCols, leftoverRows}) ] [comboRows], newType } ), Expand = Table.Combine(GroupAndSort[groups]) in ExpandUpdated ChannelAssignments:
let Source = Channels, Cleanup_Cols = Table.SelectColumns(Source,{"Chan ID", "Chan Name", "tvg-id"}), DefaultSortSimpleParts = List.Buffer( List.Select( DefaultSort, each not Text.Contains( _, " " ) ) ), AddType = Table.AddColumn( Cleanup_Cols, "Chan Type", each Text.Combine( List.Intersect( { Text.Split( [Chan Name], " " ), DefaultSortSimpleParts } ), " " ) , type text ), MergeCfgOnType = Table.NestedJoin( AddType, {"Chan Type"}, UserCfgPriorities, {"Attribute"}, "UserCfg", JoinKind.LeftOuter ), ExpandCfg = Table.ExpandTableColumn( MergeCfgOnType, "UserCfg", {"User", "Priority"}, {"UserCfg.User", "UserCfg.Priority"} ), GroupIdCfg = Table.Group( ExpandCfg, {"tvg-id", "UserCfg.User"}, {{ "rows", each [ tbl = Table.Sort( _, {{"tvg-id", Order.Ascending}, {"Chan Name", Order.Descending}} ), priorityVal = List.Min([UserCfg.Priority]), flagPriority = Table.TransformColumns( tbl, {{ "UserCfg.Priority", each if _ = priorityVal then "x" else null, type text }} ), pivot = Table.Pivot( flagPriority, List.Distinct(flagPriority[UserCfg.User]), "UserCfg.User", "UserCfg.Priority" ) ][pivot] }} ), CombineMerges = Table.Combine( GroupIdCfg[rows] ), GroupAndMergeAssignments = Table.Group( CombineMerges, {"Chan ID", "Chan Name", "tvg-id", "Chan Type"}, {{ "rows", each [ unpiv = Table.UnpivotOtherColumns( _, {"Chan ID", "Chan Name", "tvg-id", "Chan Type"}, "colname", "colval" ), repiv = Table.Pivot( unpiv , List.Distinct(unpiv[colname]), "colname", "colval", List.Max ) ][repiv], Value.Type( CombineMerges ) }} ), ExpandUsers = Table.ExpandTableColumn( GroupAndMergeAssignments, "rows", UserCfg[User] ) in ExpandUsersExample:
Hello
In my sample data i have provided simple naming for channels. In reality there is a variety of naming principles. In the ChannelAssignments query AddType step i see that you've been using a Text.AfterDelimiter to split the channel quality from channel name. In fact there can be channel names with multiple words and spaces before the quality.
Here are some samples:
Channel sport basket HD
Basket HD 50
Basket sport HD 50 orig
Sport channel
The quality of the channel will be always on the right-end side of the name. Can you please modify the query so it would check the channel name from the right side and look for the quality information? I've tried column from examples feature but it did not understand the logic. Or maybe you have better ideas.
Thanks
OK now it seems that ChatGPT was able to handle this issue. I've asked it to update query and use DefaultSort list data to split the channel name from the quality. I'm now continuing checking the results and will let you know if this is working.
- bigk1 year agoHelper III
Hello MarkLaf
I was able to use your solution and solve 95% of the task. But i've noticed that there are some uncommon channel names for which the rules should be defined or query adjusted.
- Name may contain the quality letters but is not actual quality. For example Chan13HD HD where the Chan13HD is the name but the latter HD is the quality.
- Other problem is that some channels may have extra letters after the quality. Like Chan 12 HD US.
To solve the first problem, i was thinking to modify query by adding a space " " when trimming off the quality. This way if the quality is adjanced to the name, it won't be considered as a quality.
For solving second problem, the query should look for the quality inside the whole channel name and not the end. At least that is my understanding how it works. I could not find what to modify to make it working.
Here is adjusted channels query including these 2 new cases for your testing:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVZNb6MwEP0rI87dCIxt4LyraKVeVqlySnqgCUnQUrPCpqH/fsc2EIPz0VvVqpHJezNv3hsn3WwCEkXBU/DzlAtRVEDg9y88RiEl+CLaqvJegtenDQJCksx4wMKvUEkWzol1Ux5vUbuRR8mUuHSldh5xLYsGnuGUS1CnUsJbsctbWWganEt1Ml0hF3vUDblSTfnWqkLCuWgKELWCQ92K/QJeauRbWpOLv3DGglhnb6ilMufIKGTxRKA3UTdzgoWJ6z21Hg5ukDBk940M+Zx9jzoaidlFHtNk97glDdMJ02bQ874jAzmLgBBXnzfTlQhcQjSPgNJHfiQT+vIa3/elvfjynqsd+nBAb6Sy0x3q5uKQ44pj1hgk89V/RXhEqEv0KHOfCJnbdKtLN8T+x499mMkN24TnR/0w6ZjGjiJm5Qxb2DlCJBqtTdN6PoqpoG2wKo5tlTfbAJUJCUaxXr5Si4JaH2xEpTjCrq7ad0SZIU7lUb+pVVpBJHY9TXqP4pmroz8r3x+39a6vo065sgpwA1y9TVsVZjBtlyi6fnmQjd7pFVIfxx/lHo5N3f4bykkjlRLKJlKXjlZ/Ww0n4cwdL4V1T4qieOa6HSM3V3iUjhuei0+jWlp5KHsYUp/1u+ZeyFonrc3ZBh0OOriDD3G4WlSf+GfK1bXccW0gnLuXMwb6jMeMz5fk++Wah63Z1ZXd1QUs+0e4vX35qj73+wZ4cUCqsqrsDtXYFEqxq9r9eFNu9nb6LoxPnPKED/sA2fDhhXnrb4nLr8ESTkbkTRBn/FLObMlVXJZQTifIu73TOB17R6Gt64FInGXUwUXXQDTDnxFi/gVZv+CZMpb6YH4ZGbG3gVmakOEzKYqNvJTR5ErBmGUOzpn7Kn5SdPoF5eNf/wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Chan ID" = _t, #"Chan Name" = _t, #"tvg-id" = _t, K = _t, R = _t, P = _t, Comment = _t]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"K", "K_"}, {"R", "R_"}, {"P", "P_"}, {"Comment", "Comment_"}})
in
#"Renamed Columns"And the UserCfg where i've changed the priority for user K to have the HD as a priority:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lbSUTIEYmMghiMjpVidaKUgMAtJGKwUJBMAlTFEljJWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, HD = _t, FHD = _t, orig = _t, #"4K" = _t, #"50" = _t, Regular = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"HD", Int64.Type}, {"FHD", Int64.Type}, {"orig", type text}, {"4K", type text}, {"50", type text}, {"Regular", Int64.Type}})
in
#"Changed Type"Thanks.
- MarkLaf1 year agoSuper User
On how AddType currently works, we are just grabbing all the text to the right of the second " " as I was just assuming your base channel names all followed syntax, "Channel # [modifiers]". But since it's instead more like "Channel[ ]?# [modifiers] [extra]", we can't rely on that logic.
Instead, we can rely on DefaultSort having all the individual modifiers to check against and pull out from the original [Chan Name]. Try the following variation of AddType:
let Source = <...>, // same Cleanup_Cols = <...>, // same // new step DefaultSortSimpleParts = List.Buffer( List.Select( DefaultSort, each not Text.Contains( _, " " ) ) ), // modified step AddType = Table.AddColumn( Cleanup_Cols, "Chan Type", each Text.Combine( List.Intersect( { Text.Split( [Chan Name], " " ), DefaultSortSimpleParts } ), " " ) , type text ), MergeCfgOnType = <...>, // same <...> // sameSo, we first get the short list of individual modifiers in DefaultSortSimpleParts. Then, we split [Chan Name] by space to get all its parts, and extract all the parts that match the our sanctioned list of simple parts. Basically, this removes anything not in our DefaultSort, stripping out channel name and any extra text like "US" etc.
- bigk1 year agoHelper III
Hi MarkLaf
Thanks. This worked. Can you still help me to make some rules to exclude channels. I was thinking if i would put some specific characters in any of the quality column for each user, then this specific quality will be excluded and all related channels will be excluded too. This includes both main qualities like HD, 4K etc. and also additional attributes like 50 and orig. For example if i enter 0 or "no" or anything then it would be excluded. I just cannot find at which step make the logic.
THanks
- MarkLaf1 year agoSuper User
I think I undertand. The main practical way to incorporate this with the approach we built up would be to:
In UserCfgPriorities, remove all instances of the negative attributes, so for the given user, no attribute with the negative modifier will get merged later on in ChannelAssignments. Mostly, this involves updating GroupAndSort step to filter out the negative attributes.
Additionally, doing this will mess up our latter grouping logic in ChannelAssignments, so we'll have to update the code there, too, but the overall functionality is not changing.
I used -1 as the value to specify to remove an attribute from prioritization. This is just because it can be used in both types of columns, either as -1 in our integer columns (with numbered priorities) or as "-1" in the text columns (with "x"s).
Updated UserCfgPriorities:
let Source = UserCfg, Cleanup_Ints = Table.TransformColumnTypes( Source, { {"HD", Int64.Type}, {"FHD", Int64.Type}, {"4K", Int64.Type}, {"Regular", Int64.Type} } ), Cleanup_Txts = Table.ReplaceValue( Cleanup_Ints, "", null, Replacer.ReplaceValue, {"orig", "50"} ), AddCombo = Table.AddColumn( Cleanup_Txts, "50 orig", each if [50] = "x" and [orig] = "x" then "x" else null, type text ), MergeFirstToAttr = Table.FromRecords( Table.TransformRows( AddCombo, (row) => let firstValPos = List.PositionOf(Record.FieldValues(row), 1), firstVal = Record.FieldNames(row){firstValPos}, first = if firstValPos = -1 or firstVal = "Regular" then "" else firstVal & " " in Record.TransformFields( row, { { "50", each if row[50 orig] <> null then null else if _ = "x" then first & "50" else _ }, { "orig", each if row[50 orig] <> null then null else if _ = "x" then first & "orig" else _ }, { "50 orig", each if _ = "x" then first & "50 orig" else null }, { "Regular", each if _ = -1 then -1 else if firstValPos = -1 then 1 else _ } } ) ), Value.Type(AddCombo) ), Unpivot = Table.UnpivotOtherColumns(MergeFirstToAttr, {"User"}, "Attribute", "Value"), GroupAndSort = let newType = type table Type.ForRecord( Record.RemoveFields( Type.RecordFields(Type.TableRow(Value.Type(Unpivot))), {"Value"} ) & [ Priority = [Type = Int64.Type, Optional = false] ], false ), sortFirstList = {"50 orig", "50", "orig"}, sortFirstSize = List.Count(sortFirstList), defaults = List.Buffer(DefaultSort) in Table.Group( Unpivot, "User", { "groups", each [ splitRemovals = Table.Partition( _, "Value", 2, each Number.From( _ <> -1 and _ <> "-1" ) ), removeAttr = List.ReplaceValue( splitRemovals{0}[Attribute], "Regular", "", Replacer.ReplaceText ), defaultsRemove = List.Select( defaults, each not List.ContainsAny( Text.Split(_," "), removeAttr ) ), newSort = Table.Sort( splitRemovals{1}, each let first = List.PositionOf(sortFirstList, [Attribute]) in if first = -1 then sortFirstSize + [Value] else first ), addPriority = Table.AddIndexColumn(newSort, "Priority", 1), mergeCols = Table.CombineColumns( addPriority, {"Attribute", "Value"}, (_) as text => if _{1} is number then if _{0} = "Regular" then "" else _{0} else _{1}, "Attribute" ), leftoverDefaults = List.Difference( defaultsRemove, mergeCols[Attribute] ), leftoverCount = List.Count(leftoverDefaults), thisUser = List.First([User]), maxVal = List.Max(addPriority[Priority]), leftoverRows = Table.FromColumns( { List.Repeat({thisUser}, leftoverCount), leftoverDefaults, List.Transform( List.Positions(leftoverDefaults), each _ + 1 + maxVal ) }, newType ), comboRows = Table.Combine({mergeCols, leftoverRows}) ] [comboRows], newType } ), Expand = Table.Combine(GroupAndSort[groups]) in ExpandUpdated ChannelAssignments:
let Source = Channels, Cleanup_Cols = Table.SelectColumns(Source,{"Chan ID", "Chan Name", "tvg-id"}), DefaultSortSimpleParts = List.Buffer( List.Select( DefaultSort, each not Text.Contains( _, " " ) ) ), AddType = Table.AddColumn( Cleanup_Cols, "Chan Type", each Text.Combine( List.Intersect( { Text.Split( [Chan Name], " " ), DefaultSortSimpleParts } ), " " ) , type text ), MergeCfgOnType = Table.NestedJoin( AddType, {"Chan Type"}, UserCfgPriorities, {"Attribute"}, "UserCfg", JoinKind.LeftOuter ), ExpandCfg = Table.ExpandTableColumn( MergeCfgOnType, "UserCfg", {"User", "Priority"}, {"UserCfg.User", "UserCfg.Priority"} ), GroupIdCfg = Table.Group( ExpandCfg, {"tvg-id", "UserCfg.User"}, {{ "rows", each [ tbl = Table.Sort( _, {{"tvg-id", Order.Ascending}, {"Chan Name", Order.Descending}} ), priorityVal = List.Min([UserCfg.Priority]), flagPriority = Table.TransformColumns( tbl, {{ "UserCfg.Priority", each if _ = priorityVal then "x" else null, type text }} ), pivot = Table.Pivot( flagPriority, List.Distinct(flagPriority[UserCfg.User]), "UserCfg.User", "UserCfg.Priority" ) ][pivot] }} ), CombineMerges = Table.Combine( GroupIdCfg[rows] ), GroupAndMergeAssignments = Table.Group( CombineMerges, {"Chan ID", "Chan Name", "tvg-id", "Chan Type"}, {{ "rows", each [ unpiv = Table.UnpivotOtherColumns( _, {"Chan ID", "Chan Name", "tvg-id", "Chan Type"}, "colname", "colval" ), repiv = Table.Pivot( unpiv , List.Distinct(unpiv[colname]), "colname", "colval", List.Max ) ][repiv], Value.Type( CombineMerges ) }} ), ExpandUsers = Table.ExpandTableColumn( GroupAndMergeAssignments, "rows", UserCfg[User] ) in ExpandUsersExample: