Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Need to create a table from the existing tables and union the data

Hi All

I have 2 tables A and B need to create C table from the A and B

In A table i have 4 columns 

In B table i have 4 columns

To Create C Table Need distinct data from 2 columns in A table and distinct data 2 columns in B table and union the data. 

sample data 

 

Table A
EmpId Date service no mobile 
5221/05/20201256695845213
7542/4/2020526458964125
856911/2/202025635289654
856911/2/202025635289654
2565/5/2020852659641253

 

Table B
EmpId Date roll of sal
20121/05/2020Null5
5552/4/2020Null6
77711/2/2020Null4
77711/2/2020Null5
255/5/2020Null2

 

Need to create Table C
EmpId Date DateKeyEmpID+DateKey
5221/05/2020210520205221052020
7542/4/2020242020754242020
856911/2/2020112202085691122020
2565/5/2020552020256552020
20121/05/20202105202020121052020
5552/4/2020242020555242020
77711/2/202011220207771122020
255/5/202055202025552020

 

Can anybody help me need to create SSAS tabularr model 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Venkateswara_ra 

you can try to use DAX to create a new table

Table C = 
VAR A=ADDCOLUMNS(SUMMARIZE('Table A','Table A'[EmpId ],'Table A'[Date ],"datekey",day('Table A'[Date ])&month('Table A'[Date ])&year('Table A'[Date ])),"empid+datekey",'Table A'[EmpId ]&[datekey])
VAR B=ADDCOLUMNS(SUMMARIZE('Table B','Table B'[EmpId ],'Table B'[Date ],"datekey",day('Table B'[Date ])&month('Table B'[Date ])&year('Table B'[Date ])),"empid+datekey",'Table B'[EmpId ]&[datekey])
return UNION(A,B)

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
ryan_mayu
Super User
Super User

@Venkateswara_ra 

you can try to use DAX to create a new table

Table C = 
VAR A=ADDCOLUMNS(SUMMARIZE('Table A','Table A'[EmpId ],'Table A'[Date ],"datekey",day('Table A'[Date ])&month('Table A'[Date ])&year('Table A'[Date ])),"empid+datekey",'Table A'[EmpId ]&[datekey])
VAR B=ADDCOLUMNS(SUMMARIZE('Table B','Table B'[EmpId ],'Table B'[Date ],"datekey",day('Table B'[Date ])&month('Table B'[Date ])&year('Table B'[Date ])),"empid+datekey",'Table B'[EmpId ]&[datekey])
return UNION(A,B)

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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