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
Mile_josh
New Member

Add new column from existing table in new generated table (distinct and union)

I have multiple tables eg:

Table1    
UrlTitleTemp URL
https://example.com/Page Onehttps://example.com/page1
https://example.com/page2Page Two 
https://example.com/page5Page Fivehttps://example.com/pagefive

 

Table2 
UrlTitle
https://example.com/page3Page Three
https://example.com/ 

 

Table3 
UrlTitle
https://example.com/page3Page Three
https://example.com/page4Page Four

 

 

I want to generate a new table with a union with all tables and the value (URL) should be distinct. For all Table1 URLs, I also want Temp Url to be added to this newly generated table

Generated Table  
UrlTitleTemp url
https://example.com/Page Onehttps://example.com/page1
https://example.com/page2Page Two 
https://example.com/page5Page Fivehttps://example.com/pagefive
https://example.com/page3Page Three 
https://example.com/page4Page Four 

 

@Ashish_Mathur , @Arul 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, append the three tables and then Remove Duplicates.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, append the three tables and then Remove Duplicates.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Share some data to work with and show the expected result clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Arul
Super User
Super User

@Mile_josh ,

I have generated some sample data for your requirement and written the formula to polpulate the third table. Please refer them,
Table 1:

Arul_0-1672258355844.png

Table 2:

Arul_1-1672258376328.png

Table 3 (Populated usnig dax formula):

Table3 = 
UNION ( DISTINCT ( T1[Url] ), DISTINCT ( T1[Temp url] ), DISTINCT ( T2[Url] ) )

Arul_2-1672258444643.png

Thanks,

Arul





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

Proud to be a Super User!


LinkedIn


Hi @Arul 

For each Url in Table 1 , I want tempUrl in same row. The new generated table should have Column Url, Title, Tempurl. tempurl value for other tables would empty.

 

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.