Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
2 years ago
Solved

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

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.