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
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
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
PivotedColumnSorry for my inaccuracy. And thank you very much!
You're welcome Ekaterina.