cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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

Accepted Solutions
Highlighted
Super User II
Super User II

@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
Highlighted
Super User II
Super User II

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors