cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arti_r Frequent Visitor
Frequent Visitor

split 1 row into 2 based on a value

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. 

 

Capture.PNG

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
vcastello Member
Member

Re: split 1 row into 2 based on a value

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

View solution in original post

1 REPLY 1
vcastello Member
Member

Re: split 1 row into 2 based on a value

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

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors