Evaluating table data in new table

[Ekaterina_]

Hello everyone,

I have some problems by generating a new table in Microsoft Excel:

Table 1 contains the column "Date of export", " Product Name", "Departement", "X" and "Status". So this table depends on exports which are everyday taken and out of this I want to create a table2 with columnn "Product" which summarizes the given data from table1 as follows:

Column "Product" shall display the entries from table1 "Product Name", which belong only to department B,  however I can't use a simpel VLOOKUP since eg the Product X occurs several time in table1 as the export is taken everyday, that means the product could be on the 11.03.2024 added to the table with status "In preperation", on the 12.03.2024 it is again added but bow with status "to be delieverd", on the 13.03.2024  it is added again still with the status "to be delievered", and on the 14.03.2024 it is again added in the table but with new status "delievered". Based on this I want product X to occur only one time in table 2.

The next step would be that the next column in table2 calculates how lang the product X stayed in status "in preperation", another columns calvulates how long the product X was in status"to be delieverd" and so one.

However, I would be glad if someone   could help me with at least one of these questions, since everything what I tried out didn't work 

Thank you in advance.

[dufoq3]

Hi @Ekaterina_, provide sample data as table so we can copy/paste and expected result (based on sample data).

[Ekaterina_]

Thank you for the hint, here is some Dummy data. Sorry for the formate, but I couldn't figure out how to do it in a bester way:

Date of export Product Name Department Status

07.03.2024 Shoes A In Preperation

07.03.2024 Book B In Preperation

07.03.2024 Earings C In Preperation

08.03.2024 Shoes A In Preperation

08.03.2024 Book B To be delievered

08.03.2024 Earings C In Preperation

08.03.2024 Dress A In Preperation

08.03.2024 Bracelet C In Preperation

09.03.2024 Shoes A In Preperation

09.03.2024 Book B To be delievered

09.03.2024 Earings C To be delievered

09.03.2024 Dress A In Preperation

09.03.2024 Bracelet C To be delievered

09.03.2024 T-shirt A In Preperation

10.03.2024 Shoes A To be delievered

10.03.2024 Book B Delievered

10.03.2024 Earings C To be delievered

10.03.2024 Dress A To be delievered

10.03.2024 Bracelet C To be delievered

10.03.2024 T-shirt A In Preperation

10.03.2024 Jacket A In Preperation

11.03.2024 Shoes A Delievered

11.03.2024 Book B Delievered

11.03.2024 Earings C Delievered

11.03.2024 Dress A To be delievered

11.03.2024 Bracelet C To be delievered

11.03.2024 T-shirt A To be delievered

11.03.2024 Jacket A In Preperation

It should look like this:

[dufoq3]

So we have input sample data. What about expected result (based on this data?)

[Ekaterina_]

The output should be this table 2 which counts for how many days the product was in the corresponding status:

Product Name In Preperation To be delievered

Shoes 2 1

Book 1 2

Earings 2 2

Dress 2 2

Bracelet 1 3

T-shirt 1

Jacket

 

since tshirt is still in status "to be delievered" there should be no value, same applies for jacket which is still in status "in preperation".

[dufoq3]

Do you want to group it by department or this column can be ignored?

[Ekaterina_]

Yes, I forgot to mention it. So, later I want to generate such a table for each department.

[dufoq3 · Accepted solution]

@Ekaterina_, for future be more precise with expected result. It should be BASED ON SAMPLE DATA!. You provided incorrect results for Jacket, Shoes and T-shirt!

Result (with ignoring Department, just to show you how it works)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZA9D4IwFEX/CumMpEUT6ajioJOJbIShwos0EGoK8fcLGyCvH0PzltObe0+eE3qM6D6KaXwgIXnWCvrxnsZ364KHhg9oMUjVkSJcsWelmuk4oFehZfeegi8InXiUSLZLZCp4QVBBK+ELGqp/2K9GqqF3rqFFCS0MhmjusZD7LOToQjtv28jxjfbwbNfXUg94PKOIk83sBT2zkhowBx8Lfu7DoYSDkcUHPyN3UTZgghmib22EuYljqDgTaVfGfJUxVJmdN0orfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of export" = _t, #"Product Name" = _t, Department = _t, Status = _t]),
    FilteredOutDelivered = Table.SelectRows(Source, each ([Status] <> "Delievered")),
    ChangedType = Table.TransformColumnTypes(FilteredOutDelivered,{{"Date of export", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Product Name", "Status"}, {{"Days", each Duration.TotalDays(List.Max([Date of export]) - List.Min([Date of export])) +1, Int64.Type}}),
    PivotedColumn = Table.Pivot(GroupedRows, List.Distinct(GroupedRows[Status]), "Status", "Days")
in
    PivotedColumn

Version with Department implemented:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZA9D4IwFEX/CumMpEUT6ajioJOJbIShwos0EGoK8fcLGyCvH0PzltObe0+eE3qM6D6KaXwgIXnWCvrxnsZ364KHhg9oMUjVkSJcsWelmuk4oFehZfeegi8InXiUSLZLZCp4QVBBK+ELGqp/2K9GqqF3rqFFCS0MhmjusZD7LOToQjtv28jxjfbwbNfXUg94PKOIk83sBT2zkhowBx8Lfu7DoYSDkcUHPyN3UTZgghmib22EuYljqDgTaVfGfJUxVJmdN0orfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of export" = _t, #"Product Name" = _t, Department = _t, Status = _t]),
    FilteredOutDelivered = Table.SelectRows(Source, each ([Status] <> "Delievered")),
    ChangedType = Table.TransformColumnTypes(FilteredOutDelivered,{{"Date of export", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Department", "Product Name", "Status"}, {{"Days", each Duration.TotalDays(List.Max([Date of export]) - List.Min([Date of export])) +1, Int64.Type}}),
    PivotedColumn = Table.Pivot(GroupedRows, List.Distinct(GroupedRows[Status]), "Status", "Days")
in
    PivotedColumn

[Ekaterina_]

Sorry for my inaccuracy. And thank you very much!

[dufoq3]

You're welcome Ekaterina.