Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Satish_Kumar
Helper V
Helper V

how to join two tables.

‎10-25-2016 12:53 AM

Hi All,

i want to know how can i join two tables with a common feild to make a resultant table.

Message 1 of 12
3,940 Views
0
11 REPLIES 11
BhaveshPatel
Community Champion
Community Champion

‎10-25-2016 01:19 AM

You can use Merge Queries option in Query Editor and can use Left Outer Join for the combined table.

 

Or

 

You can Drag and Drop the relationship arrow from the Table 1 to Table 2 between two common fields.

In this way, You can use the Relationship flow logic between tables and You would not create much more flattened tables. This approach is suitable in line with the best methodologies to create data warehouse.

 

Hope this has cleared your understanding.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Message 2 of 12
3,918 Views
0
Satish_Kumar
Helper V
Helper V
In response to BhaveshPatel

‎10-25-2016 01:34 AM

@BhaveshPatel Actually i have created realtionship between the  tables but i have to summerize the amount feild based on id which is in different table that why i need a join to create a new resultant table.

but i will try the merge query.

Message 3 of 12
3,915 Views
0
v-ljerr-msft
Employee
Employee
In response to Satish_Kumar

‎10-26-2016 12:08 AM

Hi @Satish_Kumar,

Actually i have created realtionship between the  tables but i have to summerize the amount feild based on id which is in different table that why i need a join to create a new resultant table.

As you have created a relationship between the two tables, you don't need a join to create a new resultant table to summarize the amount field based on id which is in different table. You can just use the formula below to create a calculate column in the second table to summarize the amount field in first table.

Amount = CALCULATE ( SUM ( Table1[Amount Including Tax] ) )

cc.PNG

 

Regards

Message 10 of 12
3,858 Views
0
Satish_Kumar
Helper V
Helper V
In response to v-ljerr-msft

‎10-26-2016 12:14 AM

hi @v-ljerr-msft 

yes i have done that too. but will you please send me a syntax of joining two table in DAX to make resultant table.

beside of this exaple.

And Lastly thanks for help.

 

Reagrds,

Satish Kumar

Message 11 of 12
3,431 Views
0
v-ljerr-msft
Employee
Employee
In response to Satish_Kumar

‎10-26-2016 12:27 AM

Hi @Satish_Kumar,

 

Have you tried to make the resultant table in Query Editor as BhaveshPatel mentioned above? It should work.Smiley Happy

 

Regards

Message 12 of 12
3,428 Views
0
BhaveshPatel
Community Champion
Community Champion
In response to Satish_Kumar

‎10-25-2016 01:37 AM

Love to recreate a solution for you if you can post sample data and expected output.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Message 4 of 12
3,910 Views
0
Satish_Kumar
Helper V
Helper V
In response to BhaveshPatel

‎10-25-2016 02:02 AM

@BhaveshPatel

 

i am sending you the demo data for this issue.

 

 

Document No_Buy-from Vendor No_Posting GroupAmount Including Tax
PINV/0809/00001VEN0027FINISHED220895
PINV/0809/00002VEN0027FINISHED625408
PINV/0809/00003VEN0027FINISHED33098
PINV/0809/00004VEN0004FINISHED322341
PINV/0809/00005VEN0004FINISHED3961
PINV/0809/00006VEN0004 49838
PINV/0809/00006VEN0004 0.02
PINV/0809/00007VEN0027FINISHED15912
PINV/0809/00008VEN0027FINISHED336177
PINV/0809/00009VEN0027FINISHED710793
PINV/0809/00010VEN0004FINISHED2154058
PINV/0809/00011VEN0006FINISHED23204
PINV/0809/00012VEN0006FINISHED946444
PINV/0809/00013VEN0004 191290
PINV/0809/00013VEN0004 -0.45
PINV/0809/00014VEN0039 48687
PINV/0809/00015VEN0040COMPUTERS30720
PINV/0809/00016VEN0041OFFICE EQU1260
PINV/0809/00016VEN0041OFFICE EQU1500
PINV/0809/00017VEN0013UPS & BATT73500
PINV/0809/00018VEN0010FINISHED22781
PINV/0809/00018VEN0010FINISHED5923
PINV/0809/00019VEN0004FINISHED1057044.34
PINV/0809/00019VEN0004 -0.34
PINV/0809/00020VEN0004FINISHED8225
PINV/0809/00021VEN0004 147258
PINV/0809/00021VEN0004 -0.09
PINV/0809/00022VEN0027FINISHED301702.91
PINV/0809/00022VEN0027 0.09
PINV/0809/00023VEN0027FINISHED39671.1
PINV/0809/00023VEN0027 -0.1
PINV/0809/00024VEN0027FINISHED678730.08
PINV/0809/00024VEN0027 -0.08
PINV/0809/00025VEN0027FINISHED787962.96
PINV/0809/00025VEN0027 0.04
PINV/0809/00026VEN0027FINISHED229900.19
PINV/0809/00026VEN0027 -0.19
PINV/0809/00027VEN0027FINISHED131541.53
PINV/0809/00027VEN0027 0.47
PINV/0809/00028VEN0006FINISHED5886013.26
PINV/0809/00028VEN0006 -0.26
PINV/0809/00029VEN0004 20025
PINV/0809/00029VEN0004 -0.09
PINV/0809/00030VEN0004FINISHED143743.03
PINV/0809/00030VEN0004 -0.03
PINV/0809/00031VEN0004 15416
PINV/0809/00031VEN0004 -0.42
PINV/0809/00032VEN0004FINISHED110657.55
PINV/0809/00032VEN0004 0.45
PINV/0809/00033VEN0018COMPUTERS30000
PINV/0809/00034VEN0043COMPUTERS23500
PINV/0809/00035VEN0004FINISHED148066.35
PINV/0809/00036VEN0044FURNITURE167562
PINV/0809/00037VEN0004FINISHED180676.66
PINV/0809/00037VEN0004 200
PINV/0809/00038VEN0010FINISHED5923
PINV/0809/00038VEN0010 22781
PINV/0809/00039VEN0045 37079
PINV/0809/00040VEN0004 15416
PINV/0809/00040VEN0004 -0.42
PINV/0809/00040VEN0004 0.42
PINV/0809/00041VEN0004 20025
PINV/0809/00041VEN0004 -0.09
PINV/0809/00041VEN0004 0.09
PINV/0809/00042VEN0004FINISHED110657.55
PINV/0809/00042VEN0004 0.45
PINV/0809/00042VEN0004 -0.45
PINV/0809/00043VEN0004FINISHED143743.03

 

