We're giving away 30 tickets for FREE! Share your story, your vision, or your hustle and tell us why YOU deserve a ticket.
Apply nowWin a FREE 3 Day Ticket to FabCon Vienna. Apply now
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 @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
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
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 @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.
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
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 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 @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
a
@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]
)
)
Proud to be a Super User! |
|
User | Count |
---|---|
63 | |
57 | |
51 | |
45 | |
34 |
User | Count |
---|---|
120 | |
81 | |
71 | |
48 | |
47 |