Страницы: 1 2 След.
RSS
Значение в столбце как исходные данные и конечный результат в Power Query
 
Добрый день, уважаемые форумчане.
Постарался изобразить в примере как выглядит исходный файл.
На Листе 1 это то как выводится таблица с результатами сборки и обработки нескольких файлов через PQ в исходном файле.
На Листе 2 для наглядности описываемого, создал таблицу через PQ и добавил в нее два столбца (Дата отправки, Дата получения), если внести любые изменения в эти столбцы и нажать (Правой кнопкой мыши по таблице-->Обновить), то введенные данные исчезнут.
На Листе 3 постарался изобразить какой результат хочется получить.
-------------------------
Вначале пробовал выводить данные таблицы PQ, а рядом в столбцы просто заполнять значения, но данные в таблице как я понял динамические, и добавление новой строки в один из исходных документов, сдвигает результат вниз и получается, что данные значений указанные в соседних столбцах уже относят не к тем данным.
Внести требуемые столбцы, в исходные таблицы к сожалению не представляется возможным.
-------------------------
Можно ли как то в PQ создать такой столбец в который можно было бы вносить данные и после команды обновить они не удалялись, а сохранялись.
Заранее спасибо.
 
Цитата
ymal_qeb написал:
и после команды обновить они не удалялись, а сохранялись.
Можно.  Приём называется Self Referencing Tables in Power Query
 
Андрей VG, спасибо, буду читать, разбираться.
 
Андрей VG, можете подсказать правильно ли я понял этапы статьи:
1 - Таблица как в примере Power Query, у меня на Листе2
(только что-бы все это работало нужно уникальное значение для слияния таблиц как я понял)
2 - выбрать таблицу на Листе2 на вкладке Power Query, выбрать из Таблицы или Диапазона.
3 - сохранить, создать только подключение
4 - вернуться в первоначальное подключение открыть его и выбрать объединить запросы.
5 - развернуть добавленный столбец. (сохранить)
-------
вроде как работает.
 
Похоже на то, вот только Индексы к источнику нужно добавлять не в запросе, индекс должен содержаться в самом источнике. В противном случае если в источнике вдруг поменяется порядок строк, то вы приджойните результаты не к тем строкам.
Если в источнике столбца с индексами или ID строк нет, то в качестве ключей для слияния таблиц можно использовать весь набор столбцов из обеих таблиц, кроме того, который заполняется ручками, но для этого вы должны быть на 100% уверены, что в исходнике никогда не будет полностью 2 одинаковых строки.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
вот только Индексы к источнику нужно добавлять не в запросе, индекс должен содержаться в самом источнике
у меня в исходном файле примерно такая схема объединения документов (из файлов берутся определенные столбцы-->сохраняется и создается только подключения, таких подключений около 4-5) потом через функционал PQ Добавление я объединяю эти подключения в одну таблицу как раз здесь можно и добавить индекс.
Цитата
PooHkrd написал:
в исходнике никогда не будет полностью 2 одинаковых строки
А инструмент проверит полностью строку на совпадение по каждому столбце?
------------------------------
PooHkrd, можете подсказать подсказать по нескольким дополнительно возникшим вопросам:
1 - я так понимаю результат PQ выводится в виде умной таблицы, но в умной таблице когда вводишь формулу он ее растягивает на весь диапазон столбца умной таблица (пример в столбцах I:J), а в результате PQ, это не работает, возможно ли как то добиться, что-бы было как в обычной умной таблице.
2 - если скрыть столбец и потом нажать обновить на таблице PQ (для примера скрыл столбец D), то он вновь появится, а можно ли как-то скрыть столбец в таблице PQ.
Изменено: ymal_qeb - 06.02.2019 15:16:34
 
