Split a Column Dynamically based on another columns length

[jamuka]

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

[Ahmedx · Accepted solution]

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"

[jamuka]

thank you very much for your help @Ahmedx 

have a nice day