Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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
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
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]))
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
@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)
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
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]
)
)
Proud to be a Super User! |
|