merge non-contiguous and contiguous range periods with the same attribute

[ivan00]

Hello,
excuse me for English Incorrect but I would ask help because I can not understand how to solve a problem related to a table that manages periods of different dates. I would like to reduce the repetition of lines where the attribute shown is the same.
I tried to help me with a calendar table but I can not understand the logic to apply (if I group the lines don't get the same the correct result).
Can you please help me?

Here below an example that can better explain my problem.


Thanks again for the support
Ivan

 

[MarcelBeug]

You can use the following Power Query code:

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    // Next row was added automatically and maually adjusted type datetime to date (2x):
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"StartDate", type date}, {"EndDate", type date}, {"Attribute", type text}}),
    
    // Add 2 index columns (from 0 and from 1) so the table can be merged with itself and have the current and prevous values in the same row
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Name", "StartDate", "EndDate", "Attribute"}, {"Previous.Name", "Previous.StartDate", "Previous.EndDate", "Previous.Attribute"}),

    // The merge step did change the row sort order, so sort back to the original sort:
    #"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}}),
    
    // Determine if the row should be kept in the final output:
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Keep", each [Name] <> [Previous.Name] or [Attribute] <>[Previous.Attribute] or [StartDate] <> Date.AddDays([Previous.EndDate], 1)),

    // Merge again with itself, but this time with the next values:
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom",{"Index.1"},#"Added Custom",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries1", "Next", {"Keep"}, {"Next.Keep"}),

    // Column New.EndDate will get only the latest EndDates (otherwise null):
    #"Added Custom1" = Table.AddColumn(#"Expanded Next", "New.EndDate", each if [Next.Keep] <> false then [EndDate] else null),

    // Now fill up so the correct EndDates will be in the rows that will be kept
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"New.EndDate"}),

    // Select only the rows that will be kept:
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Keep] = true)),

    // Final touches:
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "StartDate", "New.EndDate", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"New.EndDate", "EndDate"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"EndDate", type date}})
in
    #"Changed Type1"

 

[ivan00]

thank Marcel, really! :thumb:
It works, it's not clear if it was the result of your night's sleep or your morning breakfast (please tell me! ).
Anyway I tried your solution with several case studies (although I have not yet fully understood but I will study it) and it works. Perhaps the only pointing this out, we must be careful to order before the data for StartDate and Name.

Thanks again for the fast and accurate response (and now I'm going to study).

Ivan