Spliting one table into two table to use calculation formula for each table.

Hello again!  Is this what you are looking for?

The only difference from the prior solution is that I have added a new column for the ForecastedSales with the following logic:

• Is the first character of GROUP_ID a number?
  • If yes, 1000 * [NewQty] * [QTY]
  • If no, 1000 * [QTY]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZFBTsQwDEWvUmU9Id927CTdcQAkJJbV7ECaDeL+O0wmU4qGEYO6qFpFfi//u8sSQC2KRkAjATUcAh6QyUzVvx9P72+v/s6JEvvoRGXm5gdj1sLxsISXZ2bSM4p8AxP+xrRjPuv3sj81ctbWBbdulXyF70o+HCIRuUYY3FWGBJLLHTEu5YWlbMo/fZz+rP5PyMa+KII8MBBZzTpO27ItsX0J6oQZ8IP1zwyDUMzaBUJttK0bASXin12vDNbxrmBcKtBdIXQNYWsI7oay3XdN2Xmmqc2sv/A71qC71+CG4yc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ITEM_ID = _t, QTY = _t, U_ID = _t, ACTION_DATE = _t, GROUP_ID = _t, CO_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"QTY", type number}}),
    NewQty = Table.AddColumn ( 
        #"Changed Type", "NewQty", each try Table.SelectRows(
            #"Changed Type", (x)=> x[ITEM_ID]=[GROUP_ID] 
        ){0}[QTY] otherwise [QTY], type number 
    ),
    ForecastedSales = Table.AddColumn ( 
        NewQty, "ForecastedSales", each
        if  
            // Is the first character of the GROUP_ID a number?
            try Value.Is ( Number.From ( Text.Start ( [GROUP_ID], 1 ) ), type number ) otherwise false
        // Result if true
        then 1000 * [NewQty] * [QTY]
        // Result if false
        else 1000 * [QTY],
        type number 
    )
in
    ForecastedSales

FYI, to simply replicate IsNumber (Excel) using PowerQuery, this is how you would do it if you were adding the result as a new column:

Table.AddColumn ( #"Previous Step Name", "New Column Name", each try Value.Is ( Number.From ( [ColumnToTest] ), type number ) otherwise false, type logical )