Forum Discussion

BJSD's avatar
BJSD
New Member
2 years ago

Help! Trying to put Excel ISNUMBER formual into Power Query M.

Hello,  

 

I'm trying to put the following Excel Formula into Power Query M formula and I'm stumped.  Here is the Excel formula:

 

=IFS(ISNUMBER(VALUE(LEFT(A6,1))),A6,ISNUMBER(VALUE(LEFT(B6,1))),B6,ISNUMBER(VALUE(LEFT(C6,1))),C6,ISNUMBER(VALUE(LEFT(D6,1))),D6,ISNUMBER(VALUE(LEFT(E6,1))),E6,ISNUMBER(VALUE(LEFT(F6,1))),F6,ISNUMBER(VALUE(LEFT(G6,1))),G6,ISNUMBER(VALUE(LEFT(H6,1))),H6) 

 

This formula is checking ot see if the first character in the text of each of the cells is a number (0 to 9) and if so populate the new column with the contents of that cell if the first characer of the text is a number.  If the cell begins with the number it is the "Account Number" which is what I need.  If not, move to check the next column, There are 9 columns and each must be checked as the account number could be in the 9th column.  See sample data below.

 

 

Thank you, BJ

Need Help

2 Replies

  • BJSD's avatar
    BJSD
    New Member
    2 years ago

    Here is a better sample of the data.

    Acct Level 0Acct Level 1Acct Level 2Acct Level 3Acct Level 4Acct Level 5Acct Level 6Acct Level 7Acct Level 8Acct Level 9Count Account  Only
    T0000 - All Accounts (incl Stats)BS000 - Total Balance SheetA0000 - Total AssetsA1000 - Total Current Assets10200 - Marketable Securities     1 10200 - Marketable Securities
    • ThxAlot's avatar
      ThxAlot
      Super User
      2 years ago
      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcwxCsMwDAXQqwhPLaTg9AZO5k7OFjKoQlAT4YAl3z9OMzT9aPpP/Hl2k2+BBwQRCERbzaZwS5kEoqHp3XVuiOfPtBkKDCiYiSF+mK1p8FcNqmx61P21HmspnO3HvX9++YVlZcO3tEGmWpIlPvzvlmUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Acct Level 0" = _t, #"Acct Level 1" = _t, #"Acct Level 2" = _t, #"Acct Level 3" = _t, #"Acct Level 4" = _t, #"Acct Level 5" = _t, #"Acct Level 6" = _t, #"Acct Level 7" = _t, #"Acct Level 8" = _t, #"Acct Level 9" = _t]),

    #"Extracted Account" = Table.AddColumn(Source, "Extracted Account", each let l=Record.ToList(_) in l{List.PositionOf(l, {"0".."9"}, 0, (x,y) => List.Contains(y, Text.Start(x,1)))})
in
    #"Extracted Account"