Forum Discussion
Split or Trim Rows Based on Certain Criteria
- The Column 1logic = All characters that come before G*.BEAM or R.BEAM or DY or A*.BEAM or Z*.BEAM and ** LED
- The Column 2 logic = All characters between the first column and *** LED
- The Column 7 logic = All characters after LEC with the exception the sign DIA or mA or mm
its a bit complicated, most of the Column 7 logic is after LEC, the other criteria is only to cater for old data, all new data will have the correct naming system
Regard
I realised that splitting the columns the way I needed was too hard as there were too many criterias, so I spent some time and manually fixed all the data so they are standardized
Could you please help me added just one criteria to the one that you provided before?
let
split = Text.SplitAny( [Product Description], " /""." ),
xxx = [
Column 1 =
List.Select(
split,
(i) =>
List.Contains( { "A".."Z" }, Text.Start( i, 1 ) )
and List.Contains( { "0".."9" }, Text.End( i, 1 ) )
){0},
Column 2 =
try List.Select(
split,
(i) => Text.EndsWith( i, ".BEAM" )
){0} otherwise "",
Column 3 =
try List.Select(
split,
(i) => Text.EndsWith( i, "LED")
){0} otherwise "",
Column 4 =
try List.Select(
split,
(i) => Text.EndsWith( i, "W" )
){0} otherwise "",
Column 5 =
try List.Select(
split,
(i) => Text.EndsWith( i, "mA" )
){0} otherwise "",
Column 6 =
List.Select(
split,
(i) => Text.EndsWith( Text.Upper( i ), "K" )
){0}
]
in xxxColumn 3 is the only one that isn't working because there is a space before the word LED, if i add a space before the word it still doesnt work. How do I make it work? The data is always 2 or 3 numbers followed by space and then the word LED eg: 24 LED or 48 LED
Could you please help me add one more column? Criteria is show any text or numbers after the word LEC
Thanks