Message 5 of 12
3,900 Views
0
Satish_Kumar
Helper V
Helper V
In response to Satish_Kumar

‎10-25-2016 02:04 AM

2nd table.

No_Buy-from Vendor No_Posting DatePosting Description
PINV/0809/00001VEN00274/15/2008 0:00Order PO00001
PINV/0809/00002VEN00274/15/2008 0:00Order PO00002
PINV/0809/00003VEN00274/21/2008 0:00Order PO00003
PINV/0809/00004VEN00044/22/2008 0:00Order PO00004
PINV/0809/00005VEN00044/22/2008 0:00Order PO00005
PINV/0809/00006VEN00044/22/2008 0:00Order PO00006
PINV/0809/00007VEN00275/7/2008 0:00Order PO00007
PINV/0809/00008VEN00275/7/2008 0:00Order PO00008
PINV/0809/00009VEN00275/8/2008 0:00Order PO00009
PINV/0809/00010VEN00045/31/2008 0:00Order PO00010
PINV/0809/00011VEN00065/29/2008 0:00Order PO00012
PINV/0809/00012VEN00065/29/2008 0:00Order PO00013
PINV/0809/00013VEN00045/12/2008 0:00Order PO00011
PINV/0809/00014VEN00394/17/2008 0:00Order PO00015
PINV/0809/00015VEN00406/1/2008 0:00Order PO00016
PINV/0809/00016VEN00415/14/2008 0:00Order PO00017
PINV/0809/00017VEN00135/12/2008 0:00Order PO00018
PINV/0809/00018VEN00106/29/2008 0:00Order PO00019
PINV/0809/00019VEN00047/7/2008 0:00Order PO00020
PINV/0809/00020VEN00047/7/2008 0:00Order PO00021
PINV/0809/00021VEN00047/7/2008 0:00Order PO00022
PINV/0809/00022VEN00276/27/2008 0:00Order PO00023
PINV/0809/00023VEN00276/25/2008 0:00Order PO00024
PINV/0809/00024VEN00276/27/2008 0:00Order PO00025
PINV/0809/00025VEN00276/27/2008 0:00Order PO00026
PINV/0809/00026VEN00276/11/2008 0:00Order PO00027
PINV/0809/00027VEN00277/14/2008 0:00Order PO00028
PINV/0809/00028VEN00067/11/2008 0:00Order PO00029
PINV/0809/00029VEN00047/11/2008 0:00Order PO00030
PINV/0809/00030VEN00047/11/2008 0:00Order PO00031
PINV/0809/00031VEN00047/11/2008 0:00Order PO00032
PINV/0809/00032VEN00047/11/2008 0:00Order PO00033
PINV/0809/00033VEN00187/29/2008 0:00Order PO00035
PINV/0809/00034VEN00436/20/2008 0:00Order PO00036
PINV/0809/00035VEN00048/8/2008 0:00Order PO00038
PINV/0809/00036VEN00441/1/2009 0:00Order PO00039
PINV/0809/00037VEN00047/24/2008 0:00Order PO00040
PINV/0809/00038VEN00107/12/2008 0:00Order PO00034
PINV/0809/00039VEN00457/30/2008 0:00Order PO00043
Message 6 of 12
3,897 Views
0
Satish_Kumar
Helper V
Helper V
In response to Satish_Kumar

‎10-25-2016 02:05 AM

@BhaveshPatel on the basis of No_ feild i have to join these table together to summerize the amount feild.

Message 7 of 12
3,896 Views
0
Satish_Kumar
Helper V
Helper V
In response to Satish_Kumar

‎10-25-2016 03:19 AM

Hi @BhaveshPatel hoping that you found the solution for this issue i am waiting for the solution.

Message 8 of 12
3,887 Views
0
BhaveshPatel
Community Champion
Community Champion
In response to Satish_Kumar

‎10-25-2016 03:57 AM

@Satish_Kumar

Sorry to keep you waiting. As I was working on the other problem.

 

Please follow the attached screenshot for the solution.

 

Step 1: Go to the Table 1 and select Edit Queries, Select the Merge as a new Queries.

Merge QueriesMerge Queries

Step 2: Expand the newly created column and Select the amount including Tax.

Expand the newly created columnExpand the newly created column

Step 3 : Final Results

Final outputFinal output

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Message 9 of 12
3,881 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All