Цитата
ymal_qeb написал:
здесь можно и добавить индекс.
Нет. это еще хуже. У вас тогда при изменении источников каждый раз будут съезжать индексы. Ключи для слияния должны быть либо в источниках (что самое лучшее, ибо запрос отработает быстро), либо вы делаете слияние по всем столбцам (а вот это на серьезном массиве может и затупить). Все остальные варианты с индексированием на лету это вообще не то.
Почитайте про ключевые поля в БД, Для чего они нужны, в какой момент СУБД их присваивает для каждой новой строки.
По протягиванию формул, вот же тема с первого листа ветки.
По скрытию столбцов, не понятно зачем такое, но главное не применяйте скрытие при помощи группировки столбцов, результат вас может поразить  ;) Надо поэкспериментировать со свойствами столбцов умной таблицы. Первое что приходит в голову уберите галку со свойства "Задать ширину столбца", может и поможет
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
У вас тогда при изменении источников каждый раз будут съезжать индексы.
Получается, что каждому источнику нужно добавить уникальный идентификатор, по типу индекс.1, индекс.2, индекс.3, индекс.4, индекс.4, иначе при объединении этих таблиц будет возникать за дублирование.
------
PooHkrd, ключевые поля в БД это, что по похожее на суррогатный ключ.
При скрытии никогда не пользовался группировкой столбцов, действительно Ваша идея была правильная отключив "Задать ширину столбца", а зачем постарался показать в примере (при использовании метода Self Referencing Tables in Power Query он заменяет формулы на значения, получается нужен дополнительный столбец, а также столбец А не нужен, а был нужен новый столбец с формулой в столбце B:B), получается, что столбцы A, H, I, не несут никакой информации и могут быть скрыты.
------
наверняка есть более правильные способы решения, но я пока про них не знаю.
Изменено: ymal_qeb - 06.02.2019 17:07:23
 
Цитата
ymal_qeb написал:
наверняка есть более правильные способы решения
Делать расчеты вместо формул Экселя формулами PQ. Правильнее некуда. Смешение этих способов заведет вас в тупик.
В общем и целом большинство задач, которые решаются формулами Экселя, без особых проблем реализуются в PQ.
Спрашивайте - отвечаем :)
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Спрашивайте - отвечаем
хорошо.
Спасибо Андрей VG, PooHkrd,
Буду пробовать, возникнут вопросы обращусь. :)  
 
PooHkrd, переосмыслил свой вчерашний файл.
В исходном файле по Вашему совету добавил, столбец индекса к исходным данным.
Цитата
PooHkrd написал:
Делать расчеты вместо формул Экселя формулами PQ.
Хотел спросит Вашего совета о том, что Вы думаете о том где применять формулы PQ:
На Листе 3, это мое представление конечного результата (за исключение хотелось бы убрать столбец Q:Q)
Столбцы - 1, 3, 4, 5, 16 - это исходные данные (полученные в исходном файле объединением нескольких файлов)
Столбцы - 6, 7, 8, 9, 13, 14 - это я так понимаю, что можно реализовать формулами PQ,
Столбцы - 10, 15 - здесь я так лучше использовать метод (Self Referencing Tables in Power Query), предложенный Андрей VG, проверил вроде умная таблица в которой выводится результат PQ, нормально воспринимает выпадающий список, (пример на Листе 2 Столбцы - K:K, P:P)
Столбцы - 2, 11, 12 - в данных столбцах я так понимаю, что можно использовать формулы только excel.
---------------------------
Возможно я что-то делал не так, попробовал, на Листе 5, через кнопки в PQ, добавил несколько условных столбцов, потом проделал метод (Self Referencing Tables in Power Query), но получается что добавляются копии столбцов они пустые, а в оригиналах нельзя менять данные.
---------------------------
Кажется понял свою ошибку на Листе 5, попробовал другой метод на Листе 6, то есть как я понял формулу надо писать в дублируемом запросе.
Можете подсказать верна ли моя идея?
Изменено: ymal_qeb - 07.02.2019 13:02:23
 
