Loading excel: multiple tables in one sheet + data cleaning

[nezuko_kamado]

The excel sheet has two major tables that separated after "Expired Budget"

How can I create two tables from the original page with adding 'Total' and 'Expiration' columns to the new tables?

Is it possible to separate tables  and clean the data at  Load editor? Then can you help me on script?

Or only possible at Data manager? If so,  how can I do it?

[Shalom]

Hello, 

You can use RecNo() in where clause after Load statement to select the rows you need. Please find the attached qvw file.

LOAD ID,
[Charge Object],
Date,
[Expiration date],
Budget,
Title,
Balance,
Total
FROM
[C:\Users\admin\Downloads\Sample.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE RecNo() > 0 AND RecNo() < 6;;

[ramchalla · Accepted solution]

Hi Nezuko,

As suggested by Shalom, you can use RecNo() function to load the data as individual tables.

please use the below script.

1st Table:

Budget:

Load *
where not WildMatch(ID, '*Total');

LOAD
If(IsNull(ID), Peek(ID,-1), ID) as ID,
"Charge Object",
"Date",
"Expiration date",
Budget,
Title,
Balance

FROM [lib://DataFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1)

Where RecNo() <= 7
;

Left Join (Budget)

Load ID,
Sum(Balance) as Total
Resident Budget
Group BY ID;

2nd Table:

ExpiredBudget:

Load *
where not WildMatch(ID, '*Total');

LOAD
If(IsNull(ID), Peek(ID,-1), ID) as ID,
// ID,
"Charge Object",
"Date",
"Expiration date",
Budget,
Title,
Balance

FROM [lib://DataFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1)

Where RecNo() >= 11
;

Left Join (ExpiredBudget)

Load ID,
Sum(Balance) as Total
Resident ExpiredBudget
Group BY ID;