Filtered and Transpose the data

Question

Dear all,

As you can see in the sample file attached, I have several of columns, the first forth columns contain the customer's information and the rest columns are the daily orders. These orders are for weekly business lunch.

The weekly business lunches are for 5 working days from Sunday to Thursday.

For that weekly business lunch for each day like for example Sunday the customer has 5 options to chose from them the options are:-

1- Western meal.

2- Eastern meal.

3-Keto meal,

4-Low-fat meal.

5-Rice meal.

As you can see in the rows.

Note that some meals contains 1 or 2 this refer to this meal from which menu since we have 2 menus.

So each row is considered as one weekly business lunch for each customer.

What I'm looking for is that:-

1- I want the data of the period of  Sunday 1/8/2021 to 31/8/2021 only. So each row which contains data in these period keep it other rows get rid out from them.

2- I want to know how many meals of each package (Premium and Express).

Thanks in advance for your kind support.

Sample.xlsx

RolandSchubert · Accepted Answer

Hi @Feras95p ,

if I got you right, you want to select all data within a specific date range and count by package.

I think, the first step is to transpose date using the Transpose tool. The first four columns should be selected as key columns, all other columns (i.e. days) are transposed to rows.

The header (= date) can be used to create a [Date] field,using the Formula tool and a DateTimeParse function.

A Filter tool can be applied to the new [Date] field with the restriction you need (2021-08-01 - 2021-08-31).

A rows with [Value] = NULL (i.e. rows without order) can be removed using a second Filter tool.

Finally, a Summarize tool will return the number of meals, grouped by [Package].

I've attached a sample workflow, let me know if it works for you.

Best,

Roland