Все ваши формулы можно реализовать через PQ. Но только каждая формула это по сути отдельная тема  :D. Честно говоря, все ваши расчеты в них уже на форуме разбирались. Как прописать if then else, как ссылаться на конкретные ячейки таблиц и вот это вот все.
Если будете задавать вопрос на тему как мне реализовать такую-то формулу, то будьте добры приведите их в порядок, согласитесь, что смысл такой вот конструкции:
Код
=ЕСЛИ(
    ИЛИ(N10="";N10="#");
    ЕСЛИОШИБКА(
        ЕСЛИ(
            И(J10="Почта РФ";ИЛИ(НЕ(I10="");НЕ(I10="#")));
            ДАТАЗНАЧ(
                ТЕКСТ(I10+ИНДЕКС(Таблица4[Дней];ПОИСКПОЗ(ЕСЛИ(K10="";H10;K10);Таблица4[№];0);1)+11;
                "ДД.ММ.ГГГГ"));
            ":-:");
        "#");
    "")

разобрать значительно проще чем той колбасы, которой она является в вашем файле. Короче говоря чем в более удобном для посторонних людей виде вы предоставите данные, тем быстрее получите помощь. Желательно показать ваши попытки, что вы пробовали и в чем затруднения.
Self Referencing Tables in Power Query нужен для одной цели, там в статьей об этом прямо говорится - для накопления данных, и добавления к ним комментов вручную.
Вот горшок пустой, он предмет простой...
 
PooHkrd, на будущее запомню про порядок в формулах.
в приложенном файле исправленные формулы.
Код
    #"Условный столбец добавлен11" = Table.AddColumn(#"Условный столбец добавлен9", "Уточение-1-", each if [Дата документа] = "" then "" else if [Получили Мы] <> "" and [Получили Мы] <> "#" then "-" else "#")
Если не Вас не затруднит можете проверить правильно ли я написан аналог формулы:
Код
=ЕСЛИ($A5="";"";
  ЕСЛИ(
   И(НЕ(N5="");НЕ(N5="#"));
"-";
"#"))
Я так понимаю IF это аналог формулы ЕСЛИ в excel then это "то будет это", а else это "иначе"
Цитата
PooHkrd написал:
Все ваши формулы можно реализовать через PQ.
Правильно ли я Вас понимаю, что в PQ есть и аналоги формул ИНДЕКС, ПОИСКПОЗ?

Подскажите а выпадающие списки это тоже делается через PQ, или стандартным способом через Вкладку Данные --> Проверка данных?

Подскажите, как заменить значения в диапазоне G:Q где идут знаки #, :-:, на другие введенные вручную и после обновления получился тот-же эффект как Self Referencing Tables in Power Query, (для примера на Листе 7 в ячейке G2, набрать 07.12.2018, то после обновления данные снова станут #), это делается с помощью другого действия или это вообще не возможно.
Изменено: ymal_qeb - 07.02.2019 16:20:40
 
Про if все правильно поняли.
Цитата
ymal_qeb написал:
в PQ есть и аналоги формул ИНДЕКС, ПОИСКПОЗ?
Формул-аналогов нет, есть простые методы получить такие же результаты.
Про списки все как обычно.
Про знаки сегодня уже не успеваю глянуть.
Вот горшок пустой, он предмет простой...
 
PooHkrd, спасибо большое за помощь.
Цитата
PooHkrd написал:
Про знаки сегодня уже не успеваю глянуть.
Если Вас не затруднит и будет свободное время посмотрите.
Если не сложно, можете дать ссылок где можно почитать где реализовались методы как при формулах ИНДЕКС, ПОИСКПОЗ, ЕСЛИОШИБКА, ТЕКСТ, ДАТАЗНАЧ.
аналог оператора "или" это - or?
 
