Sometimes Need to remove numbers from the start of Cell (Not all entries have the numbers)

[DingoFett]

I have a column that lacks consistent formatting of key information.  Some entries include extra numbers at the start that I wish to remove but not all of the entries have this.  The starting numbers will always greater than 6 digits and will always be at the left of the column.

The table provides an example

Current Column Data	Desired Transformed Data
1122334455 Product A	Product A
223343312   Product B	Product B
556677888  Product 2B	Product 2B
Product A	Product A
Product 2B	Product 2B
2ProductA	2ProductA

I tried using textrange and isvalue was numeric for the first 6 digits but failed.  Wasn’t even close.

Any suggestions on how I can have Power Query look at the first 6 digits, determine if they are numeric and then remove numeric string and return the rest of the string?

[MBreden · Accepted solution]

maybe this query fits

let
  Quelle = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjQ0MjI2NjExNVUIKMpPKU0uUXBUitWJVgILGxsbGh1acGgBXM4JLGdqamZmbm5hYYEkYwSRQjUETdIIygdKxgIA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Current Column Data" = _t]
  ),
  First6X = Table.AddColumn(Quelle, "First6", each Text.Start([Current Column Data], 6)),
  TypeNumber = Table.TransformColumnTypes(First6X, {{"First6", Int64.Type}}),
  ReplaceErrors = Table.ReplaceErrorValues(TypeNumber, {{"First6", null}}),
  TextPosition = Table.AddColumn(
    ReplaceErrors,
    "PositionText",
    each if [First6] <> null then Text.PositionOfAny([Current Column Data], {"A" .. "Z"}) else null
  ),
  TextLength = Table.AddColumn(
    TextPosition,
    "Length",
    each Text.Length([Current Column Data]) - [PositionText],
    Int64.Type
  ),
  FinalDataX = Table.AddColumn(
    TextLength,
    "FinalData",
    each
      if [Length] <> null then
        Text.End([Current Column Data], [Length])
      else
        [Current Column Data],
    type text
  ),
  RemoveOtherColumns = Table.SelectColumns(FinalDataX, {"FinalData"})
in
  RemoveOtherColumns

/Melanie 

[AlexisOlson · Accepted solution]

Try adding a custom column like this:

if Text.Start([Current Column Data], 6)
   = Text.Select(Text.Start([Current Column Data], 6), {"0".."9"})
then Text.Trim(Text.AfterDelimiter([Current Column Data], " "))
else Text.Trim([Current Column Data])

This looks at the first six characters and checks if they are the same as the digits contained in the first six characters. If so, then take the text after the first space and trim excess leading or trailing spaces. If not, then use the original value (with any excess spaces trimmed).

[MBreden · Accepted solution]

maybe this query fits

let
  Quelle = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjQ0MjI2NjExNVUIKMpPKU0uUXBUitWJVgILGxsbGh1acGgBXM4JLGdqamZmbm5hYYEkYwSRQjUETdIIygdKxgIA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Current Column Data" = _t]
  ),
  First6X = Table.AddColumn(Quelle, "First6", each Text.Start([Current Column Data], 6)),
  TypeNumber = Table.TransformColumnTypes(First6X, {{"First6", Int64.Type}}),
  ReplaceErrors = Table.ReplaceErrorValues(TypeNumber, {{"First6", null}}),
  TextPosition = Table.AddColumn(
    ReplaceErrors,
    "PositionText",
    each if [First6] <> null then Text.PositionOfAny([Current Column Data], {"A" .. "Z"}) else null
  ),
  TextLength = Table.AddColumn(
    TextPosition,
    "Length",
    each Text.Length([Current Column Data]) - [PositionText],
    Int64.Type
  ),
  FinalDataX = Table.AddColumn(
    TextLength,
    "FinalData",
    each
      if [Length] <> null then
        Text.End([Current Column Data], [Length])
      else
        [Current Column Data],
    type text
  ),
  RemoveOtherColumns = Table.SelectColumns(FinalDataX, {"FinalData"})
in
  RemoveOtherColumns

/Melanie