Please check if this makes sense to you:
SortedMap:
mapping
Load distinct
[RegPointID],
Concat(distinct [Active Substance],';',[RegPointID]&[Active Substance]) as [Sorted Active Substance(s)]
group by [RegPointID];
load * where len([Active Substance]);
Load
[RegPointID],
trim(SubField([Active Substance(s)],';')) as [Active Substance]
FROM [lib://DataFiles/All Drug in the World (3).xlsx]
(ooxml, embedded labels, table is [All Drug in the World]);
[All Drug in the World]:
LOAD
[RegPointID],
[Country Group(s)],
[Country],
[Product Name (English)],
[Regulatory Status],
[Marketing Status],
[Authorization Number],
[Active Substance(s)],
ApplyMap('SortedMap',[RegPointID]) as [Sorted Active Substance(s)],
[Excipient(s)],
[Active Strength(s)],
[Auth Pharmaceutical Form],
[Route of Administration],
[Marketing Authorisation Holder],
[Application Submitted Date],
Date([Approval Date] ) AS [Approval Date],
Date([Marketed Date] ) AS [Marketed Date],
[No Longer Marketed Date],
Date([End Of Life Date] ) AS [End Of Life Date],
[End of Life Reason],
[Renewal Submitted Date],
[Renewal Approved Date],
[Renewal Required],
[Renewal Due Date],
[Renewal Expiration Date],
[Medicinal Product Type],
[Authorisation Procedure],
[Procedure Number],
[Global Product Category],
[Local Product Category],
[Local Dispensing Class],
[Product Owner],
[Brand Name],
[Formula Number],
[Formula Reference Number]
FROM [lib://DataFiles/All Drug in the World (3).xlsx]
(ooxml, embedded labels, table is [All Drug in the World]);
