Столкнулся со следующей задачей, только изучаю инструмент, прошу подсказать маршрут решения.
Есть папка с 70 файлами xlsx одинаковой структуры, но некоторые файлы имеют особенности, нужно собрать данные с листа "ФинМодель" из каждого файла в каскадную таблицу. Вложения: 1) Архив папки "Модель" - должна быть на рабочем столе, содержит перечисленные файлы(5) 2) Ожидаемый результат.xlsx - пример результирующей каскадной таблицы, которую нужно получить
При просмотре файлов в некоторых жёлтым выделил следующие особенности:
1) Название листа с ошибкой, не "ФинМодель", а "ФинМодль" (УТ553400.xlsx) - как сделать универсальную маску для листа или поиск нужного листа по какому-то общему признаку? 2) В строке 3, которую планировал забирать в качестве заголовка, могут отсутствовать данные (УТ000996.xlsx) 3) В столбце B с кодами, по которым планировал собирать данные, могут отсутствовать нужные коды, при этом в строке есть название и значения(УТ000252.xlsx) 4) В столбце B с кодами, по которым планировал собирать данные, нужные коды могут ошибочно дублироваться, при этом в строке корректное название (СЗ-0212.xlsx) 5) В столбце D с единицами измерения могут быть некорректные значения тыс.руб. - руб (УТ000117.xlsx)
AlienSx, Планета большая, кого только не встретишь) HUGO_ST, Посмотрите этот прием. У меня шаг f - это функция. Вот как эта функция обработает один файл
Код
let
Source = Folder.Files("C:\Users\user\Downloads\Модель\Модель"),
#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Ожидаемый результат")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Name], "Комментарий")),
#"Changed Type" = Table.TransformColumns(#"Filtered Rows1",{{"Content", Excel.Workbook}}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Changed Type", "Content", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded {0}", each [Kind] = "Sheet"),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each Text.Contains([Item], "Фин")),
Data = #"Filtered Rows3"{1}[Data],
#"Removed Top Rows" = Table.Skip(Data,2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", { "Показатель", "ед.изм"}, "Атрибут", "Значение"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Атрибут", type date}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Атрибут", null}}),
#"Filtered Rows4" = Table.SelectRows(#"Replaced Errors", each [Атрибут] <> null and [Атрибут] <> ""),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each [Значение] <> 0),
#"Inserted Year" = Table.AddColumn(#"Filtered Rows5", "Год", each Date.Year([Атрибут]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Название месяца", each Date.MonthName([Атрибут]), type text)
in
#"Inserted Month Name"
HUGO_ST написал: Пришёл на форму развивать навыки или он только для тех, у кого опыт нахамить?
Ссылка на похожую задачу и ее решение, судя по комментариям можно понять что к чему. Точно нахамили?.. или может все таки помогли, отправили изучить возможный вариант?
написал: Ссылка на похожую задачу и ее решение, судя по комментариям можно понять что к чему.Точно нахамили?.. или может все таки помогли, отправили изучить возможный вариант?
AlienSx нахамил, а Михаил Л очевидно "помог, отправил изучить возможный вариант".
HUGO_ST, в чем Вы увидели хамство от AlienSx? Вам дали ссылку на похожую тему. В чем проблема? 'Пришелец-прораб' - это, если можно так сказать, личная подпись AlienSx, которая присутствует во всех его сообщениях. К Вам лично она не имеет отношения. Такая подпись есть у многих пользователях форума. У меня тоже) Вы можете и себе придумать что нибудь)
Согласие есть продукт при полном непротивлении сторон
Ааа, всё, понял ... ))) Зайдёт вот такой новичок и не разберётся. Всем 'мир'. AlienSx, отдельные извинения и спасибо за ссылку, просто не разобрался что есть такое "подпись".
неа. Задача не решена и наполовину, а именно: - возможное отсутствие каких-то наименований (колонок) в файле - наличие subtotals в файлах, которые надо бы убрать (пункты 1.1 и 1.2, в частности) - "игры" с кодами (ошибочное отсутствие и/или дублирование) придает пикантности этапу борьбы с subtotals - "руб" и "тыс руб" не обработано вообще. Так что... "пилите, Шура"
1) Для создания функции открыл файл СЗ-0212.xlsx 2) При загрузке выбрал таблицу файла "ФМ3", не лист ФинМодель (что делать, если лист с ошибкой, например "ФинМодль", надо осознать) 3) Поднял заголовок 4) Загрузить в ... 5) Вернулся в расширенный редактор, ввёл функцию test, заменил источник на функцию в третьей строке кода:
(test)=> let Источник = Excel.Workbook(File.Contents(test), null, true), ФМ_3_Table = Источник{[Item="ФМ_3",Kind="Table"]}[Data], #"Измененный тип" = Table.TransformColumnTypes(ФМ_3_Table,{{"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Условия оплаты_аванс", type text}, {"Column8", type text}, {"Условия оплаты_пост оплата", type text}, {"Column10", type text}, {"Условия оплаты_гарантийное удержание", type text}, {"Column12", type text}, {"Column13", type any}, {"1", type any}, {"2", type any}, {"3", type any}, {"4", type any}, {"5", type any}, {"6", type any}, {"7", type any}, {"8", type any}, {"9", type any}, {"10", type any}, {"11", type any}, {"12", type any}, {"13", type any}, {"14", type any}, {"15", type any}, {"16", type any}, {"17", type any}, {"18", type any}, {"19", type any}, {"20", type any}, {"21", type any}, {"22", type any}, {"23", type any}, {"24", type any}}), #"Повышенные заголовки" = Table.PromoteHeaders(#"Измененный тип", [PromoteAllScalars=true]), #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"п.п", type text}, {"Показатель", type text}, {"ед.изм", type text}, {"ставка НДС", type text}, {"Вид выручки", type text}, {"%", type text}, {"дни до", type text}, {"%_1", type text}, {"дни после", type text}, {"%_2", type text}, {"дни после_3", type text}, {"ИТОГО ", type number}, {"01.09.2020", Int64.Type}, {"01.10.2020", Int64.Type}, {"01.11.2020", Int64.Type}, {"01.12.2020", Int64.Type}, {"01.01.2021", Int64.Type}, {"01.02.2021", Int64.Type}, {"01.03.2021", type number}, {"01.04.2021", type number}, {"01.05.2021", type number}, {"01.06.2021", type number}, {"01.07.2021", type number}, {"01.08.2021", type number}, {"01.09.2021", type number}, {"01.10.2021", type number}, {"01.11.2021", type number}, {"01.12.2021", type number}, {"01.01.2022", type number}, {"01.02.2022", type number}, {"01.03.2022", type number}, {"01.04.2022", type number}, {"01.05.2022", type number}, {"01.06.2022", type number}, {"01.07.2022", type number}, {"01.08.2022", type number}}) in #"Измененный тип1" 6) Далее новый запрос "Из файла - Из папки" - обращаюсь к папке со всеми 5 файлами, вижу их перечень, жму "Преобразовать данные" 7) Добавляю настраиваемый столбец - test([Folder Path]&[Name]) и ОШИБКА: Expression.Error: Имя "test" не распознано. Убедитесь в том, что оно написано верно.
Пробовал несколько раз, сверялся с шагами из видео-инструкции, что я делаю не так?
По началу хотел пивотить с пунктами, но с одним файлом не сработало. Пришлось пункты удалить. А так много что надо дорабатывать.
Года три назад была у меня работа в холдинге. Консолидировать с 60+ компаний ДЗ. У всех компаний одинаковый 1С и одинаковый отчет и все равно выгрузки по структуре были не одинаковы. Где-то колонки добавлялись, где-то пробелы. К счастью обходился всего тремя почти одинаковыми функциями, которые запускались по ориентирам.
HUGO_ST написал: то делать, если лист с ошибкой, например "ФинМодль", надо осознать
вам скинули ссылку на точно такую же тему, в ней есть вариант соединения файлов без использования файла-примера - в таком случае проблемы с именами не возникнет, вернее, она оч. просто решается (поставите условие через Text.Contains наличие "Фин" или "фин" and "мод" одновременно в соответствующей "ячейке", хотя и этого не потребуется скорее всего) вы желаете разобраться - вот и разберетесь одновременно, как обращаться ко вложенным таблицам/строкам/"ячейкам" - там все понятно по шагам
upd: доберетесь до вложенных таблиц и поставите фильтры: 1. если у вас в файле только по одной умной таблице и вся таблица с финмоделью - это умная таблица, то вообще наплевать на имя листа - отфильтруете по типу "table" на скрине 2. если у вас в файле только один лист, и хочется брать именно лист - также наплевать на имя листа - отфильтруете по типу "sheet" на скрине 3. п.1 и п.2 будут абсолютно равнозначны если в файле 1 лист и 1 таблица - что больше нравится то и фильтруйте
Здравствуйте! Можно мне внести немного ясности по прошлой теме. Для решения той задачи, которую мы решали в прошлой теме необходимо было собрать показатели из 70 файлов по списку показателей: Показатели на листе ФинМодель: Выручка_ПИР Выручка_СМР Выручка_ПНР Выручка_ТМЦ Расходы на фазе II Планирование Материальные затраты Услуги подрядчиков и прочие услуги Финансовые расходы Затраты на оплату труда сотрудников на ДГПХ/самозанятых Резерв на гарантийное обслуживание
Показатели на листе Паспорт проекта: Проект Заказчик Подразделение Руководитель проекта Фаза проекта Дата старта фазы Дата перехода на следующую фазу Длительность фазы, календарных дней Месяц старта проекта Месяц завершения проекта
Эти показатели жестко прописаны в результирующих таблицах и ТС отписалась в теме, что по этим показателям из Таблицы1 и Таблицы2 необходимо будет в дальнейшем произвести расчеты.
Т.е. при выборе данных для выполнения операции Table.UnpivotOtherColumns необходимо было отфильтровать именно эти показатели. Я это делала по структурированному номеру (считая, что он будет уникальным в файлах) и в тоже время сохраняет последовательность показателей в соответствии со структурой обозначенной в результирующей Таблице1. Сейчас, если вы посмотрите в наименования показателей, то они записаны по-разному в разных файлах и фильтровать по ним нет смысла. Но и в номерах показателей тоже получается неразбериха... Михаил Л, в коде предложенном отфильтровал показатели по сумме <> 0 и там теперь могут быть все показатели, у которых есть значения, которые в список отбираемых не входят... И получается, когда мы решали задачу в прошлой теме были одни вводные, и мы обращали внимание ТС, что если используем файл-пример для сбора данных из файлов, то данные в файлах должны строго соответствовать заданным критериям. Сейчас уже в теме с той же задачей обозначены нюансы ... voler83, по-моему мнению предложил хороший вариант для ТС, разобраться с его вариантом сбора данных из файлов в папке, учитывая имеющиеся нюансы. Но или допиливать код Михаил Л, А значения в руб. вместо тыс. руб. необходимо переводить в тыс. руб. Я думаю это не ошибка, но утверждать не могу, т.к. данные могут быть заполнены рандомными значениями, так как это тестовое задание. Могу предложить эти две строчки кода добавить
Код
replace_value = Table.ReplaceValue(#"пред. шаг",each [Значение], each if [ед.изм]= "руб." then [Значение]/1000 else [Значение],Replacer.ReplaceValue,{"Значение"})
replace_text = Table.ReplaceValue(replace_value, each [ед.изм], each if [ед.изм]= "руб." then "тыс.руб." else [Значение],Replacer.ReplaceText,{"ед.изм"})
А вот, что делать с показателями если они в разных вариациях??? Не скрываю, что могу намудрить, накрутить..., но я учусь, принимая во внимание все замечания и предложения от авторитетных профессионалов.
Цитата
HUGO_ST, написал: 4) В столбце B с кодами, по которым планировал собирать данные, нужные коды могут ошибочно дублироваться, при этом в строке корректное название (СЗ-0212.xlsx)
В этом файле наименования показателей верно указаны, по-моему мнению здесь в номерах путаница. Если я ошибаюсь в своих предположениях по заданию, то пусть меня ТС поправит...
Ma_Ri, всё верно, это тоже самое задание, файлов 68, в каждом два листа и нужно собрать с двух листов (Паспорт проекта и ФинПлан) две результирующие таблицы, с обозначенными Вами показателями, после рассчитать некий финальный показатель. Лист "Паспорт проекта" исключил из файлов чтобы поэтапно разобраться с первой частью задачи т.к. цель - не просто найти решение, а понять.
Сначала зацепился за коды категорий (те же что и у Вас), но потом обратил внимание на ошибки в столбце кода категорий или его отсутствие в некоторых таблицах. Топорным способом пересмотре все таблицы и выделил файлы с отклонениями, которые и представлены в примере, решу с ними - остальные подтянутся.
let f=(x)=>[a=Table.RemoveRows(Table.Range(Table.PromoteHeaders(x,[PromoteAllScalars=true]),2,11),4), nms1=List.Range(Table.ColumnNames(a),1,2), nms2=List.Select(Table.ColumnNames(a),(x)=>Text.Start(x,1)="0"), b=Table.UnpivotOtherColumns(Table.SelectColumns(a,nms1&nms2),nms1,"D","V"), c=List.Transform(b[D],(x)=>{x}&{Date.MonthName(Date.From(x))}&{Date.Year(Date.From(x))}), d=(x)=>if Text.Start(x{0},1)="р" then x{2}/1000 else x{2}, e=Table.ToList(Table.CombineColumns(b,{nms1{1},"D","V"},d,"tmp"),(x)=>x&{"тыс.руб."}), f=Table.FromRows(List.Transform(List.Zip({c,e}),List.Combine)), g=Table.Combine(Table.Group(f,"Column1",{"tmp",(x)=>x})[tmp])][g], bin = Folder.Files("C:\--ПУТЬ-К-СПИСКУ-ФАЙЛОВ--")[Content], lst = List.Transform(bin,(x)=>Excel.Workbook(x)[Data]{1}), to = Table.Combine(List.Transform(lst,f)) in to
Здравствуйте! Даааа...Пришел волшебник и «Вуаля!». Вот вам Power Query на "1, 2, 3". Мастер-класс от профессионала. Эти комплименты лично Вам, sotnikov, ! ) С уважением, Ma_Ri!
Цитата
HUGO_ST, написал: цель - не просто найти решение, а понять.
Хорошая цель, но только вот предложенный вариант не из тривиальных. Всего доброго! )
т. к. названия статей забиваются руками коряво (наверно, это учебная финмодель, т.к. таких не бывает, таких работников, которые так забивают данные, сразу увольняют. Да и вообще такого быть не может, ячейки со статьями д.быть защищены от изменения...), и нет никакой гарантии, что можно отфильтровать даже по наличию в названиях статей даже коротких фрагментов ("выр", "мат" & "затр" и т.п.), т.е. заранее невозможно определить всех корректных сочетаний, то абсолютно логично сначала все таблицы соединить, а потом руками фильтровать по наименованиям статей. Для соединения достаточно этого (все сделано кнопками, месяц и год дальше вытаскиваются также только кнопками, никакие перечисленные в сообщении #1 ограничения не имеют значения):
upd: единственный правильный путь - привести вручную статьи во всех источниках к одному виду и подтягивать далее что нужно из др. таблиц по наименованию статей затрат, остальное не даст никакой гарантии.
ну давайте и я пошалю. Вытащить даты проблем не составляет. Главная беда - какие показатели оставить (или какие убрать) и как их переименовать в подобие того, что нужно. Давайте полагаться на то, что какие-то "общие" статьи (расчетные - синего цвета или же 1.1 и 1.2) никто руками вбивать не будет, т.е. они стабильны и ошибок не содержат. Вот составим их список (rm_items в коде) и удалим. Это спорно, но мы ж не будем просить вас поддерживать полный список того, что надо оставить, включая вероятные ошибки. Далее, про переименования: или поддерживать словарь переименований или задаться каким-то способом, который позволит сделать это автоматом, уповая на определенного вида ошибки (в данном случае - ставят подчеркивание или убирают пробел между словами) и то, что ошибок в данных все таки меньше, чем правильных данных. Я выбрал второе - убираем пробелы и подчеркивания, поднимаем регистр (теперь ошибочно всё). Группируем по "новому" имени, считаем наиболее часто встречающееся наименование и принимаем его за правильное. Делаем словарь. Меняем в таблице названия согласно словарю. В строке Source поменяйте путь к папке с файлами.
Скрытый текст
let // список номеров для исключения из данных rm_items = {"п.п", "1.", "1.1.", "1.2.", "1.3.", "1.4.", "1.7.", "1.9.", "1.10.", "1.11.", "1.12.", "1.13.", "1.14.", "1.15."}, // функция обработки файла files = (b as binary) => [content = Excel.Workbook(b), data = content{[Item="ФМ_3", Kind="Table"]}[Data], // список дат в файле в 1-й строке dates = List.Skip(Record.FieldValues(data{0}), (x) => not Value.Is(x, type date)), col_names = Table.ColumnNames(data), // удаляем все, что надо удалить + <показатель> по 2й колонке на случай отсутствия <п.п.> remove_rows = Table.SelectRows( data, (x) => not List.Contains( rm_items, Record.Field(x, col_names{0}) ) and Record.Field(x, col_names{1}) <> "Показатель" ), // готовим данные для таблицы to_list = Table.ToList( remove_rows, (w) => List.TransformMany( {w}, (x) => List.Zip({dates, List.LastN(x, List.Count(dates))}), (x, y) => {x{1}, x{2}} & y ) ), // строим таблицу to_table = Table.FromList( List.Combine(to_list), (x) => {x{2}, Date.MonthName(x{2}, "ru-RU"), Date.Year(x{2}), x{0} , x{3} / (if Text.Contains(x{1}, "тыс") then 1 else 1000)}, {"Дата", "Месяц", "Год", "Показатель", "Сумма"} )][to_table], // получаем список файлов и имена файлов - фильтруйте его, если надо Source = Folder.Files("путь_к_папке_с_файлами")[[Content], [Name]], // обрабатываем файлы tra = Table.TransformColumns(Source, {"Content", files}), // получаем таблицу перед чисткой имен показателей xp = Table.ExpandTableColumn(tra, "Content", {"Дата", "Месяц", "Год", "Показатель", "Сумма"}), // убираем ошибочные символы и пробелы, повышаем регистр names = Table.AddColumn(xp[[Показатель]], "Name", (x) => Text.Upper(Text.Remove(x[Показатель], {" ", "_"}))), // группируем, получаем наиболее часто встречающееся название показателя g = Table.Group( names, "Name", {"Value", (x) => List.Mode(x[Показатель])} ), // словарь для массовой замены наименований dic_names = Record.FromTable(g), // меняем наименование показателей с помощью словаря upd_names = Table.TransformColumns( xp, {"Показатель", (x) => Record.Field( dic_names, Text.Upper(Text.Remove(x, {" ", "_"})) )} ) in upd_names
Можно выскажу свое скромное мнение. Просто я понимаю, что HUGO_ST, плохо владеет PQ и ему надо сделать задание самому, чтобы его защитить (это предположение, но оно на поверхности).
Цитата
voler83, написал: единственный правильный путь - привести вручную статьи во всех источниках к одному виду
Я не думаю, что работодателю понравится такой вариант решения... даже если в итоге я получу желаемый результат. AlienSx, Ваш гениальный вариант (другого Вы и не могли предложить) в данной ситуации менее всего подходит ТС, его HUGO_ST не защитит на собеседовании, но понятно же, что профессионал писал... Я бы (если бы я оказалась в такой ситуации) вообще уже не заморачивалась по поводу этих нюансов, сделала по варианту, например, sotnikov, (на данный момент он более приемлемый для HUGO_ST) или даже наклацала свой (пусть он будет простой, но я его могу разложить по полочкам) . А если вылезли бы косяки, я бы им предоставила свои аргументы, что это их проблемы, что не смогли подготовить для такого объема данных (70 файлов) корректные данные. И спросила бы их какие у них есть вопросы или претензии к решению задачи, а не к его результату, который получился допустим неверный не по моей вине... как-то так...иначе с этим заданием можно зайти на третий круг...(в этом задании еще Паспорт проекта и вычисления и какие там нюансы?..) Если стоял бы выбор выглядеть достойно с простым, но своим решением, или с продвинутым (гениальным от AlienSx), но глупо (рискуя попасть в неудобное положение) Что выбираем?... Но выбор не за мной, а за HUGO_ST.
Ma_Ri написал: ...вариант <...> в данной ситуации менее всего подходит ТС, его HUGO_ST не защитит на собеседовании
вы ТСа так вот сходу в "читеры" записали, да еще и защищаете его, бедного, от непонятных решений. Нехорошо это. Здесь читерам не рады. Будем надеяться, что ТС на самом деле не такой и своей задачкой преследует исключительно образовательные цели. А если это так, то мой код ему только на пользу пойдет - там есть что почитать (комментарии же по-русски написаны и вполне себе описывают происходящее вокруг). Кстати, Вам бы посоветовал не тратить много времени на кнопочные решения. "Мышкоклац" - это особый вид искусства, который сильно мешает познанию заложенных в M возможностей. "Опиум народа" Я не призываю все писать в расширенном редакторе. Нужно быстрое и простое решение - делайте мышой. Но лучше потратить время на чтение документации по функциям, статей про основы основ в M (даже еще до изучения функций), блогов с задачами и их типовыми решениями, чем на полировку скиллов владения интерфейсом редактора запросов. Потом эти скиллы всего лишь помогут вам помещать в расширенный редактор "макет" ваших великолепных выражений на языке M
AlienSx написал: вы ТСа так вот сходу в "читеры" записали
я согласен с Ma_Ri в части общей легенды задания, т.к. в предыдущей теме бывшая ТС это прямо пояснила. Но если текущий ТС также соискатель в ту же компанию, то это не оч. правильно здесь дублировать, т.к. работодатель тут все смотрит с большой степенью вероятности. Но это их дело.
написал: Я не думаю, что работодателю понравится такой вариант решения... даже если в итоге я получу желаемый результат. AlienSx , Ваш гениальный вариант (другого Вы и не могли предложить) в данной ситуации менее всего подходит ТС, его HUGO_ST не защитит на собеседовании, но понятно же, что профессионал писал...
Всем Привет! Спасибо за Ваше участие, Вы просто - космос.
Нет цели ввести работодателя в заблуждение, я не тот студент, что возьмёт чужое и выдаст за своё. Хочется понять, изучить для себя, а потом уже для кого-то.
Много материала в теме, разные подходы очевидных мастеров - это ценно, пробую повторить и собрать что-то своё, пусть пока не всё получается, но получится.
HUGO_ST написал: разные подходы очевидных мастеров - это ценно
забудьте. У каждого свой подход, свой путь. Вам нужна "база" (что такое список, запись, таблица, запрос в PQ M и т.п.). После этого придет понимание (если придет) с чем имеют дело загадочные аргументы функций PQ M и все будет насаживаться, как шашлык на шампур Если будет база знаний, то решение любой задачи - это только фантазии автора решения, обладающего определенным багажом знаний, а также собственного воображения и понимания как должны решаться конкретная задача. Ну не без "приемчиков", конечно, которые тоже должны входить в арсенал. Но не переусердствуйте с "копилкой решений", о которой все вокруг говорят. Вооружайтесь инструментарием, включайте мозг и решайте. Все задачи так решаются.