Страницы: 1
RSS
Найти ошибки в одной таблице, сравнивая общие суммы по категориям с другой таблицей., Power Query и Power Pivot?
 
Помогите, пожалуйста.

Есть две   таблицы (в файле - желтая и синяя), между которыми связь многие ко   многим

Общая часть нормальных данных выделена желтым.
В синей таблице часть строк -   лишние. В примере такая выделена красным. По формальным признакам они от   нормальных не отличаются и выбрать их можно только вручную. В желтой таблице   - эталонные суммы по категории "Фрукт", с которыми надо сверяться, чтобы понять, какие категории содержат ошибочные строки.

Слева внизу сводная таблица,   сделанная через PP из обеих таблиц, в ней видно, что ошибка в категории  Яблоко. То есть найти, в каких "Фруктах" содержатся ошибки, можно только сравнивая таблицы через "Суммесли" по столбцу "Фрукт".

В итоге нужно  что-то вроде синей таблицы, но с целиком  выкинутой категорией  Яблоко (то есть состоящую только из точно правильных строк) для дальнейшей обработки и другую таблицу,   состоящую только из "Яблок" на ручной разбор, но это должно само собой получиться, если сделать п1.  
Изменено: Xel - 18.09.2018 13:17:35
 
можно такой логикой пойти:мерой высчитываем разницу сумм продавцов и меню, затем в сводной ставим фильтр по значению - равно "0" или не равно "0" (для второго случая)
Код
=SUM('Продавцы'[Стоимость])-SUM('Меню'[Стоимость])
 
Цитата
Vladimir Chebykin написал:
можно такой логикой пойти:мерой высчитываем разницу сумм продавцов и меню, затем в сводной ставим фильтр по значению - равно "0" или не равно "0" (для второго случая)
Нужен не просто список ошибочных категорий.

Нужна как максимум "Синяя" табличка разбитая на 2 таблицы - одна состоящая только из правильных категорий, другая - только из неправильных. То есть одну - содержащую только "яблочные" строки, другую - все остальные.

Ну или как минимум (почему-то мне кажется, что это проще, но не знаю, почему) - какой-то аналог сводной таблицы, которая у меня справа, но созданную только на основе правильных строк.
Изменено: Xel - 18.09.2018 13:51:26
 
тогда делаем те же вычисления, но не в мере, а вычисляемом столбце в модели данных PP, формула, правда посложнее будет:
Код
=CALCULATE(SUM('Продавцы'[Стоимость]);FILTER('Продавцы';EARLIEST('Фрукты'[Фрукт])=[Фрукт]))-CALCULATE(SUM('Меню'[Стоимость]);FILTER('Меню';EARLIEST('Фрукты'[Фрукт])=[Фрукт]))
 
Спасибо огромное!

/хотя вот вариант "посложнее" получить бы, с табличками, это вообще реально?
 
не знаю, какую таблицу в конечном итоге хотите получить (конечного образца такой таблицы в примере нет), но может такой вариант Вам поможет решить задачу
 
"Синюю таблицу, разбитую на две", то есть не сгруппированные строки, а исходные, первоначальная идея была получить это все в PQ, но там задачка, по-моему, упирается в возможности компьютера :(  Я делала формулу "Суммесли" по рецепту с этого форума, но запросу целой  ночи не хватило, чтобы отработать   :D

Сейчас можно настройками сделать подходящий вид, но обратно в PQ, насколько я понимаю, "загнать" не получится, надо продумать обработку данных перед отправкой в Power Pivot, чтобы не требовалось их после свода еще допилить, но это уже другая история.

Спасибо огромное!
Изменено: Xel - 18.09.2018 14:56:51
 
Цитата
Xel написал:
"Синюю таблицу, разбитую на две", то есть не сгруппированные строки, а исходные,
во вложенном примере #6 так вроде и сделал - выгрузил исходную таблицу "меню" и для наглядности справа добавил фильтр "правильно" и "неправильно". По поводу PQ: наверное можно сделать такое сравнение быстро и качественно, но я не умею...тут нужны ответы от "мастодонтов" вроде Максима Зеленского, Андрея VG или PooHkrd (и др.)
 
Цитата
Xel написал:
Я делала формулу "Суммесли" по рецепту с этого форума, но запросу целой  ночи не хватило, чтобы отработать    
Смотря что вы под Суммесли подразумеваете. В общем случае - это Table.Group.
По сути задачи. Выявить по каким фруктам расхождение по суммам - задача простая. Вычленить же какое значение по сумме - ошибочный по сравнению с эталонной суммой по фрукту - задача не тривиальная. По ищите по форуму задачи вида подбор заданной суммы по набору значений
 
Цитата
Андрей VG написал:
Выявить по каким фруктам расхождение по суммам - задача простая
Андрей VG, я правильно понимаю, что это просто сделать в PQ? ТС именно это просит, сделать сравнение на этапе загрузки данных PQ. я бы хотел увидеть такое решение для саморазвития... если Вас не затруднит.
 
Цитата
Vladimir Chebykin написал:
если Вас не затруднит.
Да не проблема, коллега.
Успехов.
 
Андрей VG, спасибо - завтра на работе поизучаю - дома Ex 2010.
 
Ура! Спасибо за оба способа.
Танцуют все.
Цитата
Андрей VG написал:
Смотря что вы под Суммесли подразумеваете. В общем случае - это Table.Group.
Это слишком просто для чайника :oops:  
До создания треда я  поиском  нашла тема тут на форуме
Оттуда. Как и предсказывал автор - работает страшно медленно  :D  :D
Код
= Table.AddColumn(Источник, "Сумма Продаж по товару", each List.Sum(Table.SelectRows(Источник, (r)=>r[Код]=_[Код] and r[Товар]=_[Товар])[Продажи]))
Изменено: Xel - 18.09.2018 20:57:05
 
Цитата
Vladimir Chebykin написал:
"мастодонтов"
В кавычках - потому что мы ещё не вымерли?  :D  
F1 творит чудеса
 
Цитата
Xel написал:
Оттуда. Как и предсказывал автор - работает страшно медленно
Вот не понимаю, а зачем брать оттуда самый медленный вариант с перебором строк, если третий вариант с Table.Group + Table.Join крайне резкий и понятный? Я им и пяток миллионов строк лопатил - время ожидания было вполне терпимым.
Цитата
Vladimir Chebykin написал:
"мастодонтов"
Ну вот, уже "мастодонтом" обозвали, и в один ряд поставили с такими корифеями, как Максим и Андрей. Владимир, спасибо, конечно, но я пока не заслужил.  :oops:
И еще, Андрей, вы в запросе использовали конструкцию
Код
= List.Sum(List.RemoveNulls([Стоимость]))

Это какая-то индейская хитрость? Зачем здесь удалять null?
Изменено: PooHkrd - 19.09.2018 15:24:14
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
индейская хитрость?
Скрытый текст
F1 творит чудеса
 
Цитата
PooHkrd написал:
Зачем здесь удалять null?
Привычка - случайных связей берегись, а то подхватишь что-нибудь :)
 
Я обычно так зачищаю, если хочу что-то выковырнуть List.Select'ом чтобы не мешались, а тут... Ну, привычка, так привычка  :)
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Вот не понимаю, а зачем брать оттуда самый медленный вариант с перебором строк, если третий вариант с Table.Group + Table.Join крайне резкий и понятный? Я им и пяток миллионов строк лопатил - время ожидания было вполне терпимым.
Мы, чайники, такие затейники /смайлик с фейспалмом/   Расширяем сознание о подсказки умных людей. Табл.груп я смотрела до того, как задать тут вопрос, не дошло, что это то самое-нужное. Не помню, почему  :cry:

У меня после переноса решения  в реальные таблицы получился интересный фокус. Это можно вылечить округлением до 2 знаков, насколько я понимаю.

Но у меня вопрос почему так получается - никаких сложных вычислений не делается, обычные формулы Экселя считают цифры равными. Почему PQ считает, что суммы не равны?
Большая часть цифр была обработана правильно, все неравные сумму отмечены. Но часть одинаковых тоже отмечена как "ложь".
Я сюда я скопировала в "Яблоки" и "Груши" пару примеров, которые неправильно были помечены как не равные. В этом маленьком файле PQ эти суммы бодро отмечает "ложью".
Изменено: Xel - 20.09.2018 21:14:40
 
Причина проста - гуглите "excel ieee 754"
Округление вам поможет, либо если вы точно знаете сколько максимально будет знаков после запятой в исходных данных (в вашем случае 2) , то можно перед вычислениями все умножить на 100 (в вашем случае), чтобы действия производились с целыми числами. Это снизит погрешность в вычислениях если их много промежуточных.
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх