Split or Trim Rows Based on Certain Criteria

[RahulPBI]

Hi Experts

I have a few thousand rows of data in one column and now have a need to this data into seperate columns

Tried to split columns and even tried Format without much luck

Does anyone have a suggestion on how can i split my column based on certain criteria?

Format of coulmn example

LP53 C60.B 4W 70A 3K

SP53 R65.B 36L 24W 70A 4K

FT54 R.B 24L 48W/35A 3K

OC20 ABC.B 12 L 18W/70A 5K

Result I would like power query to split the above emaple and show in as below

Column 1	Column2	Column3	Column4	Column5	Column6
LP53	C60.B		4W	70A	3k
SP53	R65.B	36L	24W	70A	4K
FT54	R.B	12L	48W	35A	3K
OC20	ABC.B	12 L	18W	70A	5K

My problem is there are diffrent many instances and some could have some data not there, like row one

How do I get the rows broken up and the about columns and if the criteria is n

[Mariusz]

Hi @RahulPBI 

Try this,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PctNCoAgEAbQq3y4jvJnxtqm0CahsMCFeP9rNFK0frxaVTrZIXo9BlDBrFe4XbWhqqtD9izgfIL9lF7dbiZkMUsJtJTJ8T+PaDXWEEWNRYIR7pWF2wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn( Source, "Custom", each 
        let 
            split = Text.SplitAny( [Text], " /"  ),
            xxx = [
                column1 = 
                    List.Select( 
                        split, 
                        (i) =>     
                            List.Contains( { "A".."Z" }, Text.Start( i, 1 ) ) 
                            and List.Contains( { "0".."9" }, Text.End( i, 1 ) ) 
                    ){0},
                column2 =
                    List.Select( 
                        split, 
                        (i) => Text.EndsWith( i, ".B" ) 
                    ){0},
                column3 =
                    try List.Select( 
                        split, 
                        (i) => Text.EndsWith( i, "L" ) 
                    ){0} otherwise null,
                column4 =
                    List.Select( 
                        split, 
                        (i) => Text.EndsWith( i, "W" ) 
                    ){0},
                column5 =
                    List.Select( 
                        split, 
                        (i) => Text.EndsWith( i, "A" ) 
                    ){0},
                column6 =
                    List.Select( 
                        split, 
                        (i) => Text.EndsWith( Text.Upper( i ), "K" ) 
                    ){0}
            ] 
            
        in xxx

      
    ),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"column1", "column2", "column3", "column4", "column5", "column6"}, {"column1", "column2", "column3", "column4", "column5", "column6"})
in
    #"Expanded Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[RahulPBI]

Hi Mariusz

Works very well and managed to incorporate it into my live pbix

It done the splits how I had wanted, but I didn't realise and take into account some other kind of splits

I have some variations and is it possible to split the below data? I have attached a test pbix file, hopefully that helps

 

Should be split like below

Column1	Column2	Column3	Column4	Column5	Column6	Column7
FT54	G50.BEAM	36 LED	36W	350mA	3K	D
FT54	R.BEAM	36 LED	72W	700mA	4K	MOTION
FT53	G50.BEAM	24 LED	24W	350mA	3K	7P EM [DI/SC]
MT32	DY B65/6F+B65/6R	12 LED	12W	350mA	3K	MOTION D M
MT32Z	G70.BEAM	2X6 LED	36W	700mA	3000K	2C DAL
RT32	A60 BEAM	24 LED	72W	700/1400mA	RGB/3000K	DM
FL54	G70.BEAM	24 LED	48W	700mA	4K	ADV
VL50	G60.BEAM	12 LED	24W	700mA	4K	NE 7D [DA/PE]
VL50	G70.BEAM	12 LED	24W	700mA	4K	NM 7G [DL/PE] I.S
FL50-S DY	G65/18F+Z70/6F	24 LED	24W	350mA	4K	NA 7D [DL/PE] I.S
RS30	Z60	12 LED	24W	700mA	RGBW 3K	DA
TY15-AP			17W	500mA	3K	GRND DA
LS410	E./M.BEAM	2x 3 LED	12W	700mA	3000K	REMOTE
OC220	B.BEAM	DRKLIGHT LED	24W	700mA	3000K	DALI
AS220	B.BEAM	12 LED	36W	1050mA	3000K	CATRY DA SPAL Mk4
AS220	B.BEAM	12 LED	24W	700mA	3000K	CATRY DM Mk3/4
OR120	B.BEAM	6 LED	12W	700mA	3000K	REMOTE
LC230	M.BEAM	12 LED	12W	350mA	3000K	FOOD 48V
L230 [FP] FRM			52W	1400mA	4000K	DALI
FC26-CC	M.BEAM	36 LED	144W	350mA	RGBW	DMX
LC23-CC [ZP] ZOM SPT		12 LED	48W	300mA	RGBW	DMX
LC23-TX [FP] FRM		12 LED	44W	1300mA	2700-6000K	DALI

Its a very messy data, but working on fixing the new extries

Is it possible to acheive? Hopefully it is

Regards

[Mariusz]

Hi @RahulPBI 

What is the logic or rule for columns 1,2 and 7?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[RahulPBI]

@Mariusz 

1. The Column 1logic = All characters that come before G*.BEAM or R.BEAM or DY or A*.BEAM or Z*.BEAM and ** LED
2. The Column 2 logic = All characters between the first column and *** LED
3. 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

[RahulPBI]

@Mariusz 

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 xxx

Column 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 

[RahulPBI]

Sorry below is the link for the pbix file

https://1drv.ms/u/s!ApULo7UfaUptgS9f8Kn4Nqfp0EH7?e=6ety9O

Regards