Forum Discussion

AliNafa's avatar
AliNafa
Frequent Visitor
3 years ago
Solved

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

Hello,   I have a table on power bi and would like to split it into two tables to achieve some calculation formula. This is the look for the Main table ( Just small view of it ) : Table A ...
Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    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 )

     

AliNafa's avatar
AliNafa
Frequent Visitor
3 years ago

Hi jennratten 

Thank you so much for your reply ! it's really amazing what you have done.

But there're some issue, I will need to apply two formula on the same table to calculate forcasting sales. I have explained it on your picture below :

 

And one more thing, I tried your code but it's only applied on the specified items you have worked on them. But i will need to apply this code on a file that contians more than 13000 rows and it's updated daily since the file is connected with oracle DB and it's updated every day.

This ppix file contais some of the items :
https://drive.google.com/file/d/1J69hFEvXgcQqZ4qm7ZQnG2AmU7QaQYHF/view?usp=sharing


Thank you !

jennratten's avatar
jennratten
Icon for Super User rankSuper User
3 years ago

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 )