Loop load with joins and cell value assigned variable

[thrwmcmesing]

Hello Experts,

I am trying to do load multiple Excels and concatenate them as they each contain data from one specific company (they come in the same format).

In one sheet you have all the data and in a different sheet there is only cell  A1 filled  (with only the name of the Parent Company)

I need to join the first table with the value of the cell in A1 in the second sheet. 

Name of Company| CEO |Country| Sales| Parent Company

Nespresso | John Green| Antartica | 1 dollar |   Nestle

Nespresso | John Green| Spain | 2 dollar |   Nestle

Nespresso | John Green| UK | 3 dollar |   Nestle

I have no idea how to fill in the column values with the cell A1 (as when i load the data, it just comes as the column name)

I understand i have to use a loop in order to load all the Excels but the bit about the second sheet A1 cell ...I have no idea how  to solve. 

Could you please help me out with this?

[eliran · Accepted solution]

Hi,

You can load the value from that sheet, and then join it to the data in the 2nd tab for that specific excel.

Under field names - 'No field names' - in the excel sheet with only one value in A1 cell.

The end result should look something like this

LOAD
A as Company
FROM [lib://DataLib/Excel.xlsx]
(ooxml, no labels, table is [Company]);

JOIN

load [Name of Company],[CEO],[Country],[Sales]

FROM [lib://DataLib/Excel.xlsx]
(ooxml, embedded labels, table is [Data]);

The end result will attach all the data from A1 cell to all the rows in the data tab.

I hope it helps,

Eliran.

[thrwmcmesing]

Worked perfectly, thank you so much!

I looked everywhere for an aswer to this and couldnt find anything.