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 )

     

jennratten's avatar
jennratten
Super User
3 years ago

Hello - Is it imperative to have the tables split/duplicated or are you simply trying to obtain the appropriate QTY for your calculation?  If the latter, please consider this alternative approach which adds a column named NewQty to the existing table without creating duplicates.  

Logic:

  • Does the value of GROUP_ID for the current row appear in any row of ITEM_ID?
    • If yes, return the value of QTY for the row in which ITEM_ID equals the GROUP_ID for the current row.
    • If no, return the value of QTY for the current row.

Script:

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]),
    NewQty = Table.AddColumn ( 
        Source, "NewQty", each try Table.SelectRows(
            Source, (x)=> x[ITEM_ID]=[GROUP_ID] 
        ){0}[QTY] otherwise [QTY], type text 
    )
in
    NewQty

Result:

 

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
    Super 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 )