Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola
En los datos que quiero aplicar en la consulta de potencia . Dice cuántos consectuive cuenta de rojo. Necesita ayuda
UC ID | Fecha | Bandera general | Bandera Roja |
C1 | 31-Enero-20 | Rojo | 1 |
C1 | 29-Feb-20 | Verde | 0 |
C1 | 31-Mar-20 | Verde | 0 |
C1 | 30-Abr-20 | Verde | 0 |
C1 | 31-Mayo-20 | Rojo | 1 |
C1 | 30-Jun-20 | Verde | 0 |
C1 | 31-Jul-20 | Verde | 0 |
C1 | 31-Aug-20 | Verde | 0 |
C1 | 30-Sep-20 | Verde | 0 |
C1 | 31-Oct-20 | Rojo | 1 |
C1 | 30-Nov-20 | Rojo | 2 |
C1 | 31-Dic-20 | Rojo | 3 |
C2 | 31-Enero-20 | Rojo | 1 |
C2 | 29-Feb-20 | Rojo | 2 |
C2 | 31-Mar-20 | Rojo | 3 |
C2 | 30-Abr-20 | Verde | 0 |
C2 | 31-Mayo-20 | Verde | 0 |
C2 | 30-Jun-20 | Verde | 0 |
C2 | 31-Jul-20 | Rojo | 1 |
C2 | 31-Aug-20 | Rojo | 2 |
C2 | 30-Sep-20 | Rojo | 3 |
C2 | 31-Oct-20 | Rojo | 4 |
C2 | 30-Nov-20 | Rojo | 5 |
C2 | 31-Dic-20 | Rojo | 6 |
Solved! Go to Solution.
Lo siento @Sriku para la respuesta tardía, demasiado ocupado actualmente.
Puede utilizar el 5o elemento de la función Group en combinación con un índice anidado de este modo:
let
Source = Table.FromRows(
Json.Document( Binary.Decompress( Binary.FromText(
"fZDNCsIwEAbfJecGkq0KHouiEFBBjyWHWoMXiaUYoW9vpFbz4+4lDQyTfkxds5VkBSslV43lIPz9aC7+lEwXHwhLvjHnEW57Y6z/ih/27q7pcSx41RH4bQ/In72rnKVc5W4UrtyVGnYyHWUf2gc+bH9/hhAic23aEJYjBKo0JKWjZyHpnD+LV/66A47xztPmqXO2Oaycbw4a/0uRFJ5FZlJ4HplJ4QXT+gU=", BinaryEncoding.Base64 ), Compression.Deflate ) ,
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table[#"UC ID" = _t, Date = _t, #"Overall Flag" = _t, TargetValue = _t]
),
#"Grouped Rows" = Table.Group(
Source,
{"UC ID", "Overall Flag"},
{{"All", each Table.AddIndexColumn(_, "GeneratedResult", 1, 1)}},
GroupKind.Local,
(x, y) => Number.From(x <> y)
),
#"Expanded All" = Table.ExpandTableColumn(
#"Grouped Rows",
"All",
{"Date", "TargetValue", "GeneratedResult"},
{"Date", "TargetValue", "GeneratedResult"}
),
#"Added Custom" = Table.AddColumn(
#"Expanded All",
"FinalResult",
each if [Overall Flag] = "Red" then [GeneratedResult] else 0
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"GeneratedResult"})
in
#"Removed Columns"
Algunos antecedentes sobre cómo funciona esto:
5o elemento: https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe... y para el índice anidado: https://www.youtube.com/watch?v=-3KFZaYImEY
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF, ¿puede ayudar en este
¿Puede alguien por favor ayudar en este
Si no está en la consulta de energía. Cualquier otro método para resolver esto. Básicamente para cada "UC id" necesita contar cuántos ocurre consequetivos de rojo
Lo siento @Sriku para la respuesta tardía, demasiado ocupado actualmente.
Puede utilizar el 5o elemento de la función Group en combinación con un índice anidado de este modo:
let
Source = Table.FromRows(
Json.Document( Binary.Decompress( Binary.FromText(
"fZDNCsIwEAbfJecGkq0KHouiEFBBjyWHWoMXiaUYoW9vpFbz4+4lDQyTfkxds5VkBSslV43lIPz9aC7+lEwXHwhLvjHnEW57Y6z/ih/27q7pcSx41RH4bQ/In72rnKVc5W4UrtyVGnYyHWUf2gc+bH9/hhAic23aEJYjBKo0JKWjZyHpnD+LV/66A47xztPmqXO2Oaycbw4a/0uRFJ5FZlJ4HplJ4QXT+gU=", BinaryEncoding.Base64 ), Compression.Deflate ) ,
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table[#"UC ID" = _t, Date = _t, #"Overall Flag" = _t, TargetValue = _t]
),
#"Grouped Rows" = Table.Group(
Source,
{"UC ID", "Overall Flag"},
{{"All", each Table.AddIndexColumn(_, "GeneratedResult", 1, 1)}},
GroupKind.Local,
(x, y) => Number.From(x <> y)
),
#"Expanded All" = Table.ExpandTableColumn(
#"Grouped Rows",
"All",
{"Date", "TargetValue", "GeneratedResult"},
{"Date", "TargetValue", "GeneratedResult"}
),
#"Added Custom" = Table.AddColumn(
#"Expanded All",
"FinalResult",
each if [Overall Flag] = "Red" then [GeneratedResult] else 0
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"GeneratedResult"})
in
#"Removed Columns"
Algunos antecedentes sobre cómo funciona esto:
5o elemento: https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe... y para el índice anidado: https://www.youtube.com/watch?v=-3KFZaYImEY
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Gracias por su respuesta. ¿Puede compartir el trabajo de Powerbi. Necesidad de entender más ya que soy un estudiante
Sí, claro.
Pero también podría pegar el código en el editor avanzado de una nueva consulta y tener todo lo que hay.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Muchas gracias por compartir. Déjame tratar de comprobarlo.
Es otra tabla de consulta son relevantes para la consulta de tabla o puedo eliminar esas tablas de consulta
Puede eliminarlos. Significado para cargar una versión limpia.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |