All possible combinations of values

[Purushothaman]

Hi Experts,

Below is Data:

Output:  To generate a table with all possible combinations of values from the original table. For example;

I have attached the excel file for your reference.

Anyone, Please help!!

Thank you!!

[PrashantSangle · Accepted solution]

Hi,

Load all column individually & then Join between them

try below

LOAD
Zone1
FROM [lib://data/AS.xlsx]
(ooxml, embedded labels, table is Data)
where not WildMatch(Zone1,'-') or not IsNull(Zone1)
;

Join

LOAD
Zone2
FROM [lib://data/AS.xlsx]
(ooxml, embedded labels, table is Data)
where not WildMatch(Zone2,'-') or not IsNull(Zone2)
;

Join

LOAD
Zone3
FROM [lib://data/AS.xlsx]
(ooxml, embedded labels, table is Data)
where not WildMatch(Zone3,'-') or not IsNull(Zone3)
;

Join

LOAD
Zone4
FROM [lib://data/AS.xlsx]
(ooxml, embedded labels, table is Data)
where not WildMatch(Zone4,'-') or not IsNull(Zone4)
;

Join

LOAD
Zone5
FROM [lib://data/AS.xlsx]
(ooxml, embedded labels, table is Data)
where not WildMatch(Zone5,'-') or not IsNull(Zone5)
;

Join

LOAD
Zone6
FROM [lib://data/AS.xlsx]
(ooxml, embedded labels, table is Data)
where not WildMatch(Zone6,'-') or not IsNull(Zone6)
;

Regards,

Prashant Sangle

[Purushothaman]

@PrashantSangle 
Thanks for your quick help and solution. I heard great about you!!

Cheers!!

[BISquad]

Hi, I think it will work having all the fields in one LEFT JOIN