Forum Discussion
Transform data_Select start date and end date
Dear all,
Please your help to advise how can I convert below data.
Original data includes 2 columns: activities and date. I want to define the start date and end date of each activities.
Original data:
| Activities | Date |
| Plan | 1/2/2020 |
| Plan | 1/3/2020 |
| Plan | 1/4/2020 |
| Plan | 1/5/2020 |
| Plan | 1/6/2020 |
| Produce | 1/7/2020 |
| Produce | 1/8/2020 |
| Produce | 1/9/2020 |
| Produce | 1/10/2020 |
| Produce | 1/11/2020 |
| Delivery | 1/12/2020 |
| Delivery | 1/13/2020 |
| Delivery | 1/14/2020 |
| Delivery | 1/15/2020 |
| Delivery | 1/16/2020 |
| Receive | 1/17/2020 |
| Receive | 1/18/2020 |
| Receive | 1/19/2020 |
| Receive | 1/20/2020 |
What I expect:
| Activities | Start date | End date |
| Plan | 1/2/2020 | 1/6/2020 |
| Produce | 1/7/2020 | 1/11/2020 |
| Delivery | 1/12/2020 | 1/16/2020 |
| Receive | 1/17/2020 | 1/20/2020 |
Thanks for your support!
3 Replies
Anonymous - So put Activities in a table visualization and then put date in twice, once with a min (first) aggregation and then with a max (last) aggregation.
a non natural way to get the same result (I use the table.pivot function with a user defined aggregation function to get multiple aggregation result and collapsed the combine and split column function to only one expression) :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+xCoAgEIDhd3EO1MtK9x4gWsUh6oZACoSC3r5IIoe79fun33sxxGkTldASJChQIlSF1YQZwhrC2sLSvhwzvtzRbGl2NGvFuP69x7iemK4cgAs1FwwXGi4UxyPO+JTsHeOWcUc7fNPhBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Activities = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activities", type text}, {"Date", type date}},"en-US"), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Activities]), "Activities", "Date", (x)=>{List.Min(x),List.Max(x)}), #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"), #"Transposed Table" = Table.Transpose(#"Demoted Headers"), #"Combine and Split Column" = Table.SplitColumn(#"Transposed Table", "Column2", each Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)(Text.Combine(List.Transform(_,Text.From), ";")), {"Start", "End"}) in #"Combine and Split Column"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+xCoAgEIDhd3EO1MtK9x4gWsUh6oZACoSC3r5IIoe79fun33sxxGkTldASJChQIlSF1YQZwhrC2sLSvhwzvtzRbGl2NGvFuP69x7iemK4cgAs1FwwXGi4UxyPO+JTsHeOWcUc7fNPhBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Activities = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activities", type text}, {"Date", type date}},"en-US"), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Activities]), "Activities", "Date",(x)=>Text.From(List.Min(x))&";"&Text.From(List.Max(x))), #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"), #"Transposed Table" = Table.Transpose(#"Demoted Headers"), #"Split Column" = Table.SplitColumn(#"Transposed Table", "Column2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Start", "End"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column",{{"Start", type date}, {"End", type date}}) in #"Changed Type1"
