Import large table into Data Model, create Power Pivot with distinct alphanumerical data

Hi,

I have an Excel workbook that contains a large range of data in C7:IVE50 please see attached. The extent of the range will change dynamically vertically and horizontally depending on refreshing a VBA code that fetches and organizes data from an external source workbook. The range isn't formatted as an Excel table per se, just a normal range. The main header row is row C8:IVE8, but really the rows C9:IVE15 are also headers. The actual data is contained in C16:IVE50. (I had to distribute the header information in several rows, because creating concatenated strings of the data in AC9:IVE15 would result in headers with >100 characters, which is above the maximum limit for Data Models).

I am quite new to Power Pivot and have tried to import the data into a Data Model, but my laptop PC ran out of memory and couldn't perform the task.

Does anybody know how to piece this together so the data gets imported?

Also, I want to create Power Pivot tables from the data and will need to create measures and use the CONCATENEX function to be able to show and filter distinct alphanumerical data in the table, probably using some version of the DAX formulas below.

ListProductTypes = CONCATENATEX(DISTINCT(Product);Product[ProductType] ;", ")
ListProductTypes = CONCATENATEX(VALUES(Product);Product[ProductType] ;", ")

There are a lot of columns though, and I wonder if there is a way to create these measures en masse within the Data Model without having to create them one by one in the PivotTable Fields?

Best regards,
Marbleking