Split Alphabet only from Left Just before Numbers only in Power query

[srehman]

Hi Team,

I have used following approach but not working

Text.Select([Rawdata],{"A".."Z"}) Data(AD1022010A) Result(ADA) wrong I need (AD)
Text.Select([Rawdata],{"0".."9"})

Split text & number power query.xlsx
	D	E
1		Required Result
2	Rawdata	Starting Alphabet from left before numbers
3	AD102201A	AD
4	AIRL	AIRL
5	AP0332010B	AP
6	ARILS78	ARILS
Sheet1

 

[sandy666]

Glad to help
Thanks for the feedback

 

[srehman]

Hi Sandy / Rich ( Individually on separate sheet i am success full to run below (RICH) steps, while i have to add in my current sheet.

I tried many errors.

Any thoughts


Below (RICH) steps I have to add in my current code.
==================================================================================
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Number = Table.AddColumn(Source, "Number", each Text.Select([Rawdata],{"0".."9"})),
LenRaw = Table.AddColumn(Number, "Length", each Text.Length([Rawdata]), Int64.Type),
LenNum = Table.AddColumn(LenRaw, "Length.1", each Text.Length([Number]), Int64.Type),
Subtract = Table.AddColumn(LenNum, "Subtraction", each [Length] - [Length.1], type number),
IF = Table.AddColumn(Subtract, "IF", each if [Length.1] = 0 then [Length] else [Subtraction]-1),
Range = Table.AddColumn(IF, "Text Range", each Text.Middle([Rawdata], 0, [IF]), type text),
TSC = Table.SelectColumns(Range,{"Rawdata", "Text Range"})
in
TSC
========================================================================================
I have to add Rich steps as last from last steps from my current code
Current Sheet Code

let
Source = Folder.Files("C:\Users\Rehmans\Desktop\Karcher Slotting\SOH"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Csv.Document([Content])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.PromoteHeaders([Custom])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Tag ID", "SKU", "SKU Description", "Location", "Location Zone", "Sub Zone", "Qty On Hand", "Move Date", "Condition Code"}, {"Tag ID", "SKU", "SKU Description", "Location", "Location Zone", "Sub Zone", "Qty On Hand", "Move Date", "Condition Code"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Content", "Name", "Custom"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom_Zone", each if [Sub Zone] = "EURO" and [Location Zone]="50PIC01" then "LLOP" else
if Text.Start([Sub Zone],4) = "MEZZ" and [Location Zone]="50PIC01" then "MEZZ" else
if Text.Start([Sub Zone],4) = "BULK" and [Location Zone]="40BLK01" then "BULK" else
if Text.Start([Sub Zone],4) = "MACH" and [Location Zone]="40BLK01" then "BULK" else
if Text.Start([Sub Zone],4) = "ACID" and [Location Zone]="40BLK01" then "BULK" else
if Text.Start([Sub Zone],3) = "AIR" and [Location Zone]="50PIC01" then "AIR" else
if Text.Start([Sub Zone],3) = "LSS" and [Location Zone]="50PIC01" then "LLOP" else
if Text.Start([Sub Zone],3) = "LSS" and Text.End([Sub Zone],1)="S" and [Location Zone]="40BLK01" then "HLOP" else

if Text.Start([Sub Zone],4) = "EURO" and [Location Zone]="40BLK01" then "HLOP" else ""),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Location Zone] = "40BLK01" or [Location Zone] = "50PIC01")),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Location", "Location - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Location - Copy", "Rawdata"}})
in
#"Renamed Columns"

 

[sandy666]

first: use CODE tags for your code
second: post your source/sources data (change to generic if needed)

 

[srehman]

I tried with code tag in coding , and Individual steps one by one by adding custom column 1st steps process correctly
1. Text.Select([Rawdata],{"0".."9"})),
In 2nd second steps by adding custom column
2. Text.Length([Rawdata]), Int64.Type) spelling error while everything looks correct

 

[sandy666]

use code tags means [CODE]Your code[/CODE]
eg,

your code

don't copy code from the post above but from Advanced Editor then use code tags

 

[srehman]

Ok i got it found 1 problem in Rich Solution

it should be DAM

DAM001

DA

 

[sandy666]

Glad you solve it
but
that is why I always asking about representative example source data and expected result because in this case how do I know it was reason of error?
it is a good habit to post source data and expected result in copyable form but not a picture

 

[Aditya_Shanmugham]

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TS = Table.AddColumn(Source, "Number", each Text.Select([Rawdata],{"0".."9"})),
TBD = Table.AddColumn(TS, "TBD", each try Text.BeforeDelimiter([Rawdata], [Number]) otherwise [Rawdata], type text),
RC = Table.RemoveColumns(TBD,{"Number"})
in
RC

 

[sandy666]

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TS = Table.AddColumn(Source, "Number", each Text.Select([Rawdata],{"0".."9"})),
TBD = Table.AddColumn(TS, "TBD", each try Text.BeforeDelimiter([Rawdata], [Number]) otherwise [Rawdata], type text),
RC = Table.RemoveColumns(TBD,{"Number"})
in
RC

you need to check "your" solution

see post #2 then post #5

 

[srehman]

Aditya_Shanmugham

Still problem in line 7

Split text & number power query.xlsx
	E	F
1	Result
2	Rawdata	Custom
3	AD102201A	AD
4	AIRL	AIRL
5	AP0332010B	AP
6	ARILS78	ARILS
7	DD3403A1	DD3403A1
8	DD0101A	DD
9	DAM001	DAM
Sheet3