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

ITEM_ID                                           

QTY                 

U_ID

ACTION_DATE                      

GROUP_ID

CO_ID

019-35-005-1008

0.0416655

Ahmed

4/1/2019 5:29:42 PM

1008

6

SP2215

0.004

Ahmed

4/1/2019 5:32:16 PM

1008

5

004-002-008-2459

0.5

Ahmed

4/1/2019 5:34:48 PM

1008

5

019-35-005-1008

0.0416655

Ahmed

4/1/2019 5:29:42 PM

1008

5

033-048-060-3578

0.00347

Ahmed

4/1/2019 5:34:00 PM

1008

6

SP3237

0.004

Moh

4/1/2019 5:32:41 PM

1008

5

SP3237

0.004

Moh

4/1/2019 5:32:41 PM

1008

6

001-013-000-2566

0.15

Ahmed

9/26/2018 12:00:00 AM

SP2215

6

031-45-000-3198

0.08

Ahmed

1/12/2019 5:34:01 PM

SP2215

6

036-000-000-3207

0.001

Ahmed

9/26/2018 12:00:00 AM

SP2215

5

031-65-000-3192

0.77

Ahmed

8/4/2021 9:25:06 AM

SP2215

5

001-013-000-2566

0.15

Ahmed

9/26/2018 12:00:00 AM

SP2215

5

031-45-000-3198

0.08

Ahmed

1/12/2019 5:34:01 PM

SP2215

5


( GROUP ID ) contains different values ( Some of them start with Number and some of them start with letters like SP for example ).


When we filter a number on ( Group_ID ) like let's say for example 1008

We see those data :

 

TABLE B   ( Expected Result )

 

ITEM_ID                             

QTY                    

U_ID              

ACTION_DATE                  

GROUP_ID

CO_ID

019-35-005-1008

0.0416655

Ahmed

4/1/2019 5:29:42 PM

1008

6

SP2215

0.004

Ahmed

4/1/2019 5:32:16 PM

1008

5

004-002-008-2459

0.5

Ahmed

4/1/2019 5:34:48 PM

1008

5

019-35-005-1008

0.0416655

Ahmed

4/1/2019 5:29:42 PM

1008

5

033-048-060-3578

0.00347

Ahmed

4/1/2019 5:34:00 PM

1008

6

SP3237

0.004

Moh

4/1/2019 5:32:41 PM

1008

5

SP3237

0.004

Moh

4/1/2019 5:32:41 PM

1008

6


But Also , The ITEM_ID Like SP2215 can be a Group_ID too and it contains different items on it :

 

Table C ( Expected Result )

ITEM_ID                              

QTY             

U_ID         

ACTION_DATE                  

GROUP_ID

CO_ID

001-013-000-2566

0.15

Ahmed

9/26/2018 12:00:00 AM

SP2215

6

031-45-000-3198

0.08

Ahmed

1/12/2019 5:34:01 PM

SP2215

6

036-000-000-3207

0.001

Ahmed

9/26/2018 12:00:00 AM

SP2215

5

031-65-000-3192

0.77

Ahmed

8/4/2021 9:25:06 AM

SP2215

5

001-013-000-2566

0.15

Ahmed

9/26/2018 12:00:00 AM

SP2215

5

031-45-000-3198

0.08

Ahmed

1/12/2019 5:34:01 PM

SP2215

5



