Replace Values in Column dynamically

[RavenWintersB]

I have a column whose values are as below:

Student Table

Categories

1,2

2

3,4,5,6,7,8,9,10

1, .. 100

All of the categories id/name values are stored in another table:

Cat Table

ID	Value
1	Math
2	Chemistry
3	Physics

How do I turn the categories column in Student into this:

Categories

Math, Chemistry

Chemistry

Physics, History, English ... <Category N>

Note: I don't know how many values I will have in the Categories column (100 values in the Categories column is an overstatement but it is there to make a point) and I don't know how many rows I'll have in the Cat table.
My approach is to split the Categories columns (into C.1, C.2, C.3 ... C.100 if need be ), build a Dictionary from the Cat table, replace values in all of the C columns then join them into a new column.
I'm currently stuck at the Dictionary creation step.
Is there an efficient way to do this?

Thanks ahead,

[AlienSx · Accepted solution]

@RavenWintersB , just for fun

let
    stu_format = (txt) => "#{" & Text.Replace(txt, ",", "},#{") & "}",
    cats = List.Buffer({""} & cat_table[Value]),
    format = Table.TransformColumns(student_table, {"Categories", (x) => Text.Format(stu_format(x), cats)})
in
    format

and the one with dictionary

let
    cat_dict = Record.FromList(cat_table[Value], List.Transform(cat_table[ID], Text.From)), 
    result = Table.TransformColumns(
        student_table,
        {
            "Categories", 
            (x) => ((cats_list) => Text.Combine(
                List.Transform(
                    cats_list, 
                    (w) => Record.FieldOrDefault(cat_dict, w)
                ), 
                ", ")
            )(Text.Split(x, ","))
        }
    )
in
    result

[v-hashadapu]

Hi @RavenWintersB , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @RavenWintersB , Hope your issue is solved. If it is, please consider marking it 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details. Thank you.

[v-hashadapu]

Hi @RavenWintersB , thank you for reaching out to the Microsoft Fabric Community Forum.

Just wanted to confirm if the issue’s been resolved. While the answer provided by @p45cal is detailed and thorough, it involves multiple transformations, which might be less efficient for larger datasets. The answer posted by @AlienSx is simple, direct and more efficient. Please consider trying it. If it didn’t work, please share the details.

If it does help, then please consider marking it 'Accept as Solution' so others with similar queries may find it more easily.

Thank you.

[p45cal · Accepted solution]

the long way round:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYq5DQAgDMR2Se2CI7yzRNl/DRA0lmU5wkS1JOzTaXQGk8VGBel1VdS+IUf3zgM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Categories = _t]),
    CatTable2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY89D8IgEIb/C3MH8dtVY3Vp0sRN0gFPIiSUS4Cl/76HZ0Mcn4e7916UElI0otPZJjE0SqyJLtaMLuU4fc2GTG+n5IAntsRnhx4//L4jvkLlPfGd1qs5lAS6UM2RM+Dv0InkE+uQXJFoowlgmeWS/FuQpWyLmF/aezal7M3EUQfmUrYzbwcuGDal7gNh+cAwAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    ChangedType1 = Table.TransformColumnTypes(CatTable2,{{"ID", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Added Index", {{"Categories", type text}}, "en-GB"), {{"Categories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Categories"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Categories", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Categories", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Categories"}, ChangedType1, {"ID"}, "CatTable", JoinKind.LeftOuter),
    #"Expanded CatTable" = Table.ExpandTableColumn(#"Merged Queries", "CatTable", {"Value"}, {"Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded CatTable",{"Categories"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index"}, {{"Categories", each Text.Combine(_[Value],", ")}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

[AlienSx · Accepted solution]

@RavenWintersB , just for fun

let
    stu_format = (txt) => "#{" & Text.Replace(txt, ",", "},#{") & "}",
    cats = List.Buffer({""} & cat_table[Value]),
    format = Table.TransformColumns(student_table, {"Categories", (x) => Text.Format(stu_format(x), cats)})
in
    format

and the one with dictionary

let
    cat_dict = Record.FromList(cat_table[Value], List.Transform(cat_table[ID], Text.From)), 
    result = Table.TransformColumns(
        student_table,
        {
            "Categories", 
            (x) => ((cats_list) => Text.Combine(
                List.Transform(
                    cats_list, 
                    (w) => Record.FieldOrDefault(cat_dict, w)
                ), 
                ", ")
            )(Text.Split(x, ","))
        }
    )
in
    result