Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#1
23.01.2019 12:32:08
Всем доброго времени суток. Подскажите пожалуйста, как в запросе Power Query одного файла заполнить столбец значением именованной ячейки из другого (внешнего) Excel-файла?

Где-то на просторах интернета находил такую конструкцию:
Код
= Table.AddColumn(#"ИмяПредыдущегоШага", "ИмяДобавляемогоСтолбца", each Excel.Workbook(File.Contents("Диск\Путь[/B][B]\Файл[/B][B].xlsx"), null, true){[Name="ИмяИменованнойЯчейки"]}[Data]{0}[Column1])

, но что-то как я не пытался, не работает эта конструкция :(.

При попытке выполнить эту команду PQ выдает ошибку:Expression.Error:
Цитата
Ключу не соответствует ни одна строка в таблице.
Сведения:
   Key=Record
   Table=Table

P.S. Версия Excel - 2016.
Изменено: GrayMagellan - 23.01.2019 12:43:51
 
 
 
#2
23.01.2019 12:37:58
Цитата
GrayMagellan написал:
не работает эта конструкция
а Вы проверяли, есть ли в открываемом файле указанная именованная ячейка? Приложите пару файлов: с запросом и с именованной ячейкой. Тогда можно будет точнее сказать, что не так.
Цитата
GrayMagellan написал:
Ключу не соответствует ни одна строка в таблице
Ну вот эта ошибка как раз и означает, что нет либо самой ячейки, либо столбца Column1 в ней :)
Кстати, надо так же убедиться, что именованная ячейка создана с областью действия "Книга".

P.S. Что-то мне подсказывает, что куда проще сначала запомнить значение этой ячейки, а потом уже each по всем строкам столбца....
Изменено: Дмитрий(The_Prist) Щербаков - 23.01.2019 12:40:25
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#3
23.01.2019 12:42:22
GrayMagellan,
Код
= Table.AddColumn(#"Changed Type", "cell", each Excel.Workbook(File.Contents("C:\folder\Книга1.xlsx"), null, true){[Item="cell",Kind="DefinedName"]}[Data]{0}[Column1])
 
 
 
#4
23.01.2019 12:43:10
И вам не хворать.
У меня вот так сработало:
Код
= Table.AddColumn(Источник, "Пользовательская", each Excel.Workbook(File.Contents("E:\Книга5.xlsx"), null, true){[Item="Name",Kind="DefinedName"]}[Data]{0}[Column1])

Найдите 10 отличий  ;)
Вот горшок пустой, он предмет простой...
 
 
 
#5
23.01.2019 15:11:31
Что-то у меня обе конструкции хвостов не работают :(. Что {[Item="ИмяИменованнойЯчейкиВоВнешнемExcelФайле",Kind="DefinedName"]}[Data]{0}[Column1]), что {[Name="ИмяИменованнойЯчейкиВоВнешнемExcelФайле"]}[Data]{0}[Column1]). Я думал может что с путями к внешнему файлу или имени напутал - все трижды перепроверил, все равно ту же ошибку выдает.

Имя именованной ячейки "DefaultC1" (к примеру) во внешнем файле проверил трижды - все правильно. Я по этому имени совершенно спокойно позиционируюсь на ячейке и получаю её значение (интерактивно).


Область действия именованной ячейки проверил - стоит "Книга" как рекомендовано.
 
 
 
#6
23.01.2019 15:13:49
В основном файле команду добавления пользовательского столбца написал по данным выше шаблонам:

