Hello, communityIf someone can help me with this, it would be of great help. Thank you.This is my sample(raw) data:1 or 0Instance Sub InstanceOpenCloseDurationGap0Instance 1Instance1.10:00:030:06:036 min 0Instance 1Instance1.20:06:330:16:3310 min30 sec0Instance 1Instance1.30:16:430:30:4314 min10 sec0Instance 2Instance 2.10:00:030:06:03 6 min 0Instance 2Instance 2.20:07:050:17:0510 min1 min 2 sec0Instance 2Instance 2.30:18:150:28:1510 min1 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 0Instance 1OpenCloseDurationGap0Instance 1.10:00:030:30:43(30 min 40 sec) – 40 sec = 30 min 0Instance 2.10:00:030:06:036 min 0Instance 2.20:07:050:17:0510 min1 min 2 sec0Instance 2.30:18:150:28:1510 min1 min 10 sec Thank you.

Hi Anonymous , 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,GaoCommunity 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

Anonymous

Not applicable

1 year ago

Solved

Merge data by using conditions and filter

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.

Need Help

Anonymous

1 year ago

Hi Anonymous ,

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

7 Replies

bhanu_gautam
Super User
1 year ago
Anonymous

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]
)
)

Anonymous

Not applicable

1 year ago

Hi Anonymous ,

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

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

Anonymous

Not applicable

1 year ago

Hi Gao Anonymous , 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.

Anonymous

Not applicable

1 year ago

Hi Anonymous ,

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

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

demo.pbix34 KB

Forum Discussion

Merge data by using conditions and filter

7 Replies

Related content

Conditional Merge

MERGE QUERIES

Conditional Filtering

Merge columns based on condition

Async merge risk