PowerBI calculated table - how to combine rows for the same group accounting for nulls?

[hedgy123]

Hi,

   I have a calculated table that's the result of a UNION betwee two other tables, that results in data looking like this:

ID     Var1    Var2

1       NA        5

1        4          NA

2        6           7

     I'd like to combine the rows for the same ID so that the table above would look like:

ID      Var1      Var2

1          4           5

2          6          7

    But because it's a calculated table my options seem to be really limited.  I know how to do the above in R using tidyverse's fill function, but can't seem to figure out how to do this in PowerBI.  Any help would be appreciated!

[v-zhenbw-msft · Accepted solution]

Hi @hedgy123 ,

You can do it in Query Editor, also you can do it using DAX.

The first method is in Query Editor.

1. Append two tables.

2. Select Var1 and Var2, click fill up and fill down.

3. At last, select the id column and remove the duplicated.

The second way is using DAX.

1. Create four columns in Table 2.

New var1 = CALCULATE(SUM('Table'[Var1]),FILTER('Table','Table'[ID]='Table (2)'[ID]))

New var2 = CALCULATE(SUM('Table'[Var2]),FILTER('Table','Table'[ID]='Table (2)'[ID]))

Result var1 = 
IF(
    ISBLANK([Var1]),[New var1],[Var1])

Result var2 = 
IF(
    ISBLANK([Var2]),[New var2],[Var2])

2. Then create the calculate table.

New table = SUMMARIZE('Table (2)','Table (2)'[ID],'Table (2)'[Result var1],'Table (2)'[Result var2])

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.