Note that the ( GROUP_ID ) for example ( 1008 ) have  more than one ( SP ) on ( ITEM_ID ) .. Like on Group_ID 1008 , it has two ( Values starts with ( SP ) which are ( SP2215 ) and ( SP3237 ) and both can be a ( Group_ID ) and contains several ( ITEM_IDs ) .  

 

( Group_ID ) can starts with any letter like BAK or MIXX . Not Just ( SP ).

 

The reason i would like to split it into two tables ( Table B and C )  is because I would like to apply a calculation formula for the values starting with a number in ( Group_ID ).

And apply another calculation formula for the items starting with letters like ( SP ).

The calculation is to see the forecasting value for each item.

Let’s say i’m going to buy 1000 pc from this item ( From Table B ) :

ITEM_ID                           

QTY                    

U_ID             

ACTION_DATE               

GROUP_ID

CO_ID

019-35-005-1008

0.0416655

Ahmed

4/1/2019 5:29:42 PM

1008

6

 

Since the Group id Starts with number ,  the calculation formula will be like below :
1000 * QTY ( 0.0416655 )

But When the group id starts with letter like SP  ( From Table C ) :

 

ITEM_ID                           

QTY           

U_ID

ACTION_DATE                    

GROUP_ID

CO_ID

001-013-000-2566

0.15

Ahmed

9/26/2018 12:00:00 AM

SP2215

6

 

the formula will be like below :

 

1000  * SP ( 0.004 ) ( Explained below ) *  QTY ( 0.15 )

 

( for the SP value , it will call it from the Table B and the ITEM_ID should be related with the Group id in order to get the exact value )

ITEM_ID               

QTY             

U_ID

ACTION_DATE             

GROUP_ID

CO_ID

SP2215

0.004

Ahmed

4/1/2019 5:32:16 PM

1008

5



Here’s a google sheet link for the Original table
https://docs.google.com/spreadsheets/d/1HHNIceZxn7kX2lr_YyOxmIttgxQj57O7-wymK27HHSg/edit?usp=sharing

Here’s the PPIX file
https://drive.google.com/file/d/1J69hFEvXgcQqZ4qm7ZQnG2AmU7QaQYHF/view?usp=sharing

Thank you

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 )

     

6 Replies

  • djurecic's avatar
    djurecic
    Super User
    3 years ago

    Hi AliNafa ,

     Do you just need to duplicate the table? Power Query Editor allows you to duplicate like below:

     

     

     

    Please accept as solution if this has answered the question- thanks!

    • AliNafa's avatar
      AliNafa
      Frequent Visitor
      3 years ago

      Hi djurecic

      I tried to duplicate it but it didn’t work for me.

      But ok let’s assume that i duplicated table A , How can i filter Table A to show only ( GROUP_ID ) values starting with Numbers.

      And Filter the duplicate table ( let’s name it Table B ) to Show only the GROUP_ID values starts with letters ?

      I have more than 1000 values on GROUP ID and need a command to filter it the way i explained you above.

      If it works, Then let’s  say this is Table A  after filtering GROUP_ID to value ( 1008 ) and this value have more than one ( SP ) value as an ( ITEM_ID ) ( SP2215 ) & ( SP3237 )

      TABLE A

      ITEM_ID                      

      QTY

      U_ID        

      ACTION_DATE             

      GROUP_ID   

      CO_ID

      019-35-005-1008

      0.0416655

      Ahmed

      4/1/2019 5:29:42 PM

      1008

      6

      SP2215

      0.004

      Ahmed

      4/1/2019 5:32:16 PM

      1008

      5

      004-002-008-2459

      0.5

      Ahmed

      4/1/2019 5:34:48 PM

      1008

      5

      019-35-005-1008

      0.0416655

      Ahmed

      4/1/2019 5:29:42 PM

      1008

      5

      033-048-060-3578

      0.00347

      Ahmed

      4/1/2019 5:34:00 PM

      1008

      6

      SP3237

      0.004

      Moh

      4/1/2019 5:32:41 PM

      1008

      5

      SP3237

      0.004

      Moh

      4/1/2019 5:32:41 PM

      1008

      6



      Table B ( Duplicated Table ) after filtering GROUP_ID to ( SP2215 ) since it can be a group id too and contains items under it.

      ITEM_ID                           

      QTY     

      U_ID

      ACTION_DATE                      

      GROUP_ID   

      CO_ID

      001-013-000-2566

      0.15

      Ahmed

      9/26/2018 12:00:00 AM

      SP2215

      6

      031-45-000-3198

      0.08

      Ahmed

      1/12/2019 5:34:01 PM

      SP2215

      6

      036-000-000-3207

      0.001

      Ahmed

      9/26/2018 12:00:00 AM

      SP2215

      5

      031-65-000-3192

      0.77

      Ahmed

      8/4/2021 9:25:06 AM

      SP2215

      5

      001-013-000-2566

      0.15

      Ahmed

      9/26/2018 12:00:00 AM

      SP2215

      5

      031-45-000-3198

      0.08

      Ahmed

      1/12/2019 5:34:01 PM

      SP2215

      5



      I would like to apply this formula on Table B :   ( 1000 ) is forecasting value 

       

      1000  * SP ( 0.004 ) ( Explained below ) *  QTY for ITEM_ID on Table B ( 0.15 )


      How can I call the value which is ( 0.004 ) for ( SP2215 ) from TABLE A when it becomes an ( ITEM_ID ) and apply it on table B ?!  It’s on red color on Table A

      Thank you !

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