Create new rows based on values from several rows

[lsihui_]

Hi, 

Would like to check if it is possible to create new rows based on the value of existing rows? If yes, please advise how I can do this. Thank you! 

Sample below: 

• Row 12-17: Available in original dataset
• Row 18-19: Want to create them on PBI

[Anonymous]

Your solution is so great Sahir_Maharaj 

Hi, @lsihui_ 

In Power BI, we may not be able to insert rows on the go like Excel.
As provided by Super user, joining two tables together via the UNION function is how Power BI handles it.
If you want to customize the rows, you'll need to use a tool like Excel to insert them.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sahir_Maharaj · Accepted solution]

Hello @lsihui_,

Can you please try this approach:

NewTable = 
UNION (
    'ExistingTable',
    ADDCOLUMNS (
        FILTER ( 'ExistingTable', 'ExistingTable'[Property] IN { "Apricot", "Blackcurrant" }),
        "Property", IF ( 'ExistingTable'[Property] = "Apricot", "Combined A", "Combined B" ),
        "Value",
            VAR GroupA = CALCULATE( SUM('ExistingTable'[Value]), 'ExistingTable'[Group] = "A" )
            VAR GroupB = CALCULATE( SUM('ExistingTable'[Value]), 'ExistingTable'[Group] = "B" )
            RETURN 
                IF ( 'ExistingTable'[Property] = "Apricot", GroupA, GroupB ) - 'ExistingTable'[Value]
    )
)