Skip to main content
cancel
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
mrvamsidhar
Helper I
Helper I

Merge two Tables and add Custom columns

Hello, I am having two tables in below format (sample) one for Nov Rate and one for December rate. I need to create report combining these two tables.

How can i map two tables when there is no unique columns values to map these tables.?

 Also, I need to add aditional columns after merging two tables?

 

SubscriptionInstanceIDCategoryNovember Rate
Sub1123ABC10
Sub1123DEF10
Sub21234ABC10
Sub3456XYZ10
SubscriptionInstanceIDCategoryDEC Rate
Sub1123ABC10
Sub1123DEF10
Sub21234ABC10
Sub3456XYZ10

  

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @mrvamsidhar,

 

As per my understaning, you are trying to merge two tables which means you want a new table containing all the columns in Nov Table and Dec Table, right? 

 

If that is the case, to address the problem that there is no unique column values to map tables, you can combine three columns into a single one as unique column. 
1.PNG

 

Repeat above step in Dec Table. Then, you can merge these two tables.
2.PNG

 

Alternatively you can achieve the same result using DAX. Just use LookUp value to fetch the [DEC Rate] from Dec Table and insert into Nov Table.

December Rate =
LOOKUPVALUE (
    Dec[DEC Rate],
    Dec[Subscription], Nov[Subscription],
    Dec[InstanceID], Nov[InstanceID],
    Dec[Category], Nov[Category]
)

3.PNG

 

To your second requirement, what do you mean add aditional columns after merging two tables? What aditional columns do you want to add? You can add calculated column via writing DAX formula. Also, in query editor, under the Add Column pane in the top ribbon, you can select to create custom columns.

 

If I have something misunderstood, please correct me and provide more description about your expect result.

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Baskar
Resident Rockstar
Resident Rockstar

Cool My dear friend , 

 

We can go with Power Query, in there we can merge the two tables 

 

1.JPG

Thank You very much.  It helped me.

 

I had one more question on custome column- http://community.powerbi.com/t5/Desktop/Adding-Custom-Column-Parsing-Existing-Column/td-p/111618.

 

Can you help me on this?

v-yulgu-msft
Employee
Employee

Hi @mrvamsidhar,

 

As per my understaning, you are trying to merge two tables which means you want a new table containing all the columns in Nov Table and Dec Table, right? 

 

If that is the case, to address the problem that there is no unique column values to map tables, you can combine three columns into a single one as unique column. 
1.PNG

 

Repeat above step in Dec Table. Then, you can merge these two tables.
2.PNG

 

Alternatively you can achieve the same result using DAX. Just use LookUp value to fetch the [DEC Rate] from Dec Table and insert into Nov Table.

December Rate =
LOOKUPVALUE (
    Dec[DEC Rate],
    Dec[Subscription], Nov[Subscription],
    Dec[InstanceID], Nov[InstanceID],
    Dec[Category], Nov[Category]
)

3.PNG

 

To your second requirement, what do you mean add aditional columns after merging two tables? What aditional columns do you want to add? You can add calculated column via writing DAX formula. Also, in query editor, under the Add Column pane in the top ribbon, you can select to create custom columns.

 

If I have something misunderstood, please correct me and provide more description about your expect result.

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mrvamsidhar
Helper I
Helper I

Hello, Any update on this please?

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.

Top Solution Authors