Combine tables in Power BI

Question

How can I combine tables in Power Bi

I had a following table 

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

 

and I want to combine in this way

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

Answer 1

Use the following query:

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

Want to learn more about Power Bi, Refer & learn Power bi   

Want to earn a certificate in Power BI? here is the Power BI Course that will make you industry ready.

Also, check out our YouTube video to know the data visualization basics and best practices with Power BI