= Table.AddColumn(#"Другие удаленные столбцы", "Фоновый C1", each Excel.Workbook(File.Contents("\\UNC...\04 - Дефолтный срез.xlsx"), null, true){[Item="DefaultC1",Kind="DefinedName"]}[Data]{0}[Column1])

Блин... Не понимаю, что ему надо! Ведь Эксель же видит получаемое из именованной ячейки значение "C1#[None]"! Чего он его не хочет подтягивать в другой файл по ссылке для заполнения столбца? :(

P.S. Сами исходные файлы, простите пожалуйста, не могу выложить - там реальные данные :(.
Изменено: GrayMagellan - 23.01.2019 15:16:39
 
 
 
#7
23.01.2019 15:24:51
Пробовали обратиться к этому имени из PowerQuery напрямую из самого файла с ним? Не было ошибок? Если нет - возможно дело в доступах к файлу, т.к. он на сетевом диске. Пробовали к нему просто подключиться из PowerQuery для получения любых данных, а не только именованной ячейки?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#8
23.01.2019 15:37:06
Цитата
Дмитрий(The_Prist) Щербаков написал:
Пробовали обратиться к этому имени из PowerQuery напрямую из самого файла с ним? Не было ошибок? Если нет - возможно дело в доступах к файлу, т.к. он на сетевом диске. Пробовали к нему просто подключиться из PowerQuery для получения любых данных, а не только именованной ячейки?
Сейчас буду пробовать все ваши рекомендации. Я сделал небольшую модельку (архив прилагаю) - там все прекрасно работает по обеим методам (Name и Item/Kind)! Не понимаю почему в модели работает, а в боевом файле - нет :(. Может правда дело в сетевом расположении файлов...
 
 
 
#9
23.01.2019 15:45:32
Ага, кажется я "поймал его за хвост"! Если именованная ячейка - простая, то все работает нормально. А если именованная ячейка установлена на ячейку, являющейся именованной таблицей-результатом другого запроса, то сбоит и идет ошибка. Константа, которую я хочу подтянуть из дополнительного файла в основной, сама является результатом запроса, возвращающего одну ячейку. Мне тогда нужно ссылаться не на именованную ячейку, а на ячейку внутри умной таблицы-результата запроса? Как это сделать?
 
 
 
#10
23.01.2019 16:01:40
Цитата
GrayMagellan написал:
Как это сделать?
Судя по вашему скрину, эту таблицу зовут _04___Дефолтный_срез_С1, а обратиться к ней можно также как вы и обращались изначально к именованному диапазону, только вместо [Column1] нужно указать название столбца этой таблицы.
Вот горшок пустой, он предмет простой...
 
 
 
#11
23.01.2019 16:04:51
Вот попробовал разные варианты обращения к константе, полученной в результате запроса к неким исходным данным. 4 варианта обращения - ни один не сработал :(.
 
 
 
#12
23.01.2019 16:06:19
Цитата
PooHkrd написал:
только вместо [Column1] нужно указать название столбца этой таблицы
Ок, сейчас попробую.
 
 
 
#13
23.01.2019 16:15:09
Пробую в Table.AddColumn использовать такую конструкцию:

= Excel.CurrentWorkbook(){[Item="тблКонстантаИзУмнойТаблицы",Kind="DefinedName"]}[Data]{0}["Константа в умной таблице"]

Excel говорит мне "Недопустимый идентификатор" :(.
 
 
 
#14
23.01.2019 16:15:56
Сейчас тестовый файлик выглядит так:
 
 
 
#15
23.01.2019 16:19:09
В общем, как я понимаю, моя проблема в том, что я из одного запроса PQ обращаюсь не к простой именованной ячейке, а к ячейке из результатов другого запроса PQ. И нужно как-то правильно адресоваться к результатам того, другого, запроса... Я-то думал, что если я ячейку в результате другого запроса объявлю именованной, что Эксель будет к ней обращаться по тем же правилам, что и к простой именованной.
Изменено: GrayMagellan - 23.01.2019 16:20:59
 
 
 
#16
23.01.2019 16:20:51
Цитата
GrayMagellan написал:
{[Item="тблКонстантаИзУмнойТаблицы",Kind="DefinedName"]}
т.к. это умная таблица, то не DefinedName, а Table. Это как минимум. Ну а в целом нечто вроде:
Код
=Excel.CurrentWorkbook(){[Item="_04___Дефолтный_срез_С1",Kind="Table"]}[Data]{0}["_04___Дефолтный_срез_С1"]
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#17
23.01.2019 16:31:48
Цитата
Дмитрий(The_Prist) Щербаков написал:
[Item="_04___Дефолтный_срез_С1",Kind="Table"]
А где можно прочитать об этих Item/Kind и их всех допустимых значениях (пересечениях значений) и концептуально понять что и как в них заполнять? А то я как слепой кролик тыкаюсь в них, пытаясь заполнить наобум :(.

P.S. Попытался использовать как вы сказали - не получилось. Та же ошибка :(.

Уважаемый Пух, а не могли бы вы по последнему файлу "Дополнительный" сказать мне команду адресации к результатам запроса константы?

И чем Name, который я видел в самой первой конструкции, отличается от Item/Kind?
Изменено: GrayMagellan - 23.01.2019 16:35:31
 
 
 
#18
23.01.2019 16:38:52
Если я просто из Экселя обращаюсь к умной таблице, содержащей константу-результат запроса, то мне достаточно написать:

=тблКонстантаИзУмнойТаблицы[Константа в умной таблице]
 
 
 
#19
23.01.2019 16:46:23
Конструкция:

= Excel.CurrentWorkbook(){[Item="тблКонстантаИзУмнойТаблицы",Kind="Table"]}[Data
]{0}[Константа в умной таблице]

не работает :(
 
 
 
#20
23.01.2019 16:49:20
Ознакомьтесь с вот этим приемом от хозяина сайта. В том числе посмотрите видео, там есть информация дополняющая текст. Там часть статьи и видео посвящены вашему вопросу. Если что-то будет не понятно продолжайте спрашивать.
Вот горшок пустой, он предмет простой...
 
 
 
#21
23.01.2019 17:35:52
Цитата
PooHkrd написал:
Ознакомьтесь с вот этим  приемом  от хозяина сайта
Ознакомился :). Спасибо большое за этот способ визуального построения пути к данным! Использовав прием, описанный в видео, получил наконец-то работающую конструкцию:
Код
= Table.AddColumn(#"Добавлен столбец заполненый константой из простой именованной ячейки", "Константа из умной таблицы, заполненной результатом запроса", each Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[Константа в умной таблице])


Непонятно, конечно, как Эксель получил эту конструкцию... Это работает, но остались вопросы:
1) Почему Name, а не Item/KInd?
2) Почему [Content], а не [Data]?
3) Почему Таблица1, а не тблКонстантаИзУмнойТаблицы?

Работающая моделька в этом файле:
Изменено: GrayMagellan - 23.01.2019 17:51:11
 
 
 
#22
23.01.2019 17:46:10
Я понял! Автоматом Эксель взял данные не из умной таблицы, содержащей результат запроса к исходной умной таблице, а прямо из исходной умной таблицы (сорри за "масло масляное"). Это немного не то, что мне нужно было. Если предположить, что исходная умная таблица содержит много данных, то результатом запроса к ней будет одно итоговое число. Именно его я хочу брать. Поменял немного автоматически полученный код, и наконец-то получил то, что нужно:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    #"Добавлен столбец заполненый константой из простой именованной ячейки" = Table.AddColumn(Источник, "Константа из простой именованной ячейки", each Excel.CurrentWorkbook(){[Name="КонстантаВПростойИменованнойЯчейке"]}[Content]{0}[Column1]),
    #"Добавлен столбец заполненый константой из исходной умной таблицы" = Table.AddColumn(#"Добавлен столбец заполненый константой из простой именованной ячейки", "Константа из исходной умной таблицы", each Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[Константа в умной таблице]),
    #"Добавлен столбец заполненый константой из умной таблицы заполненной результатом запроса" = Table.AddColumn(#"Добавлен столбец заполненый константой из исходной умной таблицы", "Константа из результата запроса к умной таблице", each Excel.CurrentWorkbook(){[Name="тблКонстантаИзУмнойТаблицы"]}[Content]{0}[Константа в умной таблице])
in
    #"Добавлен столбец заполненый константой из умной таблицы заполненной результатом запроса"
Изменено: GrayMagellan - 23.01.2019 17:46:53
 
 
 
#23
23.01.2019 17:48:39
Уфф... С локальной работой в рамках одной книги разобрался. Теперь буду делать то же самое, но с забором константы, полученной первым запросом в первом файле (дополнительный), во второй запрос, расположенный во втором файле (основном).
 
 
 
#24
25.01.2019 12:26:57
Блин... Все-таки мне интересно, в чем разница между

let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true){[Item="тблРезультатВВидеЗначения",Kind="Table"]}[Data]
in
   #"Источник"


и


let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true){[Name="тблРезультатВВидеЗначения"]}[Data]
in
   #"Источник"

Обе - работают! Зачем MS сделала дублирующиеся механизмы доступа к данным? И какой все же лучше и предпочтительней использовать? Интерактивный помощник как по методу из видео строит путь по первому варианту. Второй я уже просто из чистого любопытства попробовал, и не ожидал что он тоже сработает.
Изменено: GrayMagellan - 25.01.2019 12:29:27
 
 
 
#25
25.01.2019 12:31:51
GrayMagellan, добавьте в файл где имеется таблица с названием тблРезультатВВидеЗначения пустой лист с таким же названием  и посмотрите как после этого отработают оба варианта обращения к указанному имени.
Вот горшок пустой, он предмет простой...
 
 
 
#26
25.01.2019 12:37:49
Гм... Если делаю запрос такого вида (без слова [Data] в конце):

let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true){[Name="тблРезультатВВидеЗначения"]}
in
   #"Источник"

то PQ мне показывает такую картинку:


Т.е. в принципе там есть и Name, и Item/Kind. Я вижу оба, и свободно могу выбрать Name или Item/Kind, после чего обращаюсь к объекту Data через ключевое слово [Data]?
Изменено: GrayMagellan - 25.01.2019 12:38:34
 
 
 
#27
25.01.2019 12:38:04
Это не дублирующие механизмы, это по сути фильтрация таблицы, которую возвращает Excel.Workbook.

В случае Kind и Item это сделано потому что два разных объекта, имеющие разные типы в столбце Kind могут иметь одинаковое имя в столбце Item, а в столбце Name - они уникальные.

Посмотрите сами, добавив в текст запроса только это - Excel.Workbook()

Будет более наглядно.



P.S. Еще раз, вы обращаетесь не к объектам, вы по сути выбираете из таблицы, содержащей потроха экселя, нужный вам. Делаете это конструкцией, которая сначала возвращает строку таблицы (а строка таблицы имеет тип Record), соответствующую заданным значением одной (Name) или двух одновременно (Item, Kind) колонок.

После этого обращаетесь к полю записи (поле записи = имени столбца) - [Data], а там хранится ваша таблица.

Data - это не ключевое слово, а имя колонки :-)
Изменено: Alexey_Spb - 25.01.2019 12:43:36
 
 
 
#28
25.01.2019 12:43:15
Сделал так, как вы говорили:
let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true)
in
   #"Источник"

Получил такую картинку:
 
 
 
#29
25.01.2019 12:45:29
Ну вот содержимое вашего файла.

Таблицу из него можно получить кучей способов - например, через фильтрацию строк и столбцов :-) Попробуйте :-)

Вариант визуального редактора предлагает вам получить значение столбца Data по ключу - комбинации значений в столбцах Item и Kind, либо просто по Name. Как видите, это немного разные вещи.
Изменено: Alexey_Spb - 25.01.2019 12:45:41
 
 
 
#30
25.01.2019 12:46:13
Гм... Наверное, Item/Kind предпочтительней, т.к. имя типа "лист Добавляем значение!ExternalData_1" мне более понятно и я хотя бы понимаю где это и что это такое, а вот имя "ExternalData_1" непонятно откуда взялось...
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх