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
• 7,050 points
2,080 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
• 52,360 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"})
 

Preparing for Power BI exam? Check out Power BI Syllabus now!

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,220 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,020 points
2,491 views
0 votes
4 answers

How to combine multiple tables in power BI?

Click Advanced Editor and check if step #"Appended ...READ MORE

answered Dec 16, 2020 in Power BI by Roshni
• 10,520 points
69,013 views
0 votes
3 answers

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

To do so, follow these steps: From the ...READ MORE

answered Dec 16, 2020 in Power BI by Gitika
• 65,910 points
53,371 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
• 33,030 points
12,955 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
• 52,360 points
921 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 23, 2019 in Power BI by VNK
18,405 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
• 52,360 points
36,920 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,570 points
796 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
• 52,360 points
1,017 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
• 52,360 points
1,050 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP