Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Фильтрация мусорных данных в виде парных записей в Power BI
 
#1
24.10.2019 09:44:18
Добрый день.
Есть выгруженные данные, по которым строится визуализация в 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, но останется парная положительная цифра, которая всё сломает. Как отфильтровать такие данные?
Изменено: Евгений - 29.10.2019 19:56:29
 
 
 
#2
24.10.2019 09:50:24
Даты у таких артефактов всегда одинаковые? Или могут быть разными? То что время разное я и так вижу.
Вот горшок пустой, он предмет простой...
 
 
 
#3
24.10.2019 09:56:10
Даты тоже могут быть разные, например 1 янв. 23:00 и 2 янв. 01:20
 
 
 
#4
24.10.2019 10:08:05
Какой максимальный промежуток времени может быть?
Идея в том, что можно выделить отрицательные значения в отдельную переменную, преобразовать отрицательные по модулю, а потом приджойнить их к таблице с  положительными значениями. И тут чем больше полей использовать как ключевые при джойне, тем точнее будет зачистка. Ведь теоретически может произойти такое что у одной и той же позиции с одинаковым положительным количеством может быть предшествующая строка с отрицательным, а может и не быть. Поэтому чем точнее локализовать проблему тем лучше.
Можно еще через List.Generate прогнать табличку. Но это чуть сложнее реализовать, и возможно работать будет медленнее.
И еще, отрицательные всегда предшествуют положительным?
Изменено: PooHkrd - 24.10.2019 10:10:24
Вот горшок пустой, он предмет простой...
 
 
 
#5
24.10.2019 10:43:26
Промежуток не более 4 часов.
Отрицательные всегда предшествуют положительным. При снятии показаний счётчиков иногда возникает ошибка в виде отрицательных значений, а при следующем снятии она исправляется прибавлением.
 
 
 
#6
27.10.2019 18:31:49
Правильно понимаю, что нормальные отрицательные значения отсутствуют в принципе? Т.е., если есть отрицательное значение, оно априори неверное и будет сторнировано следующим значением?
F1 творит чудеса
 
 
 
#7
29.10.2019 10:15:08
Совершенно верно, значение не может быть отрицательным.
 
 
 
#8
29.10.2019 10:20:56
Евгений, последнее уточнение, если мы находим отрицательную запись по счетчику, то мы точно знаем что следующая запись по нему это будет корректировка и между ними двумя не будет записей с корректными значениями?
В общем если ответ на мой вопрос да, то задача решается так:
Код
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
Изменено: PooHkrd - 29.10.2019 10:36:03
Вот горшок пустой, он предмет простой...
 
 
 
#9
29.10.2019 11:51:00
Ещё вариант:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    merge = Table.NestedJoin(Source,{"Серийный№"},Table.SelectRows(Source, each [количество] < 0),{"Серийный№"}, "temp"),
    replace = Table.ReplaceValue(merge,0,each [количество],(a,b,c)=>a{0}[количество]+c,{"temp"}),
    filter = Table.SelectRows(replace, each not ([количество] < 0 or [temp] = 0)),
    remove = Table.RemoveColumns(filter,{"temp"})
in
    remove
 
 
 
#10
29.10.2019 12:00:07
Aleksei_Zhigulin, ваш вариант при повторе кривых данных и их корректировке не корректно отрабатывает. Я тоже сначала хотел через джойн, но потом отказался от этой затеи, тут либо смещением, либо генератором списков. Если джойном, то нужно учитывать условие от ТС, что между кривыми показаниями и их корректировкой пройдет около 4 часов, т.е. как я это вижу надо городить доп.столбец для ключа и добавлять его в джойн.
Изменено: PooHkrd - 29.10.2019 12:03:40
Вот горшок пустой, он предмет простой...
 
 
 
#11
29.10.2019 12:22:10
PooHkrd, да, спасибо, тогда так:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    cols = {"Серийный№", "количество"},
    negative = Table.SelectColumns(Table.SelectRows(Source, each [количество] < 0), cols),
    transform = negative & Table.TransformColumns(negative, {"количество", each _ * -1}),
    merge = Table.NestedJoin(Source, cols, transform, cols, "temp",JoinKind.LeftAnti),
    remove = Table.RemoveColumns(merge,{"temp"})
in
    remove
 
 
 
#12
29.10.2019 12:31:11
Aleksei_Zhigulin,  ;)
Я такое уже пробовал. Тогда если за корректировкой попадется значение счетка с таким же, но уже корректным значением. То вы его тоже удалите. А так нельзя. Тут надо именно связывать пары значений и их удалять не задев всего остального.
Вот горшок пустой, он предмет простой...
 
 
 
#13
29.10.2019 12:52:58
PooHkrd, Вам не угодишь  :)  Согласен, не учёл такой ситуации. Но интуиция подсказывает, что должно быть достаточно простое решение  :)  
 
 
 
#14
29.10.2019 12:55:51
Aleksei_Zhigulin, дык у меня оно и не сложное. Сгруппировал, сместил, сравнил, лишнее убрал. Строчек кода много, но это ж не главное. Главное чтобы работало. Так, то перебор можно сделать и через List.Generate. Но я сейчас заморачиваться не хочу.
Вот горшок пустой, он предмет простой...
 
 
 
#15
29.10.2019 13:48:31
Попытка №3  :)
Код
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
Изменено: Aleksei_Zhigulin - 29.10.2019 13:53:18
 
 
 
#16
29.10.2019 14:05:58
Цитата
Aleksei_Zhigulin написал:
Попытка №3  
О, сразу и 4-й и 5-й аргумент группировки! Круто!. Но не работает.  :D
Пропускает вторую серию по одному и тому же счетчику.
Вот горшок пустой, он предмет простой...
 
 
 
#17
29.10.2019 19:46:25
Лады, №4  :)
Код
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 написал:
если мы находим отрицательную запись по счетчику, то мы точно знаем что следующая запись по нему это будет корректировка и между ними двумя не будет записей с корректными значениями?
Эта ситуация тоже обрабатывается, по идее.
 
 
 
Страницы: 1
Наверх