Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Subscription | InstanceID | Category | November Rate |
Sub1 | 123 | ABC | 10 |
Sub1 | 123 | DEF | 10 |
Sub2 | 1234 | ABC | 10 |
Sub3 | 456 | XYZ | 10 |
Subscription | InstanceID | Category | DEC Rate |
Sub1 | 123 | ABC | 10 |
Sub1 | 123 | DEF | 10 |
Sub2 | 1234 | ABC | 10 |
Sub3 | 456 | XYZ | 10 |
Solved! Go to Solution.
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.
Repeat above step in Dec Table. Then, you can merge these two tables.
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] )
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
Cool My dear friend ,
We can go with Power Query, in there we can merge the two tables
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?
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.
Repeat above step in Dec Table. Then, you can merge these two tables.
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] )
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
Hello, Any update on this please?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
65 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |