split 1 row into 2 based on a value

[arti_r]

Hi,

I'm pretty lost on how to go about doing this. I have a dataset that contains totals tied to specific accounts which are tied to 2 business units (Unit 1 and Unit 2). However, each account has a Location Code tied to it. If the location code is 750, that indicates that the value needs to be split between the 2 units. In the sample below, the first totals of $44.64, $ 1652.63 and $ 6141.55 need to be split evenly between Unit 1 and 2. 

I am unsure of how to accomplish this in Power BI. Is there some way I can do an IF statement to search for the '750' location code and then INSERT 2 new rows with the split USD_AMT?

[vcastello · Accepted solution]

Hi @arti_r

You can create two new columns called Business Unit 1 and Business Unit 2 and split the [USD_AMT] in between them. Although that implies that the columns that don't need to be splitted are going to somehow be splitted too ....

If that's OK for you ...

1.- Open the quety editor.
2.- Add a new personalized column called "Business Unit 1".

3.- Write the following code
    

      if [LOCATION_CODE] = "750" then [USD_AMT] * 0.5 else [USD_AMT]

(you can change 0.5 for any other percentage if the split is not equal)

4.- Add a new personalized column called "Business Unit 2".

5.- Write the following code
    

      if [LOCATION_CODE] = "750" then [USD_AMT] * 0.5 else null

6.- Apply the necessary data type transformation
7.- Close & Load

If you do this process you'll end up with a 'Business Unit 1' column with the splitted data for Business Unit 1 AND the not splitted data, and a 'Business Unit 2' column with only the Business 2 splitted data.

Hope that Helps

Vicente