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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

srehman

Board Regular
Joined
Jan 4, 2020
Messages
137
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,738
first: use CODE tags for your code
second: post your source/sources data (change to generic if needed)
 

srehman

Board Regular
Joined
Jan 4, 2020
Messages
137
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,738
use code tags means [CODE]Your code[/CODE]
eg,
Code:
your code
don't copy code from the post above but from Advanced Editor then use code tags
 
Last edited:

srehman

Board Regular
Joined
Jan 4, 2020
Messages
137
Office Version
2016
Platform
Windows
Ok i got it found 1 problem in Rich Solution

it should be DAM

DAM001DA
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,738
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

New Member
Joined
Jul 7, 2020
Messages
2
Office Version
365, 2019, 2013
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,738
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
extract.png

see post #2 then post #5 :cool:
 
Last edited:

srehman

Board Regular
Joined
Jan 4, 2020
Messages
137
Office Version
2016
Platform
Windows
Aditya_Shanmugham

Still problem in line 7

Split text & number power query.xlsx
EF
1Result
2RawdataCustom
3AD102201AAD
4AIRLAIRL
5AP0332010BAP
6ARILS78ARILS
7DD3403A1DD3403A1
8DD0101ADD
9DAM001DAM
Sheet3
 

Watch MrExcel Video

Forum statistics

Threads
1,101,870
Messages
5,483,434
Members
407,395
Latest member
Sakshine

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top