Forum Discussion
Solved
Evaluating table data in new table
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 tabl...
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 PivotedColumnVersion 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
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".
Do you want to group it by department or this column can be ignored?
Yes, I forgot to mention it. So, later I want to generate such a table for each department.
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 PivotedColumnVersion 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 PivotedColumnSorry for my inaccuracy. And thank you very much!