Открывайте файл xlsx как архив и ковыряйтесь в ресурсах xml структуры. Пример копания здесь. Были еще варианты копания в ресурсах от Андрея Лящук, можете поискать по его последним постам. Он не так часто к нам заглядывает.
Михаил Л написал: Моя задача делать так как им хочется видеть таблицу
Зануда моде он. Я, конечно, многого не знаю из организации вашей работы, но это в корне неверный подход.
Скрытый текст
Нужно людей учить работать правильно, а не подстраиваться под индивидуальных личностей и делать так как им вздумается. Это не эффективно в том числе и с точки зрения сопровождения костыльных решений. Их постоянно придется подстраивать и придумывать новые костыли для задач которые решаются за час-другой при правильно построенной системе. Обратите внимание, что самые устойчивые структуры в веках - это военные организации, знаете почему? Потому что сначала научись делать как сержант, а вот когда научишься делать лучше него, тогда уже лезь со своими советами и идеями как нужно правильно.
Потому что тогда буфферизация будет происходить только в рамках конструкции let ... in. Переменная Источник не будет видна для других запросов. Все запросы видят только последний шаг, который вы объявляете после in, в вашем случае это переменная СодержимоеКниги, но она то как раз в памяти не сохраняется. Что будет происходить при таком развитии событий? Допустим 5 запросов будут обращаться к запросу ИсхЛог, тогда каждый из них будет "видеть" шаг СодержимоеКниги, обращение к нему запустит данный запрос 5 раз, т.е. 5 раз будет происходить считывание с диска, потом 5 раз помещение в память. Если же этот шаг из запроса убрать, то считывание с диска и помещение в память произойдет только при первом обращении, все остальные разы будут брать данные из памяти. Все вышеописанное не касается случая когда каждый из пяти запросов будет вываливать свой результат в отдельную таблицу на листе или в модели данных. Тогда никакой буфер не поможет каждый запрос, который выводит какой-то результат рассчитывается отдельно ото всех остальных.
pinguindell написал: при этом в запросе нет никаких лишних шагов по преобразованию типа данных.
Как это нет, если именно на нем и ошибка? Пробуйте, только путь к папке поменяйте.
Код
let
Источник = Folder.Files("E:\456\Test"),
#"Другие удаленные столбцы" = Table.SelectColumns(Источник,{"Content", "Name"}),
#"Извлеченный текст между разделителями" = Table.TransformColumns(#"Другие удаленные столбцы", {{"Name", each Text.BetweenDelimiters(_, "_v1_", ".xlsx"), type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Извлеченный текст между разделителями", {{"Name", type date}}, "ar-DZ")
in
#"Changed Type with Locale"
andrei25776, права доступа к источникам запроса у всех юзверей одинаковы? Настройки уровня конфиденциальности в PQ у всех юзверей отключены? Сетевые пути к источникам запроса у всех юзверей внесены в зону доверенных?
mikhailov, По другому, чтобы буферизация сработала, её результат нужно присвоить какой-либо переменной, для вашего случая правильно будет так:
Код
let
Источник = Binary.Buffer(File.Contents("C:\Обработки\Источник_логистика\ИсходныеЛогистика.xlsx")),
СодержимоеКниги = Excel.Workbook( Источник, null, false )
in
СодержимоеКниги
При такой записи содержимое файла будет записано в оперативку и переменной Источник будет присвоена ссылка где оно там лежит, в результате при всех следующих обращениях к Источник файл будет считываться не с диска, а из памяти. Соответственно если нужно чтобы на запрос из памяти ссылались другие запросы, то Excel.Workbook( Источник, null, false ) нужно из этого запроса убрать, а все остальные запросы начинать с этьой конструкции Excel.Workbook( ИсхЛог, null, false )
Jack Famous, тёзка, здравия желаю. А если не прямой путь, а через одно место , например, при помощи вот этой приблуды вытащить таблички в текст, а потом из текста в Эксель? Вроде как эту утилитку можно через VBA запускать и массово конвертить в текст.
genosser написал: Вангую, что книга от такого умрет... в смысле памяти может не хватить, т.к. очень много умных таблиц.
Это не факт, все зависит от размера текстовых файлов. И PQ безо всяких макросов может раз в минуту все это дело обновлять. В общем нужно пробовать. Другой вопрос, сама по себе задача очень странная, зачем нужно вот это вот все. Лично мне сценарий использования такого инструмента не очевиден.
Виктория Голубева, возможно просто гугло-облако как-то подгаживает майкрософту, когда видит что за софтинка лезет за данными. Лично я для всех проектов связанных с облаками юзаю onedrive и никаких проблем с доступностью файлов не возникает от слова совсем. Чего вы так в этот гугл уперлись не знаю. Даже с точки зрения тех.поддержки гораздо проще при проблемах бомбить спецов MS на тему того что их же софтина не может скачать данные с их же облака. Если же в вашей ситуации обращаться в ТП, то товарищи из гугла и из МС будут вас друг к другу отфутболивать, ибо нафиг им этот гемор?
let
fx = (t)=> Text.Combine( Splitter.SplitTextByRepeatedLengths(10)(Text.Select(t,{"0".."9","."})),"; "),
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
Out = Table.TransformColumns(Source,{{"Поле 2", fx, type text}, {"Поле 3", fx, type text}})
in
Out
Данный подход будет работать только в том случае если даты будут прописываться в 10тизначном формате. Если будет что-то типа 1.1.2020, то не прокатит
Виктория Голубева, запрос покажите. К какому либо другому файлу в общем доступе пробовали запрос сделать? Т.е. такая проблема только с этим источником или со всеми? А еще рекомендую переустановить .NET. Часто помогает, возможно в нем что-то поломалось.
Maratej, а что за мегавычисления производятся что он должен их загружать? Запустите ВПР на 1кк строк из массива на 1кк строк и сразу же проц загрузится как надо. Правда память загрузится не очень. А если хотите прям загрузить делайте все вычисления в Power Query и Рower Рivot, первый отлично грузит проц, второй большой специалист по выжиранию памяти.
Вариант на PQ и сводных таблицах. В принципе можно и чисто на PQ, но лениво. Извините. З.Ы. А хотя там не так уж и сложно: вариант полностью на PQ без сводных:
Код
let
Source = Excel.CurrentWorkbook(){[Name="ИменованныйДиапазон"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "УровеньИерархии", each Text.Length( Text.Select([Column1],".") ), Int64.Type ),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Товар", each if [УровеньИерархии] = 1 then [Column1] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Вид товара", each if [УровеньИерархии] = 2 then [Column1] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Тип товара", each if [УровеньИерархии] = 3 then [Column1] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Column1", "УровеньИерархии"}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"Товар", "Вид товара"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Тип товара] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Товар", "Вид товара"}, {{"tab", each [Тип товара]}}),
#"Added Custom4" = Table.AddColumn(#"Grouped Rows", "tab1", each Table.FromColumns({{[Товар]},{[Вид товара]},[tab]},Table.ColumnNames(#"Filtered Rows"))),
tab1 = Table.Combine(#"Added Custom4"[tab1]),
#"Added Custom5" = Table.AddColumn(tab1, "Описание товара", each null)
in
#"Added Custom5"
Kanev75 написал: но как ее использовать в PP в Excel для объединения таблиц?
Никак. Если мне не изменяет мой склероз всю автоматизацию в Excel online MS планирует делать при помощи такой штуки как ExcelScript. Он точно умеет объединять таблички, и возможно умеет помещать результат в модель данных. Это уже надо в его объектную модель копать.
let
Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
Вывод = Table.TransformColumns(
Источник,
List.Transform(
Table.ColumnNames(Источник),
each {_,(t)=>Text.Split(Text.From(t),"; "), type list}) )
in
Вывод
PQ. Применение функций к определённому списку полей., В параметры функции Table.TransformColumnTypes необходимо передать список полей, а не перечислять поля.
buchlotnik, не мерил, но ранее то ли от Андрея VG, то ли от Зеленского Максима была инфо, что аккумулятор в каждом цикле просчитывает всю операцию целиком, причем с учетом ленивых вычислений на каждом следующем шаге происходит пересчет всех предыдущих. На маленьком примере типизация может происходить быстро, а вот когда типизация происходит после сбора гига текстовых источников и некоторых преобразований над этим объемом, то что там будет это большой вопрос.
PQ. Применение функций к определённому списку полей., В параметры функции Table.TransformColumnTypes необходимо передать список полей, а не перечислять поля.
Andrey_S, а зачем в цикле типизировать одну и ту же таблицу столько раз, пусть и по разным столбцам, если можно аккумулятором собрать список преобразований, а потом уже их применить один раз к таблице?
Накидал 2 варианта, 1-ый это слегка зачищенный от шелухи вариант Юрий Я (синяя табличка), второй это вариант при котором на выходе получается ровно такая же табличка как в условии задачи с учетом сортировки строк. Судя по всему именно такое решение и подразумевается в упражнении. А вообще это типовая задача, которая разбирается в книге автора сайта.
PQ. Применение функций к определённому списку полей., В параметры функции Table.TransformColumnTypes необходимо передать список полей, а не перечислять поля.
ArgentumTiger_7, если брать ваш пример то лучше все таки так, чтобы не было путаницы, а то вы столбец Вид затрат аж во все три аргумента напихали. Передали аккуратно аргументы, сделали собственную функцию риплейсер, и вперед:
Код
Table.ReplaceValue(тип, each [УПС], 0,(a,b,c)=> if b = 250 and a = "не определено" then "условия соблюдены" else a,{"Вид затрат"})
PooHkrd написал: Если нужно работать со строкой и в качестве условия использовать значения из других полей одной строки
Чувствуете разницу между обработкой полей одной записи и возможностью передачи в списка значений какого-то столбца в итератор от Table.TransformColumns. И что это даст? как вы будете сопоставлять какую по счету строку таблицы изменяет эта функция и какой элемент списка из переданного её столбца ставить в соответствие?