topic Re: Evaluating table data in new table in Power Query

Evaluating table data in new table

Ekaterina_ — Tue, 12 Mar 2024 14:17:05 GMT

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.

Re: Evaluating table data in new table

dufoq3 — Tue, 12 Mar 2024 15:25:19 GMT

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

Re: Evaluating table data in new table

Ekaterina_ — Tue, 12 Mar 2024 17:27:53 GMT

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:

Re: Evaluating table data in new table

dufoq3 — Tue, 12 Mar 2024 17:36:59 GMT

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

Re: Evaluating table data in new table

Ekaterina_ — Tue, 12 Mar 2024 18:03:54 GMT

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".

Re: Evaluating table data in new table

dufoq3 — Tue, 12 Mar 2024 18:30:13 GMT

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

Re: Evaluating table data in new table

Ekaterina_ — Tue, 12 Mar 2024 18:37:27 GMT

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

Re: Evaluating table data in new table

dufoq3 — Tue, 12 Mar 2024 18:48:33 GMT

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

Re: Evaluating table data in new table

Ekaterina_ — Tue, 12 Mar 2024 19:13:02 GMT

Sorry for my inaccuracy. And thank you very much!

Re: Evaluating table data in new table

dufoq3 — Tue, 12 Mar 2024 19:25:03 GMT

You're welcome Ekaterina.