Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сборка таблиц из разных файлов Excel с помощью Power Query_(?), В замечательной статье http://www.planetaexcel.ru/techniques/12/2152/
 
#1
07.06.2017 18:10:48
Здравствуйте.
Пож-та, подскажите, как должен будет выглядить запрос, если имена листов загружаемых в Power Query xlsx-файлов (на которых расположены данные) разные?
Сейчас редактор выдаёт следующий синтаксис:
Код
(filepath)=>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    #07-06-17 11-58_Sheet" = Source{[Item="07-06-17 11-58",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"07-06-17 11-58_Sheet",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type any}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type text}, {"Column25", type any}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type text}, {"Column37", type any}, {"Column38", type any}, {"Column39", type text}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type text}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type text}, {"Column49", type text}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type text}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type any}, {"Column70", type text}, {"Column71", type text}, {"Column72", type any}, {"Column73", type text}, {"Column74", type text}})
in
    #"Changed Type"

Имя листа на котором находятся данные "07-06-17 11-58"
Заранее спасибо.
Изменено: AndyGrouve - 09.06.2017 14:32:28
 
 
 
#2
07.06.2017 18:40:35
Тут всё зависит от того, можете ли вы предварительно отобрать нужные листы по какому-то признаку. Если после первого шага вы можете оставить в списке листов только нужные (например, по принципу "название содержит пробел"), то дальше проще, выполняете на столбце Data разворачивание (CombineBinaries) , он вам сам создаст функцию, которая обрабатывает каждый отдельный лист.

Совет - отключите автоматическое распознавание типов данных в настройках текущего файла.
F1 творит чудеса
 
 
 
#3
07.06.2017 19:10:47
Спасибо за ваш ответ. Скажиите, не упростит ли задачу то, что в файлах всего ОДИН лист?  Но имя этого листа не предсказуемо. Подскажите, а где отключается автоматическое распознавание типа данных?
Изменено: AndyGrouve - 07.06.2017 19:12:38
 
 
 
#4
07.06.2017 22:35:02
Упростит, конечно.
Код
(filepath)=> Excel.Workbook(File.Contents(filepath), null, true){[Kind="Sheet"]}[Data]

Цитата
AndyGrouve написал:
а где отключается автоматическое распознавание типа данных?
Файл - Параметры и настройки - Параметры запроса - Текущая книга / Загрузка данных
Изменено: Максим Зеленский - 07.06.2017 22:36:20 (код поправил)
F1 творит чудеса
 
 
 
#5
07.06.2017 23:45:41
Огромное вам спасибо!  Попробую ваши рекомендации и отпишу результат. С уважением.
Изменено: AndyGrouve - 05.07.2017 12:47:35
 
 
 
#6
08.06.2017 11:18:37
Учтите, лист должен быть действительно только один. Если есть скрытые листы, то вот эту часть [Kind="Sheet"] нужно записать как [Kind="Sheet", Hidden=false]
F1 творит чудеса
 
 
 
#7
08.06.2017 12:07:10
Максим,
Вот текст запроса после правки :

Код
(filename)=>
let
    Source = Excel.Workbook(File.Contents(filename), null, true) {[Kind="Sheet"]}[Data],
    #"07-06-17 11-58_Sheet" = Source{[Item="07-06-17 11-58",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"07-06-17 11-58_Sheet",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type any}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type text}, {"Column25", type any}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type text}, {"Column37", type any}, {"Column38", type any}, {"Column39", type text}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type text}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type text}, {"Column49", type text}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type text}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type any}, {"Column70", type text}, {"Column71", type text}, {"Column72", type any}, {"Column73", type text}, {"Column74", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column4", "Column6", "Column17", "Column18", "Column19", "Column20", "Column21", "Column23", "Column28", "Column29", "Column30", "Column32", "Column33", "Column34", "Column35", "Column37", "Column38", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column49", "Column50", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column71", "Column73", "Column74"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Дата"}, {"Column3", "Канал"}, {"Column5", "Договор"}, {"Column7", "Класс товара"}, {"Column8", "Рекламодатель"}, {"Column9", "ID Бренда"}, {"Column10", "Бренд"}, {"Column11", "Заказ"}, {"Column12", "Медиаплан"}, {"Column13", "Ролик"}, {"Column14", "Хр-ж"}, {"Column15", "Стоимомть без НДС"}, {"Column16", "Стоимомть с НДС"}, {"Column22", "Программа"}, {"Column24", "Тип РМ"}, {"Column25", "№ блока"}, {"Column26", "Позиция спота"}, {"Column27", "Тип блока"}, {"Column31", "Факт.время"}, {"Column36", "Тип размещения"}, {"Column39", "Целевая группа"}, {"Column46", "ID спота"}, {"Column47", "ID блока"}, {"Column48", "Точка продаж"}, {"Column51", "ID ролика"}, {"Column70", "Фин.бренд"}, {"Column72", "ID спота ТНС"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",3),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Bottom Rows", "Дата", "Дата - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Дата", "Дата - Copy", "Канал", "Договор", "Класс товара", "Рекламодатель", "ID Бренда", "Бренд", "Заказ", "Медиаплан", "Ролик", "Хр-ж", "Стоимомть без НДС", "Стоимомть с НДС", "Программа", "Тип РМ", "№ блока", "Позиция спота", "Тип блока", "Факт.время", "Тип размещения", "Целевая группа", "ID спота", "ID блока", "Точка продаж", "ID ролика", "Фин.бренд", "ID спота ТНС"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Дата - Copy", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type1", {{"Дата - Copy", each Date.MonthName(_), type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Month Name",{{"Дата - Copy", "Месяц"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Дата", "Месяц", "Канал", "Рекламодатель", "Договор", "Медиаплан", "Бренд", "Фин.бренд", "Класс товара", "Заказ", "Ролик", "Хр-ж", "Стоимомть без НДС", "Стоимомть с НДС", "Программа", "Тип РМ", "№ блока", "Позиция спота", "Тип блока", "Факт.время", "Тип размещения", "Целевая группа", "ID спота", "ID блока", "Точка продаж", "ID Бренда", "ID ролика", "ID спота ТНС"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns1","пл","плав",Replacer.ReplaceText,{"Заказ"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","плавав","плав",Replacer.ReplaceText,{"Заказ"})
in
    #"Replaced Value1"

Подозреваю, что нужно в let-секции заменить строки 07-06-17 11-58_Sheet, они упоминаются в трёх местах.
Однако, заменяя эти строки на {[Kind="Sheet"]}[Data], получаю ошибку : "Token Identifier expected" в 4-ой строке запроса.

Не подскажите в чём дело?
Заранее спасибо!
 
 
 
