Добрый день. Есть выгруженные данные, по которым строится визуализация в Power BI Например счётчик выпущенной с конвейера продукции, он не может быть отрицательным по определению.
В данных периодически встречается спонтанный мусор в виде парных записей, первая с рандомным отрицательным значением, вторая- то же число, но положительное (-100500 и 100500):
Дата/время
Серийный№
количество
24.08.2018 10:56:02
abc123
354
24.08.2018 11:05:25
asd789
421
24.08.2018 12:56:02
abc123
-100500
24.08.2018 13:24:58
zxc051
268
24.08.2018 14:03:13
abc123
100500
На итоговые суммы не влияет, т.к. при сложении эти 2 цифры дают 0, но визуализации в Power BI портятся, да и со сводными таблицами могут быть проблемы.
Можно в Power Query удалить всё что меньше 0, но останется парная положительная цифра, которая всё сломает. Как отфильтровать такие данные?
Какой максимальный промежуток времени может быть? Идея в том, что можно выделить отрицательные значения в отдельную переменную, преобразовать отрицательные по модулю, а потом приджойнить их к таблице с положительными значениями. И тут чем больше полей использовать как ключевые при джойне, тем точнее будет зачистка. Ведь теоретически может произойти такое что у одной и той же позиции с одинаковым положительным количеством может быть предшествующая строка с отрицательным, а может и не быть. Поэтому чем точнее локализовать проблему тем лучше. Можно еще через List.Generate прогнать табличку. Но это чуть сложнее реализовать, и возможно работать будет медленнее. И еще, отрицательные всегда предшествуют положительным?
Промежуток не более 4 часов. Отрицательные всегда предшествуют положительным. При снятии показаний счётчиков иногда возникает ошибка в виде отрицательных значений, а при следующем снятии она исправляется прибавлением.
Правильно понимаю, что нормальные отрицательные значения отсутствуют в принципе? Т.е., если есть отрицательное значение, оно априори неверное и будет сторнировано следующим значением?
Евгений, последнее уточнение, если мы находим отрицательную запись по счетчику, то мы точно знаем что следующая запись по нему это будет корректировка и между ними двумя не будет записей с корректными значениями? В общем если ответ на мой вопрос да, то задача решается так:
Код
let
fnTableAddOffsetColumn = ( tbl as table, clmn as text, dir as number ) =>
if dir = 0
then Table.FromColumns(
Table.ToColumns( tbl ) & Table.ToColumns( #table( {clmn}, {{null}} ) & Table.RemoveLastN( Table.SelectColumns( tbl, {clmn}), 1 ) ),
Table.ColumnNames( tbl ) & {"Пред."&clmn} )
else Table.FromColumns(
Table.ToColumns( tbl ) & Table.ToColumns( Table.RemoveFirstN( Table.SelectColumns( tbl, {clmn}), 1 ) & #table( {clmn}, {{null}} ) ),
Table.ColumnNames( tbl ) & {"След."&clmn} ),
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Дата/время", type datetime}, {"Серийный№", type text}, {"количество", Int64.Type}}),
GroupedRows = Table.Group(ChangedType, {"Серийный№"}, {{"таб", each Table.Sort( _,{{"Дата/время", Order.Ascending}}), type table}}),
AddedCustom = Table.AddColumn(GroupedRows, "доб", each fnTableAddOffsetColumn( [таб], "количество", 0 )),
Combine = Table.Combine( AddedCustom[доб] ),
FilteredRows1 = Table.SelectRows(Combine, each [количество] >= 0),
FilteredRows2 = Table.SelectRows(FilteredRows1, each [Пред.количество] >= 0 or [Пред.количество] = null),
RemovedColumns = Table.RemoveColumns(FilteredRows2,{"Пред.количество"})
in
RemovedColumns
Aleksei_Zhigulin, ваш вариант при повторе кривых данных и их корректировке не корректно отрабатывает. Я тоже сначала хотел через джойн, но потом отказался от этой затеи, тут либо смещением, либо генератором списков. Если джойном, то нужно учитывать условие от ТС, что между кривыми показаниями и их корректировкой пройдет около 4 часов, т.е. как я это вижу надо городить доп.столбец для ключа и добавлять его в джойн.
Aleksei_Zhigulin, Я такое уже пробовал. Тогда если за корректировкой попадется значение счетка с таким же, но уже корректным значением. То вы его тоже удалите. А так нельзя. Тут надо именно связывать пары значений и их удалять не задев всего остального.
Aleksei_Zhigulin, дык у меня оно и не сложное. Сгруппировал, сместил, сравнил, лишнее убрал. Строчек кода много, но это ж не главное. Главное чтобы работало. Так, то перебор можно сделать и через List.Generate. Но я сейчас заморачиваться не хочу.
let
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
sort = Table.Sort(Source,List.Transform({"Серийный№", "Дата/время"}, each {_,0})),
group = Table.Group(sort, {"Серийный№", "количество"}, {{"temp", each if List.Sum([количество]) = 0 then #table({},{}) else _}}, 0,
(a,b)=>Number.From(b[#"Серийный№"] <> a[#"Серийный№"] or b[количество] <> -a[количество])),
combine = Table.Combine(group[temp])
in
combine
let
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
sort = Table.Sort(Source,List.Transform({"Серийный№", "Дата/время"}, each {_,0})),
group = Table.Group(sort, {"Серийный№", "количество"}, {"temp", each [a = Table.RowCount(_),
b = List.Count(List.Select([количество], each _ < 0)),
c = if b = 0 then _ else Table.LastN(_, a-b-Number.From(a>b))][c]},
0, (x,y)=>Number.From(x[#"Серийный№"] <> y[#"Серийный№"] or x[количество] <> -y[количество])),
combine = Table.Combine(group[temp])
in
combine
Цитата
PooHkrd написал: если мы находим отрицательную запись по счетчику, то мы точно знаем что следующая запись по нему это будет корректировка и между ними двумя не будет записей с корректными значениями?