Merge data by using conditions and filter

[MonaliC]

Hello, community

If someone can help me with this, it would be of great help. Thank you.
This is my sample(raw) data:

1 or 0	Instance	Sub Instance	Open	Close	Duration	Gap
0	Instance 1	Instance1.1	0:00:03	0:06:03	6 min
0	Instance 1	Instance1.2	0:06:33	0:16:33	10 min	30 sec
0	Instance 1	Instance1.3	0:16:43	0:30:43	14 min	10 sec
0	Instance 2	Instance 2.1	0:00:03	0:06:03	6 min
0	Instance 2	Instance 2.2	0:07:05	0:17:05	10 min	1 min 2 sec
0	Instance 2	Instance 2.3	0:18:15	0:28:15	10 min	1 min 10 sec

Condition - For column(1 or 0)=0, merge the data, if the total gap for the single same instance is less than a minute, if it's more than a minute, do not merge.

I would want the data to be shown in the way depicted below:

1 or 0	Instance 1	Open	Close	Duration	Gap
0	Instance 1.1	0:00:03	0:30:43	(30 min 40 sec) – 40 sec = 30 min
0	Instance 2.1	0:00:03	0:06:03	6 min
0	Instance 2.2	0:07:05	0:17:05	10 min	1 min 2 sec
0	Instance 2.3	0:18:15	0:28:15	10 min	1 min 10 sec

Thank you.

[v-cgao-msft]

Hi @MonaliC ,

Please refer the below steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY9LCoAwDESvIl0XmbT+6A08Q+lCxF3pRr2/FiFGEQVhNo8wj4z3CkqrPs3LkMapIAFUZkIJuBzL0ByQ1hhV0K8GI0qWDSRhj8WXR1YrBgsB+fjgMRf4NemmODe1DjU/JgHkYD5F56jOEXeNhF2UR4UN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1 or 0" = _t, Instance = _t, #"Sub Instance" = _t, Open = _t, Close = _t, Gap = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1 or 0", Int64.Type}, {"Instance", type text}, {"Sub Instance", type text}, {"Open", type duration}, {"Close", type duration}, {"Gap", type duration}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [1 or 0]=0),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Duration", each [Close]-[Open],type duration),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Instance"}, {{"Total", each List.Sum([Gap]), type nullable duration},{"Data", each _}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each [Total] < #duration(0, 0, 1, 0)),
    #"Aggregated Data" = Table.AggregateTableColumn(#"Filtered Rows1", "Data", {{"1 or 0", List.Max, "1 or 0"}, {"Sub Instance", List.Max, "Sub Instance"}, {"Open", List.Min, "Open"}, {"Close", List.Max, "Close"}, {"Gap", List.Sum, "Gap"}}),
    #"Added Custom1" = Table.AddColumn(#"Aggregated Data", "Duration", each [Close]-[Open]-[Gap]),
    #"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each [Total] >= #duration(0, 0, 1, 0)),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows2", "Data", {"1 or 0", "Sub Instance", "Open", "Close", "Gap", "Duration"}, {"1 or 0", "Sub Instance", "Open", "Close", "Gap", "Duration"}),
    #"Appended Query" = Table.Combine({#"Expanded Data", #"Aggregated Data"})
in
    #"Appended Query"

 output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[MonaliC]

Hi Gao @v-cgao-msft , thanks for replying. But I would want this inside power BI and not in power query because the raw data itself is a result of multiple tables and relationships.

[v-cgao-msft]

Hi @MonaliC ,

Please new a calculated column first:

TotalGap = CALCULATE(SUM('Table'[Gap]), 'Table'[1 or 0]=0, ALLEXCEPT('Table','Table'[Instance]))

Then create a new table:

Table2 = 
VAR __table_1 =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( 'Table', 'Table'[1 or 0] = 0 && 'Table'[TotalGap] <= TIME ( 0, 1, 0 ) ),
            'Table'[1 or 0],
            'Table'[Instance],
            "Sub Instance",
                CALCULATE (
                    MIN ( 'Table'[Sub Instance] ),
                    ALLEXCEPT ( 'Table', 'Table'[Instance] )
                ),
            "Open", CALCULATE ( MIN ( 'Table'[Open] ), ALLEXCEPT ( 'Table', 'Table'[Instance] ) ),
            "Close", CALCULATE ( MIN ( 'Table'[Close] ), ALLEXCEPT ( 'Table', 'Table'[Instance] ) ),
            "Gap", BLANK ()
        ),
        "Duration", [Close] - [Open]
    )
