Help to split a row into two based on condition on Power Query

[eduardofavilla]

Hi team! I am struggling to split a row into two based on condition on Power Query.

I have the table below where there is a column called CL4. Every time this column has the word "Flemingo", the value on Amount column should be split in two rows, one with 22% of the original value and other with 73%.

Does anyone can give me a help to achieve this solution?

Below the original table:

 Cust Hier Level 4  Forecast Group  Date  Net net proceeds of sales [3060] 

L4 28 Kaimay Trading	28 INFANT FORMULA	17-Jan-22	79,522.73
L4 28 Flemingo	28 NESCAFE	30-Jan-23	133,834.46

And the desired table:

 Cust Hier Level 4  Forecast Group  Date  Net net proceeds of sales [3060] 

L4 28 Kaimay Trading	28 INFANT FORMULA	17-Jan-22	79,522.73
L4 28 Flemingo	28 NESCAFE	30-Jan-23	30,476.71
L4 28 Flemingo	28 NESCAFE	30-Jan-23	103,357.75

[rajendraongole1]

Hi @eduardofavilla - Create a calculated table which contain the split rows and can be used in your visuals and reports

SplittedSalesTable =
VAR SplitFlemingo =
ADDCOLUMNS (
FILTER ( Sales, CONTAINSSTRING ( Sales[Cust Hier Level 4], "Flemingo" ) ),
"SplitAmount1", Sales[Net net proceeds of sales [3060]] * 0.22,
"SplitAmount2", Sales[Net net proceeds of sales [3060]] * 0.78
)
VAR UnsplitTable =
FILTER ( Sales, NOT CONTAINSSTRING ( Sales[Cust Hier Level 4], "Flemingo" ) )
VAR ResultTable =
UNION (
SELECTCOLUMNS (
SplitFlemingo,
"Cust Hier Level 4", [Cust Hier Level 4],
"Forecast Group", [Forecast Group],
"Date", [Date],
"Net net proceeds of sales [3060]", [SplitAmount1]
),
SELECTCOLUMNS (
SplitFlemingo,
"Cust Hier Level 4", [Cust Hier Level 4],
"Forecast Group", [Forecast Group],
"Date", [Date],
"Net net proceeds of sales [3060]", [SplitAmount2]
),
SELECTCOLUMNS (
UnsplitTable,
"Cust Hier Level 4", [Cust Hier Level 4],
"Forecast Group", [Forecast Group],
"Date", [Date],
"Net net proceeds of sales [3060]", [Net net proceeds of sales [3060]]
)
)
RETURN
ResultTable

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!