Join the data from multiple columns and display the data

[InsightSeeker]

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_key	product_id	ticket_id	From	To	Category	From Date	To Date	Coupon_No	Base
1090782	1	90782	DXB	EBB	Q	02-Apr-24	02-Apr-24	1	OLK
1090782	1	90782	EBB	DXB	L	11-Apr-24	11-Apr-24	2	OLK
1091197	1	91197	DXB	LAX	W	05-Apr-24	05-Apr-24	1	WLW
1091197	1	91197	LAX	DXB	W	13-Apr-24	14-Apr-24	2	WLW
1091198	1	91198	LAS	LAX	Q	12-Apr-24	12-Apr-24	1	QAQ
1091762	1	91762	DXB	LAX	E	24-Mar-24	24-Mar-24	1	ELX
1091762	1	91762	LAX	DXB	W	12-Apr-24	13-Apr-24	2	ELX

RESULT

NEW TABLE DUMPDATARESULT

unique_key	Area	Category	Date	Base
1090782	DXB-EBB-DXB	Q-L	02-04-2024 / 11-04-2024	OLK-OLK
1091197	DXB-LAX-DXB	W-W	05-04-2024 / 14-04-2024	WLW-WLW
1091198	LAS-LAX	Q	12-Apr-24	QAQ
1091762	DXB-LAX-DXB	E-W	24-03-2024 / 12-Apr-24	ELX-ELX

[Ashish_Mathur]

Hi,

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

[v-tangjie-msft · Accepted solution]

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]))

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.