0 votes
1 view
in BI by (9.4k points)

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> 

1 Answer

0 votes
by (19.3k points)

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   

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

 

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked 19 hours ago in BI by Chris (9.4k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...