Need help in creating new table and 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

[lbendlin · Accepted solution]

Create a helper column

XO2 = 'Sample'[DepartureCityCode] & "-" & 'Sample'[ArrivalCityCode] & switch('Sample'[XO],"X","/","*")

Then create the segment column

Segment = 
var t = CALCULATETable('Sample',ALLEXCEPT('Sample','Sample'[InvoiceDetailID]))
var l=CONCATENATEX(t,[XO2],"",[SegmentID])
return left(l,LEN(l)-1)

You removed the Leg Number from your source data so maybe it cannot be sorted correctly?

"Instead of creating a calculated column, I would like to create a new table." - Sure, but why?