2 ACCEPTED SOLUTIONS
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
Combine
UserCfgPriorities
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
Expand
ChannelAssignments
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
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
Expand
Updated 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
ExpandUsers
Example:
Make sure that your ranking number columns (HD, FHD, 4K) are of a number column type in UserCfg. E.g.
If, for some reason, those column must be text columns, then you should be able to fix the error inside the GroupAndSort step of UserCfgPriorities by replacing any reference of:
[Value]
With:
Number.From( [Value] )
I believe it only occurs once in GroupAndSort on line 65 from original:
I was thinking that your columns [K], [R], [P], and [Comment] were your desired output/notes, so the query I constructed assumed that the actual Channel input table would not include them. I.e. it looks like the following (I noted this structure at the top of my initial post):
If your Channels table already includes those columns, then that is why it's throwing an error. A table can't have duplicate column names, so when we try to add in our K, R, etc.'s onto a table that already has those columns, it throws that error.
Also, going back to exactly how you had supplied your tables, another difference in UserCfg (which will cause problems with my code as provided) is that I replaced all blank text with nulls. In retrospect, I should have more explicitly called out these column type differences with the "Enter Data" tables you had supplied.
I'm thinking best approach for these differences is to explicitly include "cleanup" steps in the queries:
UserCfgPriorities
(added Cleanup_Ints and Cleanup_Txts steps)
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 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
Expand
ChannelAssignments
(added Cleanup_Cols steps)
let
Source = Channels,
Cleanup_Cols = Table.SelectColumns(Source,{"Chan ID", "Chan Name", "tvg-id"}),
AddType = Table.AddColumn(
Cleanup_Cols, "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(Cleanup_Cols)),
flags = Table.Column(pivot, List.Last(Table.ColumnNames(pivot)))
in
[Base = base, FlagCols = flags],
type [Base = Value.Type(Cleanup_Cols), 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
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
Expand
Updated 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
ExpandUsers
Example:
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
Combine
UserCfgPriorities
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
Expand
ChannelAssignments
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
Helpful resources
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
