Forum Discussion

bigk's avatar
bigk
Helper III
1 year ago
Solved

Selecting rows based on the ranking from another table

Hello community. I've been struggling to get solution using ChatGPT for 3 days so i'm turning to humans. Please help me to solve the puzzle.

 

I have 2 tables in excel powerquery:

Table "Channels" with sample expected results and comments

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZNBb6MwEIX/yohzG4ExSXvuKqrUyyqrnpIc3ISAta69wqah/37HNlCDm6a3XkA2PM+b742324RkWXKTPNRMylIAgcdfuMxSSvAlWyGiV7K/2eIPKVnNdFCk35GS+3QuVA2vLkm7UUfJVLgOrXaR8FmXDTxBzTSYmmt4KQ+s1aWVwZmb2lUFJo/oG5gxDX9pTanhXDYlSGXgpFp5XMAfhXova5j8C2c8EM85Oik3bp05h0U+MRh11M1IFOkqZE89w4EGSdPia5Dpcq7+SjqCxOyySOmyu16SpncTpc+g1/1EBnoWASGhv6inTyIIBdk8Akqv8VhN5OvP9DGX9oPLKzMH5HBCNtr47k6q+SAUUAlgjUEWsfvvGM8IDYWRZM6JkDmmS1W6IfbfcexDT2HYLrw46qtJ5zQPHBXezjCFXWBEI2gLzfp5K6eGdsmmrFrBml2CzqQG59gOH7emQNmFj4jLCg5KtK/4l2ui5pX9aF16QyQPma56RvmM6shnE/MJSx/6c0zNjHeAExD6bVpRusYsLll2/fCgGtnZETJv1S0/QtWo9t9wnHZWKaHFxOo68BpPq9OslkXY3h0896Isy2fUfRvMXeHROk44k+/Otfb20PbQpF3br+5eaGWTtnB2SYeNDnRwE5tTUrzjY6q1Z4Xt+kCWy/By5kCfcHm/nA/Jz9t1m62b1Y2f1QWs+y2c3v54oc79vAFeHNCGC+FnSGFR4PIg2uN4Uy7WDuoukv3+Pw==", 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])

 

Table "UserCfg"

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lbSUTICYkMgrgBiEyAGImOlWJ1opSCoJDIyBMsEIGmDoAqIrlgA", 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])

 

The high level explanation of expected result is - each user should have a single channel for each tvg-id group.

Details:

My final output should be 3 columns that are added to Channels table which are named as the users from UserCfg table User column (as in the sample). UserCfg table contains priority ranking for channel names in the columns (HD, FHD and Regular). orig and 50 are attribute columns that should be used in the combination with HD, FHD and Regular columns.

Each of added user column should have either "X" or null for each channel row based on the rules:

  • Find the highest rank for each user (incremental, e.g. 1 is highest) and take the column name of that rank. 
  • Then check all channel names with the same tvg-id value in Channels table and add "x" to the channel row which matches the column name. If the rank from the column "Regular" wins then the channel without any prefixes (e.g. HD, FHD, etc.) shoud have "X". Other channels from the same tvg-id group should have "null".
  • If there is "x" in the UserCfg attribute columns orig and 50, then these values should be added to the search statement for the channel with highest rank. If not found, then search without these attribute columns, e.g. only based on column names with rank.
  • If channel names with the same tvg-id do not match any of the ranking, then the logic should follow like "Regular" have won.
  • At the end there should should always be at only one channel with "x" for each tvg-id group per each user.

It is really hard to explain what i need but i've tried my best.

Please let me know if you need additional clarification. And thanks in advance!

 

 

  • 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 IDChan Nametvg-id
    211Channel 2 HD1042
    10027Channel 2 HD 501042
    290Channel 2 HD orig1042
    2420Channel 2 FHD1042
    530Channel 21042
    5071Channel 4 HD 50 orig2005
    206Channel 4 HD orig2005
    10016Channel 4 HD 502005
    2408Channel 4 FHD2005
    522Channel 42005
    5072Channel 1 HD 50 orig2044
    2407Channel 1 FHD 50 orig2044
    10015Channel 1 HD 502044
    124Channel 12044
    222Channel 1 HD2044
    343Channel 52045
    3234Channel 7 HD234
    4245Channel 7 FHD234
    7654Channel 8 UFHD2113
    3667Channel 3 4K965

     

    UserCfg

    Note: added some additional users for testing, has some changes from what you provided, I think

     

    UserHDFHDorig4K50Regular
    K21x4x3
    R2nullnullnullnull1
    P12xnullnull3
    Q12x3x4
    Znullnullnullnullnullnull
    Ynullnullxnullnullnull
    Wnullnullnullnullxnull

     

    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:

     

     

13 Replies

  • 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 IDChan Nametvg-id
    211Channel 2 HD1042
    10027Channel 2 HD 501042
    290Channel 2 HD orig1042
    2420Channel 2 FHD1042
    530Channel 21042
    5071Channel 4 HD 50 orig2005
    206Channel 4 HD orig2005
    10016Channel 4 HD 502005
    2408Channel 4 FHD2005
    522Channel 42005
    5072Channel 1 HD 50 orig2044
    2407Channel 1 FHD 50 orig2044
    10015Channel 1 HD 502044
    124Channel 12044
    222Channel 1 HD2044
    343Channel 52045
    3234Channel 7 HD234
    4245Channel 7 FHD234
    7654Channel 8 UFHD2113
    3667Channel 3 4K965

     

    UserCfg

    Note: added some additional users for testing, has some changes from what you provided, I think

     

    UserHDFHDorig4K50Regular
    K21x4x3
    R2nullnullnullnull1
    P12xnullnull3
    Q12x3x4
    Znullnullnullnullnullnull
    Ynullnullxnullnullnull
    Wnullnullnullnullxnull

     

    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

     

     

     

  • bigk's avatar
    bigk
    Helper III

    Hello MarkLaf this looks very promissing on the screenshots. However i somehow was not able to reach final goal due to error in the UserCfgPriorities query in GroupAndSort step. It cannot expand groups because it seems that some of the items are numbers and some are text. Even if i change the "Value"colum type to text after the "Unpivot"step, i'm still getting the same error:

    Expression.Error: We cannot apply operator + to types Number and Text.
    Details:
    Operator=+
    Left=3
    Right=2

    • MarkLaf's avatar
      MarkLaf
      Super User

      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:

       

       

      • bigk's avatar
        bigk
        Helper III

        Hello

        Changing the type to number did not help. But chaning to 

        Number.From( [Value] )

        did help.

        Howeve now i've run into next error. In the ChannelAssignments query ExpandGroupData step i'm getting error on Both Base and FlagCols columns. 

        It says 
        Expression.Error: The field 'K' already exists in the record.
        Details:
        Name=K

         

        When i'm clicking on the row where there is another user in UserCfg.User column, then the error is changing to another user name. For example R

        Expression.Error: The field 'R' already exists in the record.
        Details:
        Name=R
        Value=

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi bigk,

     

    Glad your issue has been resolved!
    I suggest you to accept your super suer post as the solution — it will help other community members facing similar problems to find the answer faster.

    Thanks MarkLaf  for addressing the issue.

     

    Regards,

    Vinay Pabbu