Forum Discussion
Solved
Split a Column Dynamically based on another columns length
Hello all,
Our product names should start with Brand Names then product details. Our system allows 30 characters for product name.
Therefore sometimes we have to tweak names.
I'd like to check names whether they include Brand Names or not.
I want to split names into two columns based on brand lengths, so I can check whether products have correct brand names or not.
I tried to use solution in another topic but couldn't update formula for my need.
Solved: Split a column by positions in another column - Microsoft Fabric Community
kind regards
| Brand | Length | Name | Name.1 | Name.2 | Check (Brand with Name.1 |
| XYZ | 4 | XYZ SUNGLASSES PR45630 | XYZ | SUNGLASSES PR45630 | TRUE |
| ABC TOYS | 9 | ABCTOYS ORGANIC WOOD BLOCK 400 | ABCTOYS O | RGANIC WOOD BLOCK 400 | FALSE |
| ABC TOYS | 9 | ABC TOYS WOOD BLOCKS 50 PIECES | ABC TOYS | WOOD BLOCKS 50 PIECES | TRUE |
pls try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYs7CoAwEAWv8tjaImAULGMMEhRXXMVPyP2v4WplYzHwBualRMd5UUFW0QXZpn50IkEwL7aqS0O5SORaj5VP0apRVB8DL72bosfO3KEd2Q+w5u/x6icVVAZzDD4I5XwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, Lenght = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Lenght", Int64.Type}, {"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Name.1", each [ t = if Text.Range([Name], [Lenght]-1, 1)=" " then Splitter.SplitTextByLengths({[Lenght], 1000})([Name]) {0} else Splitter.SplitTextByLengths({[Lenght]-1, 1000})([Name]) {0}, to = Text.Trim(t)][to]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Name.2", each [ t = if Text.Range([Name], [Lenght]-1, 1)=" " then Text.Range([Name], [Lenght]) else Text.Range([Name], [Lenght]-1), to = Text.Trim(t)][to]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Check (Brand with Name.1", each [Brand] =[Name.1]) in #"Added Custom2"
2 Replies
pls try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYs7CoAwEAWv8tjaImAULGMMEhRXXMVPyP2v4WplYzHwBualRMd5UUFW0QXZpn50IkEwL7aqS0O5SORaj5VP0apRVB8DL72bosfO3KEd2Q+w5u/x6icVVAZzDD4I5XwD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, Lenght = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Lenght", Int64.Type}, {"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Name.1", each [ t = if Text.Range([Name], [Lenght]-1, 1)=" " then Splitter.SplitTextByLengths({[Lenght], 1000})([Name]) {0} else Splitter.SplitTextByLengths({[Lenght]-1, 1000})([Name]) {0}, to = Text.Trim(t)][to]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Name.2", each [ t = if Text.Range([Name], [Lenght]-1, 1)=" " then Text.Range([Name], [Lenght]) else Text.Range([Name], [Lenght]-1), to = Text.Trim(t)][to]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Check (Brand with Name.1", each [Brand] =[Name.1]) in #"Added Custom2"
