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

Question

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:&nbsp;Cust Hier Level 4&nbsp;&nbsp;Forecast Group&nbsp;&nbsp;Date&nbsp;&nbsp;Net net proceeds of sales [3060]&nbsp;L4 28 Kaimay Trading28 INFANT FORMULA17-Jan-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 79,522.73L4 28 Flemingo28 NESCAFE30-Jan-23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 133,834.46&nbsp;And the desired table:&nbsp;Cust Hier Level 4&nbsp;&nbsp;Forecast Group&nbsp;&nbsp;Date&nbsp;&nbsp;Net net proceeds of sales [3060]&nbsp;L4 28 Kaimay Trading28 INFANT FORMULA17-Jan-22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 79,522.73L4 28 Flemingo28 NESCAFE30-Jan-23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,476.71L4 28 Flemingo28 NESCAFE30-Jan-23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 103,357.75

rajendraongole1 · Answer

Hi&nbsp;eduardofavilla&nbsp;- Create a calculated table which contain the split rows and can be used in your visuals and reports&nbsp;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]]))RETURNResultTable&nbsp;Did I answer your question? Mark my post as a solution! This will help others on the forum!Appreciate your Kudos!!

Forum Discussion

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

1 Reply

Related content

Split Rows based on Condition

Conditional Split Column by delimiter based on value

Split rows based on condition

Splitting Column values

Conditional formatting based on rules

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