Combine tables in Power BI

0
votes

I'm trying to combine two tables in Power BI.

I have the following two tables:

<table border="1">
<tr><th>Id</th><th>Category</th></tr>
<tr><td>1</td><td>A</td></tr>
<tr><td>1</td><td>B</td></tr>
<tr><td>2</td><td>A</td></tr>
<tr><td>3</td><td>B</td></tr>
<tr><td>3</td><td>C</td></tr>
<tr><td>4</td><td>A</td></tr>
</table>
<br>
<br>
<table border="1">
<tr><th>Id</th><th>Value</th></tr>
<tr><td>1</td><td>10</td></tr>
<tr><td>1</td><td>20</td></tr>
<tr><td>3</td><td>10</td></tr>
<tr><td>3</td><td>30</td></tr>
<tr><td>4</td><td>20</td></tr>
<tr><td>4</td><td>30</td></tr>
<tr><td>5</td><td>10</td></tr>
</table>

I need to combine them as below:

<table border="1">
<tr><th>Id</th><th>Category</th><th>Value</th></tr>
<tr><td>1</td><td>A</td><th>10</th></tr>
<tr><td>1</td><td>A</td><th>20</th></tr>
<tr><td>1</td><td>B</td><th>10</th></tr>
<tr><td>1</td><td>B</td><th>20</th></tr>
<tr><td>2</td><td>A</td><th>BLANK</th></tr>
<tr><td>3</td><td>B</td><th>10</th></tr>
<tr><td>3</td><td>B</td><th>30</th></tr>
<tr><td>3</td><td>C</td><th>10</th></tr>
<tr><td>3</td><td>C</td><th>30</th></tr>
<tr><td>4</td><td>A</td><th>20</th></tr>
<tr><td>4</td><td>A</td><th>30</th></tr>
<tr><td>5</td><td>BLANK</td><th>10</th></tr>
</table>

How can this be achieved using the DAX in Power BI ?

2
powerbidax

2 Answers

3
votes

First, you will need a distinct list of all IDs between your two tables. To get this in PowerBI, click on 'Modeling' -> 'New Table' and enter this formula.

IDs = DISTINCT(UNION(
    SELECTCOLUMNS(Categories, "ID", Categories[Id]), 
    SELECTCOLUMNS('Values', "ID", 'Values'[Id]))
)

ID Table

This table will help create a many-to-many relationship between your category table and value table.

Relationship

With that relationship in place, you can create another new table with this formula to get you results.

Results = SELECTCOLUMNS(NATURALLEFTOUTERJOIN(NATURALLEFTOUTERJOIN(IDs, Categories), 'Values'),
    "ID", IDs[ID], 
    "Category", Categories[Category], 
    "Value", 'Values'[Value]
)

Results

1
votes

(a Power Query, not DAX solution)

Add a blank query:

= Table.NestedJoin(Table1,{"Id"},Table2,{"Id"},"Table2",JoinKind.FullOuter)

Add a transformation step:

= Table.ExpandTableColumn(Source, "Table2", {"Id", "Value"}, {"Table2.Id", "Value"})

Add step:

= Table.AddColumn(#"Expanded Table2", "NewId", each (if [Id] = null then [Table2.Id] else [Id]))

Delete columns Id and Table2.Id.

Alternatively, a GUI approach via PowerBI Desktop:

  • Open query editor -> Home tab
  • Merge queries -> As New
  • Select both tables, highlight Id column, Full outer join
  • Expand the last column.
  • Rename columns appropriately.
  • Coalesce Id columns using the method of your choice.