Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
DekkerNick
Frequent Visitor

Transform data in grouped periods

‎06-28-2024 05:00 AM

Hi all, 

I have the following data example: 

DekkerNick_0-1719575917559.png


As shown it has periods of consecutive dates which I want to group for the PowerBI Gantt Chart. So that in the Gantt Chart the grouped dates are visualized per person. In now the Gantt Chart needs a start and end date but how can I create a start and end date grouped by consecutive periods? 





Labels:
Message 1 of 6
935 Views
0
2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User
In response to DekkerNick

‎07-02-2024 03:08 PM 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdY9a8MwEAbgv2I8x2B9S2vbqVOgkAwhg0tFCRgT7P5/eunQYl2Hs14PBiP8vDrsQ77LpX0a5q/mmO95XNpDez41pzy/36aP2/SZJ1pp6NKx853utaX7t/PLY8W314MQJwCbHsCqC4UN8qoBawBroZrj2hq1oeZ6awHrmNVi6wEbABuhmlNh5f0cABsBm5iV96TqEawQrLGdqfKVtvKmpq0RbSBtuZa3p3KQ9pAOaOWq0Bt+FR7SAdKR6w2dmhCte0grSNNb02vt5N+bZhJEJ0TTVMK0kWuFaMIP636t/GjQ/9PnnJfmdZiWnwcL2hyHeRjHPP4dL+VAZitS3C4pfpeUwFJcRQofkWtS+KxckaL50FxXSzmKpqpa9kjhozylXL8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Naam = _t, Groep = _t, Aanwijzing = _t, Datum = _t, Waarde = _t, SumWeeknummer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type date}},"nl"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Naam", "Groep", "Aanwijzing", "Waarde", "Datum", "Index"}, {{"Rows", each _, type table [Naam=nullable text, Groep=nullable text, Aanwijzing=nullable text, Datum=nullable date, Waarde=nullable text, SumWeeknummer=nullable text, Index=number]}},GroupKind.Local,(x,y)=>Number.From(try y[Datum]<>Date.AddDays(#"Added Index"{y[Index]-1}[Datum],1) otherwise true))
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

 

Obligatory reference: @ImkeF https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

View solution in original post

Message 5 of 6
795 Views
Anonymous
Not applicable
In response to DekkerNick

‎07-03-2024 01:36 AM

Hi @DekkerNick 

 

