Выделите столбец. Правая кнопка мыши, тип изменения, используя локальные. И вот там найдите подходящий формат

Доброго всем дня!

Есть две таблицы в PQ. В таблице "products" столбцы город доставки и столбец с количеством коробок для каждого города. В таблице "trucks" столбцы с городом доставки, типом грузовиков, максимальным количеством коробок, вмещающихся в грузовик и стоимость доставки одним грузовиком. Необходимо, в таблице "products" посчитать минимальную стоимость доставки всей партии.

Объединил таблицу products с таблицей trucks. Раскрыл вложенную таблицу. Добавил столбец с формулой. Сделал группировку по столбцу "citi", с выводом минимального значения по столбцу стоимости.

Вот этот последний шаг (группировка) занимает очень много времени. Мне видится, что если делать вычисления внутри nested table и там же вычислять минимум, то времени на обработку потребуется меньше. Однако, я никак не могу понять, как осуществить все это.

Заранее спасибо за дельный совет!

Доброго дня!

Написал пользовательскую функцию ВПР. Но не до конца понимаю как ее допилить
1. Сейчас она использует один столбец из массива (clmn_base). Однако, есть случаи, когда нужно сделать ВПР по двум столбцам. Я так полагаю, их надо в коде объединять, чтобы не плодить временные столбцы. А вот сделать это не получается. Пробовал использовать Table.CombineColumns, но так и не разобрался с ней
2. В одном из случаев мне надо проВПРить курсы валют, а потом на них умножить/разделить имеющийся в таблице столбец "cost_rub". Опять же хотелось бы избежать вставки курса в таблицу, а в самой функции все перемножить. Но тут у меня совсем никаких идей нет

Спасибо!

Андрей VG,Простите, а что такое ключи?

Table.Buffer не раз встречал в чужих примерах, но так и не понял как ее применять. Т.е. я-то ее вставляю в код, но прироста скорости не ощущаю.

И еще пара вопросов, если можно.
1.Есть ли разница (в скорости), что использовать источником данных: эксель, csv или txt?
2.Например, есть у меня некий файл. Мне нужно в PQ: удалить столбцы. сделать замену значений, удалить строки, переименовать столбцы, сделать слияние и прочее, и прочее. PQ все равно в каком порядке это производить?

Андрей VG, спасибо еще раз! Проверил на реальных данных: сравнивал базу в 50 тыс строк с базой в 1,5 млн строк. Заняло всего 5 минут!!!

Андрей VG, Андрей, спасибо большое за ответ! Я именно такого и ждал: не готового решения, а информации для размышления. Хотя у вас все так подробно описано, что это практически готовое решение

Перечитал свой изначальный пост. Да, действительно, создается впечатление, что мне было просто лень самому что-то делать. Приношу свои извинения. На самом деле, это не так. Просто я почему-то изначально отверг возможность решения задачи через простые трансформации и связывания таблиц. Решил, что тут исключительно через пользовательские функции надо решать. До этого я решал такую задачу в обычном экселе через макросы: искал в массиве нужные айдишки и циклом выбирал айди с ближайшей датой. Вероятно поэтому и сам зациклился на пользовательской функции.

Еще раз спасибо! В прикрепленном файле решение задачи.

Доброго всем вечера!

Есть две таблицы: рабочая и подстановки. В обеих есть поля id и date. В обеих таблицах id не уникальны, т.к. для каждой id есть еще и дата, Таким образом. у одной id может быть несколько значений в поле value в зависимости от даты.

В рабочей таблице для каждой id также проставлены даты. Однако, они могут (и почти всегда) не соответствуют тому, что есть в таблице подстановки.

Задача. Для каждой id в рабочей таблице найти такую же в таблице подстановки так, чтобы даты были наиболее близки. И в столбец value проставить соответствующее значение.

Примечания:
1. Таблицу подстановки можно отсортировать по id, если это необходимо
2. В поле date всегда даны даты на первое число месяца
3. В рабочей таблице могут быть такие id, которых нет в таблице подстановки. В таком случае необходимо в поле value писать "No data"

Подскажите, пожалуйста, как решить такую задачу.

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

Пока все вычисления сходятся с исходными. Вероятно, что-то с настройками моего экселя или PQ (на работе 2013-й русский эксель и англ PQ, дома 2016-й русский)

Прошу прощения. В прикрепленном файле на листе "in" исходная таблица. На листе "out" выгрузка из PQ

Всем доброго вечера!

При выгрузке массива из Power Query в Excel появляются отрицательные нули (-0). Происходит это при следующем условии. В Power Query есть столбец "Затраты" с целыми положительными числами (в т.ч. нулями). Есть столбец "Курс" с числами (курс валюты). В новом столбце делим столбец "Затраты" на "Курс". При этом используется округление Number.Round(выражение,0). В случае, если в столбце "Затраты" стоял ноль, то на выходе получаем -0 (минус ноль). Если же в столбце "Затраты" стояло число >0 и при делении на курс оно давало 0,49 (или меньше), то при округлении получался обычный ноль.

При этом.
1. -0 появляется только при выгрузке в эксель. В самом Power Query он отображается как обычный ноль
2. Если не использовать округление, то -0 не возникает
3. Если в экселе сложить эти два отрицательных нуля, то будет опять же -0. Если сложить через формулу СУММ, то получится 0
4. Эта проблема возникает и в 2013-м, и в 2016-м экселе.

Спасибо!

Спасибо! Буду пробовать

Доброй ночи!

В таблице запроса есть некоторое количество одинаковых столбцов с немного отличающимися названиями. Например, "Продажи 1", Продажи 2", "3 Продажи" и т.п.. Т.е. в названии столбцов всегда есть слово "Продажи". Список таких столбцов всякий раз может быть разным. И все эти столбцы мне не нужны. Можно, конечно, вручную их выбрать и удалить. Но, возможно, есть какой-то более простой механизм удаление? Какой-нибудь RemoveIfContains?

Андрей, спасибо огромнейшее!

Я правильно понимаю, что GetLink - это пользовательская формула? А куда на панели жать, чтобы написать такую?

Всем доброго дня!

Осваиваю Power Query. Столкнулся с проблемой поиска значений по словарю. В прикрепленном файле табличка и словарь к ней. Правила поиска такие.

1. В столбце "А" есть некий набор имен. Если имен несколько то разделителем будет ;
2. На листе Vocabulary есть список всех имен. Но нас интересуют только девушки. Поэтому в финальной табличке оставляем только их
3. Бывает так, что одна и та же девушка дважды попала в ячейку. Дубли нам не нужны, оставляем только одну девушку (ячейка А11)
4. Бывает, что девушек в ячейке совсем нет. Тогда пишем "мужик"
5. Имя может быть не обособлено ; (как в ячейке А7 "Илья (Наташа дала контакт)"). Такую Наташу мы не учитываем.
6. Могут появляться новые имена (например, Анастасия в ячейке А12). Таких мы помечаем Unknown

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

Спасибо!

Максим, спасибо большое! Все работает

Максим, я попробовал так сделать, но что-то пошло не так
Потом в цикле у меня

Самый примитивный вариант это
А вот как мне объединить a_col & i, чтоб VBA понимал, что это a_col1 я не знаю

Доброго всем дня!
Есть несколько коллекций a_col1, a_col2, a_col3 и т.д. Все эти коллекции создаются из одной таблички. Просто из разных столбцов циклом в соответствующую коллекцию добавляются значения ячеек.
Потом идет цикл
for i=1 to 10
Тут что-то происходит. И с каждой итерацией (i) должна выбираться соответствующая коллекция a_col1, a_col2, a_col3 и т.д.
next i

Вот не пойму как сделать, чтоб VBA понимал, какую коллекцию надо вызывать.

Заранее спасибо!

Спасибо большое! Заработало!

Я пробовал работать прям в том же файле, который идет в нагрузку к надстройке, в качестве примера. Причем и в 2007-м и в 2013-м Офисе результат одинаков.

Доброго всем дня!
С сайта Майкрософт скачал fuzzy lookup. Однако, при работе постоянно выдает ошибку. Делает расчет десяти строк и выдает такую картинку. Что делать?

Хорошо, пусть так. Но как мне понять по каким из столбцов сортировать, чтобы получить минимальный размер файла?

Всем доброго дня!

Обратил внимание, что один и тот же файл с массивом данных весит по разному в зависимости от того, как отсортирован массив. Допустим в массиве 20 столбцов с текстовыми данными и 10 с числовыми. Текстовые значения внутри каждого столбца часто повторяются, цифровые почти нет. Если сортирую по какому-нибудь из текстовых столбцов, то файл становится меньше, если по числовому, то сильно больше.

Правильно ли я понимаю, чтобы добиться минимизации размера файла, необходимо найти столбец с минимальным количеством уникальных значений и отсортировать по нему?

Scripter, спасибо! Помогло.

P.S. Да, я знаю, что лучше не объединять ячейки, но тут другого выбора не было

Scripter, так не получится. Я знаю, что начало объединенной ячейки в "А3", но я не знаю, где конец.

Доброго всем дня!

Необходимо скопировать с одного листа на другой некоторое кол-во объединенных ячеек. Например, объединенные ячейки "A3:D3". Известен только адрес начала этой "ячейки": "A3".

Если использовать Sheets("Лист1").Range("A3").Copy Destination:=Sheets("Лист2").Range("A3"), то макрос скопирует все по кусочкам. Приходится заходить на "Лист1", выделять, копировать... Все это занимает много времени

В общем, помогите, пожалуйста, скопировать.

Владимир, спасибо большое!

Если использовать такой вариант =ИНДЕКС(C2:C7;МАКС(ЕСЛИ(A2:A7=F2;ЕСЛИ(B2:B7<=G2;СТРОКА(A2:A7)-1))))
то при  "G2"=01.02.2012 будет возвращаться значение "ф", а должно быть "ы"

Так дата в ячейке "G2" может быть произвольная. И не обязательно содержаться в столбце "B". Из столбца "B" должна быть выбрана ближайшая дата к дате в ячейке "G2"  

Спасибо! Но у меня возвращается "ф", а должно "ы"