Merge multiple rows into one with multiple conditions

Hi rupertbrown

I think that the basic idea of  what you need to do is add an index, then add a calculated column that enters null where the value is "Statistical Separation" or the index value, then Fill Up on that column.

Then group on that column and the Person, with the aggregates of Min of Admit Date, Max of Discharge Date, and Sum of Length of stay.

Here is the M code: 

let
    Source = Excel.Workbook(File.Contents("C:\testing1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Person", type text}, {"Admit Date", type any}, {"Discharge Date", type any}, {"Discharge To", type text}, {"Length of Stay", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "HomeIndex", each if [Discharge To] = "Statistical Separation" then null else [Index]),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Person"}, {{"SecondTable", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillUp ([SecondTable],{"HomeIndex"})),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Person", "Admit Date", "Discharge Date", "Discharge To", "Length of Stay", "Index", "HomeIndex"}, {"Person.1", "Admit Date", "Discharge Date", "Discharge To", "Length of Stay", "Index", "HomeIndex"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Expanded Custom1", "Custom", each if [HomeIndex] = null then [Index] else [HomeIndex]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"SecondTable", "Person.1", "Index", "HomeIndex"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"Custom", "Person"}, {{"Admit date", each List.Min([Admit Date]), type anynonnull}, {"Discharge Date", each List.Max([Discharge Date]), type anynonnull}, {"LOS", each List.Sum([Length of Stay]), type number}, {"S", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows1", "Count of Statistical Separation", each Table.RowCount( Table.SelectRows([S], each [Discharge To]="Statistical Separation"))),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"S", "Custom"})
in
    #"Removed Columns2"

Hi rupertbrown

This DAX calculated table might be close

Table = 

Var y = ADDCOLUMNS(
            'Table1',
            "Batch",1+
            CALCULATE(
                COUNTROWS('Table1'),
                FILTER(
                    'Table1',
                    'Table1'[Admit Date]<EARLIER('Table1'[Admit Date])
                    && 'Table1'[Person]=EARLIER('Table1'[Person])
                    && 'Table1'[Discharge To] = "Home"
                    )))
RETURN 
    SUMMARIZE(
            y,
            [Person],
            [Batch],
            "Admit Date" , MIN('Table1'[Admit Date]),
            "Discharge Date" , MAX('Table1'[Discharge Date]),
            "Length of Stay" ,INT(MAX('Table1'[Discharge Date]) - MIN('Table1'[Admit Date])),
            "Count Statistical Separations" , COUNT('Table1'[Admit Date])-1
            )