Split one row in two or more

[Anonymous]

Hi Family,

I have a Sales table with a column of terms payments, as you can see below:

o 50 * SD * 10 means that the calculated DueDate is equal to “Ship Date” + 10 and that the amount to be paid is 50% of the amount of the line

o 50 * SD * 60 means that the calculated DueDate is equal to " Ship Date " + 60 and that the amount to be paid is 50% of the amount of the line

So if I have a purchase of $ 150,000 with this payment term and a " Ship Date " = 2021-06-01, we should find in the model of two purchases

• $ 75,000 payable on 2021-06-11 (50% 10 days later than Ship Date)
• $ 75,000 payable on 2021-07-30 (50% 60 days later than Ship Date)

Importantly, the original line of $ 150,000 should no longer be there or no longer counted. We could keep it by changing the amount payable to $ 0, so nothing to pay.

My question is, how can I split the original row in two (or more in another case)?  

Thank you all 🙂

[v-yalanwu-msft]

Hi， @Anonymous ；

I am not very clear about your split logic. For your description, I think it can be extracted and splited in power query.as follows:

1.Add a custom column.

=Text.Select([strTerm],{" ".."9"})

2.Write a formula

if not Text.Contains([Custom], "%") then " "&[Custom] else [Custom]

3.split column

The final output is shown below:

If this is not correct, can you share the following screenshot of the result you want to output and the logic of your split？
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi,
Sorry for the delay but I was very busy with this issue.
The problem is I want to split a ROW not a column and that is not possible with Power BI.
I tried a lot of ways and manners but it doesn't work and my report is so complicated.
I decided to split what I need on SQL than in Power BI.
Thank you all 🙂