How to handle data with self repeating columns with different name in rows ?

[shekhar_analyti]

Hi All ,

How to handle data with self repeating columns with different name in rows ?

Thanks & Regards

Shekar

[avinashelite]

What is the output your excepting ?

for the first column

PurgeChar(field_name,'0123456789') as New_field_name

[sunny_talwar]

May be like this

Table:

LOAD A,

    B,

    PurgeChar(A, '0123456789') as NewA,

    KeepChar(A, '0123456789') as SNo

FROM

[..\..\Downloads\self repeating.xlsx]

(ooxml, no labels, table is Sheet1);

FinalTable:

LOAD DISTINCT SNo

Resident Table;

FOR i = 1 to FieldValueCount('NewA')

  LET vField = FieldValue('NewA', $(i));

  Left Join (FinalTable)

  LOAD SNo,

  B as [$(vField)]

  Resident Table

  Where NewA = '$(vField)';

NEXT

DROP Table Table;

[shekhar_analyti]

Hi Sunny ,

I guess , age is not coming properly !

[sunny_talwar · Accepted solution]

It is a matter of fixing the format, underlying value isn't incorrect

[shekhar_analyti]

As always .. Take a bow !!