Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Replace Values in Column dynamically

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

IDValue
1Math
2Chemistry
3Physics

 

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,

Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago

    Anonymous , 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

     

  • p45cal's avatar
    p45cal
    1 year ago

    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"

     

     

5 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    1 year ago

    Anonymous , 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

     

  • p45cal's avatar
    p45cal
    Solution Supplier
    1 year ago

    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"

     

     

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    1 year ago

    Hi Anonymous , 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.

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    1 year ago

    Hi Anonymous , 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's avatar
    v-hashadapu
    Community Support
    1 year ago

    Hi Anonymous , 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.