Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredJoin 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.
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 |
Solved! Go to 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.
Hi,
Do you want to do this in the Query Editor or directly in the visual?
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.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
42 | |
30 | |
27 | |
27 |