Combine tables in Power BI

+3 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 do I achieve this using DAX?

Oct 3, 2018 in Power BI by Nilesh
• 6,970 points
570 views

2 answers to this question.

0 votes

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

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

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]
)
answered Oct 3, 2018 by Kalgi
• 51,890 points
0 votes

You can also achieve this using a power 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]))
Delete columns Id and Table2.Id.

Hope this helps..

answered Oct 5, 2018 by lina
• 8,200 points

edited Oct 11, 2018 by Kalgi

Related Questions In Power BI

0 votes
1 answer

How to combine your data or tables in Power BI?

Power BI includes Query Editor which is ...READ MORE

answered Feb 27, 2019 in Power BI by Phalguni
• 1,010 points
519 views
0 votes
1 answer

How to combine multiple tables in power BI?

Hi Sindhu, You can merge the 3 tables ...READ MORE

answered Mar 14, 2019 in Power BI by Cherukuri
• 32,460 points
9,441 views
0 votes
1 answer

How to create final table based on Joins of two tables in power BI?

Hi Nithin, It is easy and simple. 1. Go ...READ MORE

answered Mar 1, 2019 in Power BI by Cherukuri
• 32,460 points
6,166 views
0 votes
1 answer

Create custom tables in power BI

Hi Sindhu, You can create custom tables by clicking ...READ MORE

answered May 2, 2019 in Power BI by Cherukuri
• 32,460 points
116 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 51,890 points
237 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 22, 2019 in Power BI by VNK
14,516 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 51,890 points
21,045 views
0 votes
1 answer

Dax code for calculated columns

I would recommend you to adjust the ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,070 points
206 views
0 votes
1 answer

Programmatically sign in - power bi

Basically: POST request to: https://login.microsoftonline.com/common/oauth2/token Body, form-url-encoded: grant_type: "password" scope: "openid" resource: "https://analysis.windows.net/powerbi/api" client_id: ...READ MORE

answered Sep 19, 2018 in Power BI by Kalgi
• 51,890 points
242 views
0 votes
1 answer

create interactive R plots in Power BI

 Right now Power BI only supports render ...READ MORE

answered Sep 20, 2018 in Power BI by Kalgi
• 51,890 points
337 views