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 ...
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"
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"