Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Buenas tardes a todos, tengo unos datos de pruebas de velocidad, estas pueden ser exitosas o fallidas. necesito establecer grupos de pruebas fallidas consecutivas. esto con el fin de encontrar la fecha de inicio y la fecha fin de cada uno de los grupos. por el momento esta es mi data
Lo que espero como proximo paso es llegar a esto: (Crear la columna Grupo ID)
y como resultado final necesito esto:
que es producto del siguiente analisis:
Solved! Go to Solution.
Considere la tabla de ejemplo...
Puedes terminar con lo siguiente...
Usando el código...
let
Source =
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67CsAgDIXhV5HMgiaN160tdO4uvv9r1KlwzHAyffxkDOKgQaKoY+5JydN53eu+a0zTbyD94FkTCzKAw4ICQC2o8EOyoDlI5E1IjxESxQKGQrVAADSa8wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, Result = _t, Index = _t]),
#"Changed Type" =
Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Code", type text}, {"Result", type text}}),
#"Grouped Rows" =
Table.Group(#"Changed Type", {"Result"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Records", each _, type table [Date=nullable datetime, Code=nullable text, Result=nullable text]}},GroupKind.Local),
#"Group Failed" =
Table.AddIndexColumn(Table.SelectRows(#"Grouped Rows", each [Result] = "F" and [Count] <> 1), "Group", 1, 1),
#"Removed Columns" =
Table.RemoveColumns(#"Group Failed",{"Result", "Count"}),
#"Expanded Records" =
Table.ExpandTableColumn(#"Removed Columns", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}),
#"Non Consecutive" =
Table.SelectRows(#"Grouped Rows", each [Result] <> "F" or [Count] = 1),
#"Removed Columns1" =
Table.RemoveColumns(#"Non Consecutive",{"Result", "Count"}),
#"Expanded Records1" =
Table.ExpandTableColumn(#"Removed Columns1", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}),
#"Combine Tables" =
Table.Combine({#"Expanded Records1", #"Expanded Records"}),
#"Sorted Rows" =
Table.Sort(#"Combine Tables",{{"Index", Order.Ascending}})
in
#"Sorted Rows"
La clave es GroupKind.Local en el paso Table.Group.
Esperemos que esto ayude.
Considere la tabla de ejemplo...
Puedes terminar con lo siguiente...
Usando el código...
let
Source =
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67CsAgDIXhV5HMgiaN160tdO4uvv9r1KlwzHAyffxkDOKgQaKoY+5JydN53eu+a0zTbyD94FkTCzKAw4ICQC2o8EOyoDlI5E1IjxESxQKGQrVAADSa8wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Code = _t, Result = _t, Index = _t]),
#"Changed Type" =
Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Code", type text}, {"Result", type text}}),
#"Grouped Rows" =
Table.Group(#"Changed Type", {"Result"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Records", each _, type table [Date=nullable datetime, Code=nullable text, Result=nullable text]}},GroupKind.Local),
#"Group Failed" =
Table.AddIndexColumn(Table.SelectRows(#"Grouped Rows", each [Result] = "F" and [Count] <> 1), "Group", 1, 1),
#"Removed Columns" =
Table.RemoveColumns(#"Group Failed",{"Result", "Count"}),
#"Expanded Records" =
Table.ExpandTableColumn(#"Removed Columns", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}),
#"Non Consecutive" =
Table.SelectRows(#"Grouped Rows", each [Result] <> "F" or [Count] = 1),
#"Removed Columns1" =
Table.RemoveColumns(#"Non Consecutive",{"Result", "Count"}),
#"Expanded Records1" =
Table.ExpandTableColumn(#"Removed Columns1", "Records", {"Date", "Code", "Result", "Index"}, {"Date", "Code", "Result", "Index"}),
#"Combine Tables" =
Table.Combine({#"Expanded Records1", #"Expanded Records"}),
#"Sorted Rows" =
Table.Sort(#"Combine Tables",{{"Index", Order.Ascending}})
in
#"Sorted Rows"
La clave es GroupKind.Local en el paso Table.Group.
Esperemos que esto ayude.