Forum Discussion

lsihui_'s avatar
lsihui_
Frequent Visitor
1 year ago
Solved

Create new rows based on values from several rows

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

 

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    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]
    )
)

     

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    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]
    )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.