#8
08.06.2017 12:41:55
Максим, разобрался. Спасибо вам большое за помощь !  :)
Вот такой текст функции  финально получился, который работает :
Код
(filename)=>
let
    Source = Excel.Workbook(File.Contents(filename), null, true) {[Kind="Sheet"]}[Data],
    #"MyData" = Excel.Workbook(File.Contents(filename), null, true) {[Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"MyData",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type any}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type text}, {"Column25", type any}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type text}, {"Column37", type any}, {"Column38", type any}, {"Column39", type text}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type text}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type text}, {"Column49", type text}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type text}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type any}, {"Column70", type text}, {"Column71", type text}, {"Column72", type any}, {"Column73", type text}, {"Column74", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column4", "Column6", "Column17", "Column18", "Column19", "Column20", "Column21", "Column23", "Column28", "Column29", "Column30", "Column32", "Column33", "Column34", "Column35", "Column37", "Column38", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column49", "Column50", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column71", "Column73", "Column74"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Дата"}, {"Column3", "Канал"}, {"Column5", "Договор"}, {"Column7", "Класс товара"}, {"Column8", "Рекламодатель"}, {"Column9", "ID Бренда"}, {"Column10", "Бренд"}, {"Column11", "Заказ"}, {"Column12", "Медиаплан"}, {"Column13", "Ролик"}, {"Column14", "Хр-ж"}, {"Column15", "Стоимомть без НДС"}, {"Column16", "Стоимомть с НДС"}, {"Column22", "Программа"}, {"Column24", "Тип РМ"}, {"Column25", "№ блока"}, {"Column26", "Позиция спота"}, {"Column27", "Тип блока"}, {"Column31", "Факт.время"}, {"Column36", "Тип размещения"}, {"Column39", "Целевая группа"}, {"Column46", "ID спота"}, {"Column47", "ID блока"}, {"Column48", "Точка продаж"}, {"Column51", "ID ролика"}, {"Column70", "Фин.бренд"}, {"Column72", "ID спота ТНС"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",3),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Bottom Rows", "Дата", "Дата - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Дата", "Дата - Copy", "Канал", "Договор", "Класс товара", "Рекламодатель", "ID Бренда", "Бренд", "Заказ", "Медиаплан", "Ролик", "Хр-ж", "Стоимомть без НДС", "Стоимомть с НДС", "Программа", "Тип РМ", "№ блока", "Позиция спота", "Тип блока", "Факт.время", "Тип размещения", "Целевая группа", "ID спота", "ID блока", "Точка продаж", "ID ролика", "Фин.бренд", "ID спота ТНС"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Дата - Copy", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type1", {{"Дата - Copy", each Date.MonthName(_), type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Month Name",{{"Дата - Copy", "Месяц"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Дата", "Месяц", "Канал", "Рекламодатель", "Договор", "Медиаплан", "Бренд", "Фин.бренд", "Класс товара", "Заказ", "Ролик", "Хр-ж", "Стоимомть без НДС", "Стоимомть с НДС", "Программа", "Тип РМ", "№ блока", "Позиция спота", "Тип блока", "Факт.время", "Тип размещения", "Целевая группа", "ID спота", "ID блока", "Точка продаж", "ID Бренда", "ID ролика", "ID спота ТНС"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns1","пл","плав",Replacer.ReplaceText,{"Заказ"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","плавав","плав",Replacer.ReplaceText,{"Заказ"})
in
    #"Replaced Value1"
 
 
 
#9
08.06.2017 18:27:14
AndyGrouve, 2 и 3 шаги убейте :)
F1 творит чудеса
 
 
 
#10
09.06.2017 14:31:38
Всё же оставлю с использовением функции, как у Николая в описании, cпасибо!
P.S. Максим, вы не сталкивались с Replace-ом в Power Query используя символы подстановки "*" и "?". Что-то не могу добиться результата....
 
 
 
#11
15.06.2017 12:09:22
Напрямую такие символы подстановки в PQ не работают.
F1 творит чудеса
 
 
 
#12
16.06.2017 17:47:13
Максим, про wildcard-символы понял, спасибо. Не подскажите : как изменить ситаксис запроса, чтобы файл "Book1.xlsx"  (см. код ниже)  открывался из той же папки откуда грузится файл с запросом PowerQuery?
В редакторе сейчас это выглядит следующим образом :
Код
    Source = Excel.Workbook(File.Contents("c:\WORK\2017\Exp-PowerQuery\NextQuery\Book1.xlsx"), null, true) {[Kind="Sheet"]}[Data],
    #"DSheet" = Excel.Workbook(File.Contents("c:\WORK\2017\Exp-PowerQuery\NextQuery\Book1.xlsx"), null, true) {[Kind="Sheet"]}[Data],
На данный момент получается, что для того, чтобы отлаженный файл-запроса работал на других  РС, то  и папка на этих РС должна  быть обязательно такой же (c:\WORK\2017\Exp-PowerQuery\NextQuery\)  как и на РС на котором этот запрос сейчас работает.
Можно ли этого избежать?
Заранее спасибо!
 
 
 
#13
16.06.2017 18:41:45
Относительный путь к данным PowerQuery
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#14
16.06.2017 19:52:42
Большое спасибо за ссылку и статью. То, что нужно !  :)  
Изменено: AndyGrouve - 16.06.2017 19:52:54
 
 
 
Страницы: 1
Наверх