Load of table with the fields names being dynamic

[alec1982]

I have a table where the some of the headers are not the same. the table has following columns (Name, Category and then several columns that start with abc (for example abc12, abc34, abc56). I want to pull in the name, category and then the abc column with the highest number (in our sample here, it will be abc56).

any idea how to do that?

[fosuzuki]

Hi,

The data is in what kind of source? SQL, xls, in-memory table, txt, ...?

[alec1982]

excel

[fosuzuki · Accepted solution]

So I am assuming that the number of columns and their names can vary. Try this:

Data:
CrossTable(Type, Value, 2)
LOAD
*
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Max:
load distinct
Type,
replace(Type, 'abc', '') as T
Resident Data;

Inner Join(Max)
load max(T) AS T
Resident Max;

Inner Join(Data)
load Type
Resident Max;

[alec1982]

close to what I needed. I adjust it to address the order of the columns as this is another issue where the first and second fields are not always the same.