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

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

Reply
InsightSeeker
Helper III
Helper III

Join the data from multiple columns and display the data

Hello

 

I need help to create a new table that should join the data from multiple columns and display the data in new table as per the result shown below.

 

DATA

 

TABLE DUMPDATA

 

unique_keyproduct_idticket_idFromToCategoryFrom DateTo DateCoupon_NoBase
1090782190782DXBEBBQ02-Apr-2402-Apr-241OLK
1090782190782EBBDXBL11-Apr-2411-Apr-242OLK
1091197191197DXBLAXW05-Apr-2405-Apr-241WLW
1091197191197LAXDXBW13-Apr-2414-Apr-242WLW
1091198191198LASLAXQ12-Apr-2412-Apr-241QAQ
1091762191762DXBLAXE24-Mar-2424-Mar-241ELX
1091762191762LAXDXBW12-Apr-2413-Apr-242ELX

 

RESULT

 

NEW TABLE DUMPDATARESULT

 

unique_keyAreaCategoryDateBase
1090782DXB-EBB-DXBQ-L02-04-2024 / 11-04-2024OLK-OLK
1091197DXB-LAX-DXBW-W05-04-2024 / 14-04-2024WLW-WLW
1091198LAS-LAXQ12-Apr-24QAQ
1091762DXB-LAX-DXBE-W24-03-2024 / 12-Apr-24ELX-ELX

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @InsightSeeker ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

Rank = RANKX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[From Date],,ASC,Dense)
Area = 
var _a= CONCATENATEX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[From],"-") 
var _max_rank=MAXX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Rank])
var _b=  CALCULATE(MAX('Table'[To]),FILTER('Table','Table'[unique_key]=EARLIER('Table'[unique_key]) && 'Table'[Rank]=_max_rank))
RETURN _a & "-"  & _b
_Category = 
CONCATENATEX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Category],"-") 
Date = 
var _min_rank=MINX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Rank])
var _max_rank=MAXX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Rank])
var _min_date=CALCULATE(MIN('Table'[From Date]),FILTER('Table','Table'[unique_key]=EARLIER('Table'[unique_key]) && 'Table'[Rank]=_min_rank))
var _max_date=CALCULATE(MAX('Table'[To Date]),FILTER('Table','Table'[unique_key]=EARLIER('Table'[unique_key]) && 'Table'[Rank]=_max_rank))
RETURN IF(_min_date=_max_date, CONVERT(_min_date,STRING),_min_date & " / " & _max_date)
_Base = 
CONCATENATEX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Base],"-") 

(3) We can create a table.

Table 2 = SUMMARIZE('Table',[unique_key],[Area],"Category",MAX('Table'[_Category]),"Date",MAX('Table'[Date]),"Base",MAX('Table'[_Base]))

vtangjiemsft_1-1712715318351.png

Best Regards,

Neeko Tang

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

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Do you want to do this in the Query Editor or directly in the visual?


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

Hi @InsightSeeker ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

Rank = RANKX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[From Date],,ASC,Dense)
Area = 
var _a= CONCATENATEX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[From],"-") 
var _max_rank=MAXX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Rank])
var _b=  CALCULATE(MAX('Table'[To]),FILTER('Table','Table'[unique_key]=EARLIER('Table'[unique_key]) && 'Table'[Rank]=_max_rank))
RETURN _a & "-"  & _b
_Category = 
CONCATENATEX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Category],"-") 
Date = 
var _min_rank=MINX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Rank])
var _max_rank=MAXX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Rank])
var _min_date=CALCULATE(MIN('Table'[From Date]),FILTER('Table','Table'[unique_key]=EARLIER('Table'[unique_key]) && 'Table'[Rank]=_min_rank))
var _max_date=CALCULATE(MAX('Table'[To Date]),FILTER('Table','Table'[unique_key]=EARLIER('Table'[unique_key]) && 'Table'[Rank]=_max_rank))
RETURN IF(_min_date=_max_date, CONVERT(_min_date,STRING),_min_date & " / " & _max_date)
_Base = 
CONCATENATEX(FILTER('Table',[unique_key]=EARLIER('Table'[unique_key])),[Base],"-") 

(3) We can create a table.

Table 2 = SUMMARIZE('Table',[unique_key],[Area],"Category",MAX('Table'[_Category]),"Date",MAX('Table'[Date]),"Base",MAX('Table'[_Base]))

vtangjiemsft_1-1712715318351.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors