Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@Anonymous
Group it:
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Group it:
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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"
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.