ymal_qeb, так не получится, не надо искать методы в PQ отпрыгивая от стандартных инструментов Экселя, ищите методы исходя из задач, так вы гораздо быстрее сможете понять логику работы не с ячейками, а с наборами данных: списками, записями, таблицами и функциями. Так гораздо проще искать решение. Лично у меня переход от мышления формулами Экселя на язык М занял около года, такая вот инерция мышления. Но я уже не мальчик, мне сложнее перестраиваться. Если вы молоды и горячи - дерзайте!
Изменено: PooHkrd - 07.02.2019 18:20:09
Вот горшок пустой, он предмет простой...
 
Цитата
ymal_qeb написал:
написан аналог формулы
я не нашел. наверное, надо так
Код
= Table.AddColumn(#"Условный столбец добавлен11", "Если", each if [Индекс] = null then null else if [Получили Мы] <> null and  [Получили Мы] <> "#" then "-" else "#")
 
PooHkrd, Не совсем до конца понял Ваше высказывание в посте 16.
Вы имеете ввиду, что надо отвязаться от классической логики Excelя, ссылка на ячейку и надо искать решения отталкиваясь от задачи.
Можете подсказать а как правильнее обозначить пустую ячейку: "" или null?
Спасибо, буду искать методы решение своей задачи.
 
Цитата
ymal_qeb написал:
надо искать решения отталкиваясь от задачи.
Да, иначе те шаблоны, которые сформировались в вашем мышлении не будут вас отпускать, мешая решению задачи.
Цитата
ymal_qeb написал:
Можете подсказать а как правильнее обозначить пустую ячейку: "" или null?
Это очень сильно зависит от конкретной задачи, универсального ответа нет.
Вот горшок пустой, он предмет простой...
 
PooHkrd, можете подсказать где ошибка? пытался формулу Excel
Код
=ЕСЛИ(
      И(B2="Почта РФ";НЕ(ИЛИ(A2="";A2="#")));1+1;
      ":-:")
перевести на PQ, но результат получается немного иной (строка 7)
Код
if [Способ передачи] = "Почта РФ" and [Отправка Оригинал] <> "" or [Отправка Оригинал] <> "#" then 1+1 else ":-:"
 
ymal_qeb, так надо?
Код
= if [Способ передачи] = "Почта РФ" and ( [Отправка Оригинал] <> "" or [Отправка Оригинал] <> "#" ) then 1+1 else ":-:"

Почитайте про порядок выполнения логических операций в выражениях. Не обязательно в PQ, а вообще в мат.логике.
Вот горшок пустой, он предмет простой...
 
PooHkrd, к сожалению не помогло, в строке 7 все также 2, а не :-:.
может нужно заменить <> на = и добавить оператор "not"?
----------
изменение <> на = + not помогло
Код
if [Способ передачи] = "Почта РФ" and not ( [Отправка Оригинал] = "" or [Отправка Оригинал] = "#" ) then 1+1 else ":-:"
Изменено: ymal_qeb - 08.02.2019 17:12:15
 
ymal_qeb, нет, просто or надо заменить на and .
З.Ы. Хотя можно и ваш вариант - тоже должен сработать
Изменено: PooHkrd - 08.02.2019 17:14:48
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
нет, просто or надо заменить на and
а можете объяснить почему?
В справочнике пишут and это "И", а or это "ИЛИ"
Изменено: ymal_qeb - 08.02.2019 17:15:30
 
Правильно пишут. Вот 2 равноценные формулы.
Код
if [Способ передачи] = "Почта РФ" and not ( [Отправка Оригинал] = "" or [Отправка Оригинал] = "#" ) then 1+1 else ":-:")

Код
if [Способ передачи] = "Почта РФ" and [Отправка Оригинал] <> "" and [Отправка Оригинал] <> "#" then 1+1 else ":-:")

Почему так - читайте мат. логику
Вот горшок пустой, он предмет простой...
 
интересно получается, надо будет прочитать про выполнение логических операций в мат. логике.
 
