Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
MonaliC
Helper II
Helper II

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 0Instance Sub InstanceOpenCloseDurationGap
0Instance 1Instance1.10:00:030:06:036 min 
0Instance 1Instance1.20:06:330:16:3310 min30 sec
0Instance 1Instance1.30:16:430:30:4314 min10 sec
0Instance 2Instance 2.10:00:03

0:06:03

 

6 min 
0Instance 2Instance 2.20:07:050:17:0510 min1 min 2 sec
0Instance 2Instance 2.30:18:150:28:1510 min1 min 10 sec

MonaliC_0-1736498997570.png

 

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 1OpenCloseDurationGap
0Instance 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 sec
0Instance 2.30:18:150:28:1510 min1 min 10 sec

MonaliC_1-1736499027219.png

 

Thank you.

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

vcgaomsft_0-1736817045913.png

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

View solution in original post

7 REPLIES 7
v-cgao-msft
Community Support
Community Support

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:

vcgaomsft_0-1736739191797.png

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

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.

Hi @MonaliC ,

 

Please new a calculated column first:

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

vcgaomsft_0-1736754725495.png

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)

vcgaomsft_1-1736754767052.png

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

@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".

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)

vcgaomsft_0-1736817045913.png

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

a

bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!