Need help - Merging data based of Values

[gauravnarchal]

Hello All

I need your help in merging the data from multiple columns into one based on the values. Instead of creating a calculated column, I would like to create a new table.

I have attached the PBIX sample data for your kind assistance. Click Here

Condition

Of each InvoicedetailID  -        Find first SegmentID & First Leg Number and then Merge DepCityCode & ArrCityCode

& Of First SegmentID – find Second Leg Number and then Merge DepCityCode & ArrCityCode

& Of First SegmentID - find Third Leg Number and then Merge DepCityCode & ArrCityCode

& Of First SegmentID - find Fourth Leg Number and then Merge DepCityCode & ArrCityCode

                                                Continue……

While merging the data XO column is to be taken into consideration when merging.

All O values should be replaced with “*“

All X values should be replaced with “/“

And “-“ is to be used between DepCityCode and ArrCityCode in the final result.

The result column should merge the data and show as

For each InvoiceDetailsID - Segment column should show as  First DepCityCode-ArrCityCode(XO Condition)Second DepCityCode- Second ArrCityCode(XO Condition)Third DepCityCode & Third ArrCityCode…….....

Result Table is to be displayed as below.

InvoiceDetailID	Segment
2459255	CAI-IST*IST-MAD
2459256	DXB-DOH/DOH-IAH*IAH-DOH/DOH-DXB
2459260	LHR-DXB*DXB-ATH
2459262	ISU-DXB*DXB-EBL
2459395	GLA-AMS/AMS-DXB*DXB-AMS/AMS-GLA

Thanks

Gaurav

[richbenmintz · Accepted solution]

Hi @gauravnarchal ,

couple options:

1. create a calc column to concatente the results for each invoicedetailid

segments = 
var _idi = [InvoiceDetailID]
var _sep = ""
return
left(
    CONCATENATEX(FILTER('Sample', 'Sample'[InvoiceDetailID] = _idi) , 'Sample'[DepartureCityCode]&"-"&[ArrivalCityCode]&if([XO] = "X", "/","*"),"", [LegNumberTest Rank], ASC), 
    len(CONCATENATEX(FILTER('Sample', 'Sample'[InvoiceDetailID] = _idi) , 'Sample'[DepartureCityCode]&"-"&[ArrivalCityCode]&if([XO] = "X", "/","*"), "", [LegNumberTest Rank], ASC))
    -1
    )

2. create a calc table that summerizes the data at the invoice and invoicedetailid

group invoice details = 
SUMMARIZE('Sample',
'Sample'[InvoiceID],
'Sample'[InvoiceDetailID],
"segments", var _idi = [InvoiceDetailID]
var _sep = ""
return
left(
    CONCATENATEX(FILTER('Sample', 'Sample'[InvoiceDetailID] = _idi) , 'Sample'[DepartureCityCode]&"-"&[ArrivalCityCode]&if([XO] = "X", "/","*"),"", [LegNumberTest Rank], ASC), 
    len(CONCATENATEX(FILTER('Sample', 'Sample'[InvoiceDetailID] = _idi) , 'Sample'[DepartureCityCode]&"-"&[ArrivalCityCode]&if([XO] = "X", "/","*"), "", [LegNumberTest Rank], ASC))
    -1
    ))

attached please find your sample with the formulas,

hope this helps,