Цитата
ymal_qeb написал:
Подскажите, как заменить значения в диапазоне G:Q где идут знаки #, :-:, на другие введенные вручную и после обновления получился тот-же эффект как Self Referencing Tables in Power Query, (для примера на Листе 7 в ячейке G2, набрать 07.12.2018, то после обновления данные снова станут #), это делается с помощью другого действия или это вообще не возможно.
можно ли на Листе 2, как-то добиться того же эффекта как при использовании Self Referencing Tables, если столбец не присоединяется а уже существует с формулой PQ.
-----------
поэкспериментировал на Листе 3, вроде как удалось достичь желаемого эффекта (единственное, чтобы символы появились надо обновлять таблицу 2 раза).
-----------
PooHkrd, можете подсказать может есть более правильный способ, достичь такого эффекта, что ячейку можно редактировать и данные сохранялись после обновления, если в столбце присутствует формула PQ?
Изменено: ymal_qeb - 11.02.2019 13:08:12
 
ymal_qeb, вы уж не обессудьте, но разбираться в том что вы хотели, и что получилось из истории ветки у меня времени особо нет.
По сути вопроса:
Цитата
ymal_qeb написал:
ячейку можно редактировать и данные сохранялись, если в ячейке присутствует формула?
Этого можно добиться только одним способом, столбец с формулой должен быть добавлен к таблице, после того, как она будет сформирована вашим запросом. Т.е. данный столбец не должен видеть сам запрос, т.к. он всегда забирает из ячеек только их значения, и возвращает в таблице на выходе он только значения текстовые/числовые/даты, но только не формулы.
Еще раз, даже если запрос забирает данные из таблицы, на которую он же и ссылается, после чего возвращает этот столбец в итоговом результате, то в нем формул не будет. Если же вы заберете таблицу, и перед выгрузкой результата на лист удалите столбец с тем названием, в котором были формулы, то PQ выведет таблицу без него, после чего уже сам Эксель "вспомнит", что там был столбец с формулой, и его прилепит после выгрузки результата.
Как-то так.
Изменено: PooHkrd - 11.02.2019 13:20:41
Вот горшок пустой, он предмет простой...
 
спасибо, за пример я понял Вашу мысль
Цитата
PooHkrd написал:
столбец с формулой должен быть добавлен к таблице, после того, как она будет сформирована вашим запросом.
вот здесь в моем случае немного иная ситуация, (Вы ранее говорили "Делать расчеты вместо формул Экселя формулами PQ. Смешение этих способов заведет вас в тупик.", я прислушался к Вашему совету, в исходном файлы практически все формулы которые были на Excel заменил на PQ, в остальной части еще не разобрался)
Цитата
PooHkrd написал:
Т.е. данный столбец не должен видеть сам запрос, т.к. он всегда забирает из ячеек только их значения, и возвращает в таблице на выходе он только значения текстовые/числовые/даты, но только не формулы.
для меня в конечном варианте и не нужна формула в ячейке, а нужно как раз значение.
---------------------------------------
PooHkrd, не надо ворошить истории ветки, я повторю:
* - в первом посте при создании я хотел получить столбец который можно было редактировать и поле нажатия кнопки обновить значения сохранялись.
* - Андрей VG, предложил, вариант Self Referencing Tables in Power Query. (очень хороший способ, но мне немного не подошел, так как у меня уже был столбец с формулой на PQ, я хотел добавить ему возможности редактирования)
* - на Листе 1, у меня представлена таблица с исходными данными, я выбираю "Из таблицы или диапазона", добавляю условный столбец. (Лист 7)
* - как можно добиться чтобы этот столбец стал редактируемым (в тот момент когда я выгружаю запрос на лист, если заменить значение в этом столбце и нажать обновить, то оно сохранилось, а не заменялось [стандартными данными (данными из формулы)] из столбца)?
 
Тут только "СамиКСебеОбращающиеся" таблицы и помогут. Смотрите пример, если я все правильно понял.
Вот горшок пустой, он предмет простой...
Страницы: 1 2 След.
Наверх