Forum Discussion
Solved
Descuadre de errores
Buenos días, Estoy haciendo un Power BI que muestra el avance de la resolución de errores que tienen varias personas. Estos errores los actualizo mes a mes. Lo que quiero es que, si los errores a...
Hi again,
Result
You can delete these steps:
but in CombinedTables step, you should specify your tables.
Whole code with sample data:
let fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table => //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ let a = Table.Column(tbl, col), b = if shift = 0 or shift = null then a else if shift > 0 then List.Repeat({null}, shift) & List.RemoveLastN(a, shift) else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1), c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) & ( if newColName <> null then {newColName} else if shift = 0 then {col & "_Duplicate"} else if shift > 0 then {col & "_PrevValue"} else {col & "_NextValue"} )), d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any}) in d, TableFebruary = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTICYkMobWCob2Ckb2RgZKIUqxOt5JiTlFpUkg9VYACjURV55RenIsmiq4gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]), TableMarch = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTIEYgMgNgLRhvoGxvpGBkYmSrE60UqOOUmpRSX5UEUgbIKpyCu/OBXJGEM0FbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]), ChangedTypeFeb = Table.TransformColumnTypes(TableFebruary,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}), ChangedTypeMarch = Table.TransformColumnTypes(TableMarch,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}), CombinedTables = Table.Combine({ ChangedTypeFeb, ChangedTypeMarch }), UnpivotedOtherColumns = Table.UnpivotOtherColumns(CombinedTables, {"Nombre", "Fecha"}, "Error Type", "Value"), ChangedFechaFormat = Table.TransformColumns(UnpivotedOtherColumns, {{"Fecha", each Date.ToText(_, "yyyy-MM", "es-ES"), type text}}), GroupedRows = Table.Group(ChangedFechaFormat, {"Nombre"}, {{"All", each [ a = Table.Group(Table.Sort(_, {{"Fecha", Order.Ascending}}), {"Fecha"}, {{"Errors", each List.Sum([Value]), Int64.Type}}), //Sort + inner group b = fnShift(a, "Errors", 1, null, Int64.Type), //shift rows - add prev. value c = Table.FromColumns(Table.ToColumns(#table(null, List.Repeat({{[Nombre]{0}?}}, 2))) & Table.ToColumns(b), Value.Type(#table(type table[Nombre=text], {}) & b)) ][c], type table}}), CombinedAll = Table.Combine(GroupedRows[All]), FilteredRows = Table.SelectRows(CombinedAll, each ([Errors_PrevValue] <> null)), Ad_VsPrevMonth = Table.AddColumn(FilteredRows, "VS prev. Month", each Percentage.From([Errors] / [Errors_PrevValue]), Percentage.Type), FilteredRows2 = Table.SelectRows(Ad_VsPrevMonth, each [VS prev. Month] >= 1.1) in FilteredRows2
Buenos días,
Añado algunos datos de muestra:
Tabla de Febrero
| Nombre | Pinchazos | Motor | Chapa | Fecha |
| Juan | 2 | 1 | 2 | 01/02/2024 |
| Alberto | 1 | 0 | 1 | 01/02/2024 |
| Jose | 0 | 1 | 1 | 01/02/2024 |
Tabla de Marzo
| Nombre | Pinchazos | Motor | Chapa | Fecha |
| Juan | 1 | 0 | 2 | 01/03/2024 |
| Alberto | 1 | 1 | 4 | 01/03/2024 |
| Jose | 1 | 0 | 1 | 01/03/2024 |
Quiero sumar todos los errores de Juan, Alberto y Jose y comprobar si el mes siguiente ha aumentado un 10% los errores.
Por ejemplo: Cogemos a Juan, que la suma de los errores de Febrero es 5 (2+1+2), le suamos un 10% y la comparamos con la suma de errores de Marzo que es 3 (1+0+2). Es decir, 5X10/100=0,5--5+0,5=5,5>3. En este caso Juan no aparecería porque en Marzo ha bajado los errores.
Por otro lado, Alberto=Errores Febrero+10% es menor que Errores Marzo--1+0+1=2+2x10/100=2,2<(1+1+4)=2,2<6. Por lo que Alberto si debería aparecer.
Y con el caso de Jose=Errores Febrero+10%<Errores Marzo--(0+1+1)+2x10/100<1+0+1=2+0,2<2=2,2<2. Por lo que Jose no debería aparecer
Saludos y gracias
Hi again,
Result
You can delete these steps:
but in CombinedTables step, you should specify your tables.
Whole code with sample data:
let
fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
//v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
let
a = Table.Column(tbl, col),
b = if shift = 0 or shift = null then a else if shift > 0
then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),
c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
( if newColName <> null then {newColName} else
if shift = 0 then {col & "_Duplicate"} else
if shift > 0 then {col & "_PrevValue"}
else {col & "_NextValue"} )),
d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
in
d,
TableFebruary = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTICYkMobWCob2Ckb2RgZKIUqxOt5JiTlFpUkg9VYACjURV55RenIsmiq4gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]),
TableMarch = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTIEYgMgNgLRhvoGxvpGBkYmSrE60UqOOUmpRSX5UEUgbIKpyCu/OBXJGEM0FbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]),
ChangedTypeFeb = Table.TransformColumnTypes(TableFebruary,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}),
ChangedTypeMarch = Table.TransformColumnTypes(TableMarch,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}),
CombinedTables = Table.Combine({ ChangedTypeFeb, ChangedTypeMarch }),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(CombinedTables, {"Nombre", "Fecha"}, "Error Type", "Value"),
ChangedFechaFormat = Table.TransformColumns(UnpivotedOtherColumns, {{"Fecha", each Date.ToText(_, "yyyy-MM", "es-ES"), type text}}),
GroupedRows = Table.Group(ChangedFechaFormat, {"Nombre"}, {{"All", each
[ a = Table.Group(Table.Sort(_, {{"Fecha", Order.Ascending}}), {"Fecha"}, {{"Errors", each List.Sum([Value]), Int64.Type}}), //Sort + inner group
b = fnShift(a, "Errors", 1, null, Int64.Type), //shift rows - add prev. value
c = Table.FromColumns(Table.ToColumns(#table(null, List.Repeat({{[Nombre]{0}?}}, 2))) & Table.ToColumns(b), Value.Type(#table(type table[Nombre=text], {}) & b))
][c], type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
FilteredRows = Table.SelectRows(CombinedAll, each ([Errors_PrevValue] <> null)),
Ad_VsPrevMonth = Table.AddColumn(FilteredRows, "VS prev. Month", each Percentage.From([Errors] / [Errors_PrevValue]), Percentage.Type),
FilteredRows2 = Table.SelectRows(Ad_VsPrevMonth, each [VS prev. Month] >= 1.1)
in
FilteredRows2