VAR __table_0 =
SELECTCOLUMNS(
    FILTER ( 'Table', 'Table'[1 or 0] = 0 && 'Table'[TotalGap] >= TIME ( 0, 1, 0 ) ),
    "1 or 0", [1 or 0],
    "Instance", [Instance],
    "Sub Instance", [Sub Instance],
    "Open",[Open],
    "Close",[Close],
    "Gap",[Gap],
    "Duration",[Duration]
)
RETURN
    UNION(__table_1,__table_0)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[MonaliC]

@v-cgao-msft I am afraid this is partly correct, result of "Close" column in Instance 1 one should be the last value of "Close" 0:30:43. You can refer my image from above.
Also can you add how I can get Gap calculated column basically difference between current row of "Open" and previous row of "Close".

[v-cgao-msft · Accepted solution]

Hi @MonaliC ,

I edited the code.

Table2 = 
VAR __table_1 =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( 'Table', 'Table'[1 or 0] = 0 && 'Table'[TotalGap] <= TIME ( 0, 1, 0 ) ),
            'Table'[1 or 0],
            'Table'[Instance],
            "Sub Instance",
                CALCULATE (
                    MIN ( 'Table'[Sub Instance] ),
                    ALLEXCEPT ( 'Table', 'Table'[Instance] )
                ),
            "Open", CALCULATE ( MIN ( 'Table'[Open] ), ALLEXCEPT ( 'Table', 'Table'[Instance] ) ),
            "Close", CALCULATE ( MAX ( 'Table'[Close] ), ALLEXCEPT ( 'Table', 'Table'[Instance] ) ),
            "Gap", BLANK ()
        ),
        "Duration", [Close] - [Open] -  CALCULATE(SUM('Table'[Gap]), 'Table'[1 or 0]=0, ALLEXCEPT('Table','Table'[Instance]))
    )
VAR __table_0 =
SELECTCOLUMNS(
    FILTER ( 'Table', 'Table'[1 or 0] = 0 && 'Table'[TotalGap] >= TIME ( 0, 1, 0 ) ),
    "1 or 0", [1 or 0],
    "Instance", [Instance],
    "Sub Instance", [Sub Instance],
    "Open",[Open],
    "Close",[Close],
    "Gap",[Gap],
    "Duration",[Duration]
)
RETURN
    UNION(__table_1,__table_0)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[MonaliC]

a

[bhanu_gautam]

@MonaliC 

Create a new column to calculate the total gap for each instance. You can use DAX (Data Analysis Expressions) to achieve this.

DAX
TotalGap =
VAR CurrentInstance = 'Table'[Instance]
RETURN
SUMX(
FILTER('Table', 'Table'[Instance] = CurrentInstance),
'Table'[Gap]
)

Create a new table or modify the existing table to merge rows based on the condition that the total gap is less than a minute.

DAX
MergedTable =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[Instance],
'Table'[Open],
'Table'[Close],
'Table'[Duration],
'Table'[Gap]
),
"MergedDuration",
IF(
[TotalGap] < TIME(0, 1, 0),
SUMX(
FILTER('Table', 'Table'[Instance] = EARLIER('Table'[Instance])),
'Table'[Duration]
),
'Table'[Duration]
)
)