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

[AliNafa]

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

[jennratten]

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]

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 · Accepted solution]

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 )

[djurecicK2]

Hi @AliNafa ,

 You have several questions there. To determine if the first letter of a field is numeric, you can create a new columns using ISNUMBER on the first character of Item_ID.  

https://learn.microsoft.com/en-us/dax/isnumber-function-dax

https://learn.microsoft.com/en-us/dax/left-function-dax

[djurecicK2]

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]

Hi @djurecicK2

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 !