Here is my method for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdY9a8MwEAbgv2I8x2B9S2vbqVOgkAwhg0tFCRgT7P5/eunQYl2Hs14PBiP8vDrsQ77LpX0a5q/mmO95XNpDez41pzy/36aP2/SZJ1pp6NKx853utaX7t/PLY8W314MQJwCbHsCqC4UN8qoBawBroZrj2hq1oeZ6awHrmNVi6wEbABuhmlNh5f0cABsBm5iV96TqEawQrLGdqfKVtvKmpq0RbSBtuZa3p3KQ9pAOaOWq0Bt+FR7SAdKR6w2dmhCte0grSNNb02vt5N+bZhJEJ0TTVMK0kWuFaMIP636t/GjQ/9PnnJfmdZiWnwcL2hyHeRjHPP4dL+VAZitS3C4pfpeUwFJcRQofkWtS+KxckaL50FxXSzmKpqpa9kjhozylXL8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Naam = _t, Groep = _t, Aanwijzing = _t, Datum = _t, Waarde = _t, SumWeeknummer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Naam", type text}, {"Groep", type text}, {"Aanwijzing", type text}, {"Datum", type date}, {"Waarde", type text}, {"SumWeeknummer", Int64.Type}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Naam", Order.Ascending}, {"Groep", Order.Ascending}, {"Datum", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Naam", "Groep", "Aanwijzing", "Waarde", "Index"}, #"Added Index1", {"Naam", "Groep", "Aanwijzing", "Waarde", "Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Datum"}, {"Datum.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each if [Datum.1] = Date.AddDays([Datum],1) then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End Date", each if [Custom] = 0 then [Datum] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"End Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Up",{{"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Naam", "Groep", "Waarde", "Aanwijzing", "End Date"}, {{"Start Date", each List.Min([Datum]), type nullable date}})
in
    #"Grouped Rows"

vjingzhanmsft_0-1719995557143.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

Transform data in grouped periods.pbix
Message 6 of 6
772 Views
0
5 REPLIES 5
DekkerNick
Frequent Visitor

‎07-02-2024 07:22 AM

@lbendlin & @Anonymous , thanks for the information. 

I want to use the PowerBI Gantt Chart dashboard. But I only have one date column. In the PowerBI Gantt Chart, you need a start and enddate. How can I achieve this? So that, the PowerBI Gantt Chart accept my data. 

SWD 2024[Naam]SWD 2024[Groep]SWD 2024[Aanwijzing]SWD 2024[Datum]SWD 2024[Waarde][SumWeeknummer]

Bart PepelsWV Verbindingen 28-6-2024SWD26
Bart PepelsWV Verbindingen 29-6-2024SWD26
Bart PepelsWV Verbindingen 30-6-2024SWD26
Bart PepelsWV Verbindingen 1-7-2024SWD27
Bart PepelsWV Verbindingen 2-7-2024SWD27
Bart PepelsWV Verbindingen 3-7-2024SWD27
Bart PepelsWV Verbindingen 4-7-2024SWD27
Bart PepelsWV Verbindingen 2-8-2024SWD31
Bart PepelsWV Verbindingen 3-8-2024SWD31
Bart PepelsWV Verbindingen 4-8-2024SWD31
Bart PepelsWV Verbindingen 5-8-2024SWD32
Bart PepelsWV Verbindingen 6-8-2024SWD32
Bart PepelsWV Verbindingen 7-8-2024SWD32
Bart PepelsWV Verbindingen 8-8-2024SWD32
Bart PepelsWV Verbindingen 6-9-2024SWD36
Bart PepelsWV Verbindingen 7-9-2024SWD36
Bart PepelsWV Verbindingen 8-9-2024SWD36
Bart PepelsWV Verbindingen 9-9-2024SWD37
Bart PepelsWV Verbindingen 10-9-2024SWD37
Bart PepelsWV Verbindingen 11-9-2024SWD37
Bart PepelsWV Verbindingen 12-9-2024SWD37
Bart PepelsWV Verbindingen 11-10-2024SWD41
Bart PepelsWV Verbindingen 12-10-2024SWD41
Bart PepelsWV Verbindingen 13-10-2024SWD41
Bart PepelsWV Verbindingen 14-10-2024SWD42
Bart PepelsWV Verbindingen 15-10-2024SWD42
Bart PepelsWV Verbindingen 16-10-2024SWD42
Bart PepelsWV Verbindingen 17-10-2024SWD42
Bart PepelsWV Verbindingen 15-11-2024SWD46
Bart PepelsWV Verbindingen 16-11-2024SWD46
Bart PepelsWV Verbindingen 17-11-2024SWD46
Bart PepelsWV Verbindingen 18-11-2024SWD47
Bart PepelsWV Verbindingen 19-11-2024SWD47
Bart PepelsWV Verbindingen 20-11-2024SWD47
Bart PepelsWV Verbindingen 21-11-2024SWD47
Bart PepelsWV Verbindingen 27-12-2024SWD52
Bart PepelsWV Verbindingen 28-12-2024SWD52
Bart PepelsWV Verbindingen 29-12-2024SWD52
Bart PepelsWV Verbindingen 30-12-2024SWD53
Bart PepelsWV Verbindingen 31-12-2024SWD53
Bart PepelsWV Verbindingen 1-1-2025SWD1
Bart PepelsWV Verbindingen 2-1-2025SWD1
Cees JansenWV Verbindingen Parallel 14-6-2024SWD24
Cees JansenWV Verbindingen Parallel 15-6-2024SWD24
Cees JansenWV Verbindingen Parallel 16-6-2024SWD24
Cees JansenWV Verbindingen Parallel 17-6-2024SWD25
Cees JansenWV Verbindingen Parallel 18-6-2024SWD25
Cees JansenWV Verbindingen Parallel 19-6-2024SWD25
Cees JansenWV Verbindingen Parallel 20-6-2024SWD25
Cees JansenWV Verbindingen Parallel 19-7-2024SWD29
Cees JansenWV Verbindingen Parallel 20-7-2024SWD29
Cees JansenWV Verbindingen Parallel 21-7-2024SWD29





Message 4 of 6
805 Views
0
Anonymous
Not applicable
In response to DekkerNick

‎07-03-2024 01:36 AM

Hi @DekkerNick 

 

Here is my method for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdY9a8MwEAbgv2I8x2B9S2vbqVOgkAwhg0tFCRgT7P5/eunQYl2Hs14PBiP8vDrsQ77LpX0a5q/mmO95XNpDez41pzy/36aP2/SZJ1pp6NKx853utaX7t/PLY8W314MQJwCbHsCqC4UN8qoBawBroZrj2hq1oeZ6awHrmNVi6wEbABuhmlNh5f0cABsBm5iV96TqEawQrLGdqfKVtvKmpq0RbSBtuZa3p3KQ9pAOaOWq0Bt+FR7SAdKR6w2dmhCte0grSNNb02vt5N+bZhJEJ0TTVMK0kWuFaMIP636t/GjQ/9PnnJfmdZiWnwcL2hyHeRjHPP4dL+VAZitS3C4pfpeUwFJcRQofkWtS+KxckaL50FxXSzmKpqpa9kjhozylXL8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Naam = _t, Groep = _t, Aanwijzing = _t, Datum = _t, Waarde = _t, SumWeeknummer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Naam", type text}, {"Groep", type text}, {"Aanwijzing", type text}, {"Datum", type date}, {"Waarde", type text}, {"SumWeeknummer", Int64.Type}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Naam", Order.Ascending}, {"Groep", Order.Ascending}, {"Datum", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Naam", "Groep", "Aanwijzing", "Waarde", "Index"}, #"Added Index1", {"Naam", "Groep", "Aanwijzing", "Waarde", "Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Datum"}, {"Datum.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each if [Datum.1] = Date.AddDays([Datum],1) then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End Date", each if [Custom] = 0 then [Datum] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"End Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Up",{{"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Naam", "Groep", "Waarde", "Aanwijzing", "End Date"}, {{"Start Date", each List.Min([Datum]), type nullable date}})
in
    #"Grouped Rows"

vjingzhanmsft_0-1719995557143.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Transform data in grouped periods.pbix
Message 6 of 6
773 Views
0
lbendlin
Super User
Super User
In response to DekkerNick

‎07-02-2024 03:08 PM 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdY9a8MwEAbgv2I8x2B9S2vbqVOgkAwhg0tFCRgT7P5/eunQYl2Hs14PBiP8vDrsQ77LpX0a5q/mmO95XNpDez41pzy/36aP2/SZJ1pp6NKx853utaX7t/PLY8W314MQJwCbHsCqC4UN8qoBawBroZrj2hq1oeZ6awHrmNVi6wEbABuhmlNh5f0cABsBm5iV96TqEawQrLGdqfKVtvKmpq0RbSBtuZa3p3KQ9pAOaOWq0Bt+FR7SAdKR6w2dmhCte0grSNNb02vt5N+bZhJEJ0TTVMK0kWuFaMIP636t/GjQ/9PnnJfmdZiWnwcL2hyHeRjHPP4dL+VAZitS3C4pfpeUwFJcRQofkWtS+KxckaL50FxXSzmKpqpa9kjhozylXL8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Naam = _t, Groep = _t, Aanwijzing = _t, Datum = _t, Waarde = _t, SumWeeknummer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Datum", type date}},"nl"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Naam", "Groep", "Aanwijzing", "Waarde", "Datum", "Index"}, {{"Rows", each _, type table [Naam=nullable text, Groep=nullable text, Aanwijzing=nullable text, Datum=nullable date, Waarde=nullable text, SumWeeknummer=nullable text, Index=number]}},GroupKind.Local,(x,y)=>Number.From(try y[Datum]<>Date.AddDays(#"Added Index"{y[Index]-1}[Datum],1) otherwise true))
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

 

Obligatory reference: @ImkeF https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

Message 5 of 6
796 Views
Anonymous
Not applicable

‎07-01-2024 08:12 PM

Hi @DekkerNick 

 

I found some similar threads and a blog. You may refer to them and try their solutions. 

Grouping by consecutive dates into date ranges - Microsoft Fabric Community

Group data by consecutive dates per client - Microsoft Fabric Community

Extract Start and End Dates with Power Query • My Online Training Hub

 

Best Regards,
Jing

Message 3 of 6
822 Views
lbendlin
Super User
Super User

‎06-30-2024 07:34 AM

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Message 2 of 6
867 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All