Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Преобразовать данные Power Query для сводной таблицы
 
#1
26.11.2019 15:39:19
Предложения по быстрому способу преобразования данных (файл во вложении) по блокам (выделены рамкой - столбец "Наименование работ"), убрать всего и итого для работы в Power Pivot и сводной таблицы. С условным столбцом получается громостко и Power Query при большом объёме (таблиц 1600 шт.) зависает.
Изменено: Николай Савенко - 29.11.2019 17:09:38
 
 
 
#2
26.11.2019 15:44:56
Если честно не понятно, чего вы хотите получить на выходе?
Вот горшок пустой, он предмет простой...
 
 
 
#3
26.11.2019 17:48:20
Я добавил рядом столбец, что должно получиться. В скобках процесс и наименование подразделения, а так же, что не нужно тащить в данные.
Изменено: Николай Савенко - 27.11.2019 18:20:17
 
 
 
#4
26.11.2019 19:38:30
Николай Савенко, функция Максима Зеленского Вам в помощь (для работы с уровнями группировок):

https://github.com/hohlick/Power-Query-Excel-Formats/blob/master/RowsOutline/ExcelWorksheetsRowOutlines.pq
Изменено: Aleksei_Zhigulin - 26.11.2019 19:40:45
 
 
 
#5
27.11.2019 07:42:28
Спасибо, сейчас попробуем и отпишусь вечером
 
 
 
#6
27.11.2019 07:50:29
По функции Максима Зеленского fnGetRowsOutline не получилось, она работает на уровни структуры строк. А в данной таблице нет уровней, здесь разбивается на блоки, они разбиты.
Изменено: Николай Савенко - 27.11.2019 18:20:04
 
 
 
#7
27.11.2019 09:42:25
Цитата
Николай Савенко написал:
С условным столбцом получается громостко
т.е. какой-то рабочий вариант решения у вас имеется, но с большим объемом он не справляется? Покажете запрос?
И второй вопрос: какой смысл объединять наименование подразделения и работ, так никакого анализа в сводной не получится, наоборот их нужно разнести в столбцы таблицы раздельно.
Изменено: PooHkrd - 27.11.2019 09:44:21
Вот горшок пустой, он предмет простой...
 
 
 
#8
27.11.2019 18:18:29
Спасибо PooHkrd. Я переделал желаемую таблицу (разнёс: наименование подразделения, наименование работ, процесс). Файл во вложении. Так же в Power query есть полурабочий вариант. Я не могу сообразить в каком направлении двигаться. Перебирать данные логикой, в моём понимании неправильно. Завтра изменятся данные и снова переписывать, так же не хочется тянуть в модель данных Итого и Всего (это лишних 171 строчка). Могу направить файлы, как сделал по другим предприятиям.  
Изменено: Николай Савенко - 29.11.2019 10:22:17
 
 
 
#9
28.11.2019 08:25:28
Рабочий вариант решения во вложении и код ниже, но он уж очень большой получается
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Удаленные столбцы" = Table.RemoveColumns(Источник,{"План", "Факт"}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Удаленные столбцы", "ПГР / ОГР", each if [#"№ п/п"] = 1 then "ПГР + ОГР" else if [#"№ п/п"] = 2 then "ПГР" else if [#"№ п/п"] = 3 then "ОГР" else null),
    #"Заполнение вниз" = Table.FillDown(#"Условный столбец добавлен",{"Наименование работ", "ПГР / ОГР"}),
    Фильтр = Table.SelectRows(#"Заполнение вниз", each ([#"ПГР / ОГР"] <> "ПГР + ОГР")),
    Подразделение = Table.AddColumn(Фильтр, "Наименование подразделения", each if Text.Contains([Наименование работ], """") then Text.BetweenDelimiters([Наименование работ], """", """") 
                                                                                else if Text.Contains([Наименование работ], "ПНР", Comparer.OrdinalIgnoreCase)  then  "Екатерина 1 + Екатерина 2" 
                                                                                else null),
    УдалВерхСтрок = Table.RemoveFirstN (Подразделение, 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Анфисинский") <> true),
    #"Другие удаленные столбцы" = Table.SelectColumns(УдалВерхСтрок,{"ПГР / ОГР", "Наименование подразделения", "Наименование работ", "Ед. изм."}),
    СтартоваяТаблица = #"Другие удаленные столбцы",
    ПГР_OriginalTable = Table.SelectRows(СтартоваяТаблица, each ([#"ПГР / ОГР"] = "ПГР")),
    СтолбецПроцессПГР = Table.AddColumn(ПГР_OriginalTable, "Наименование процесса", each if Text.Contains([Наименование работ], "попутная", Comparer.OrdinalIgnoreCase) then "Добыча руды - попутная" 
                                    else if Text.Contains([Наименование работ], "очистная", Comparer.OrdinalIgnoreCase) then "Добыча руды - очистная" 
                                    else if Text.Contains([Наименование работ], "добыча руды", Comparer.OrdinalIgnoreCase) then "добыча руды" 
                                    else if Text.Contains([Наименование работ], "перевозка", Comparer.OrdinalIgnoreCase) then "Перевозка" 
                                    else if Text.Contains([Наименование работ], "Закладка", Comparer.OrdinalIgnoreCase) then "Закладка" 
                                    else if Text.Contains([Наименование работ], "Проходка", Comparer.OrdinalIgnoreCase) then "Проходка горных выработок" 
                                    else if Text.Contains([Наименование работ], "Крепление", Comparer.OrdinalIgnoreCase) then "Крепление" 
                                    else if Text.Contains([Наименование работ], "Бурение ПГР", Comparer.OrdinalIgnoreCase) then "Бурение ПГР"  
                                    else if Text.Contains([Наименование работ], "Проходческое", Comparer.OrdinalIgnoreCase) then "Бурение проходческое" 
                                    else if Text.Contains([Наименование работ], "крепь", Comparer.OrdinalIgnoreCase) then "Бурение под крепь" 
                                    else if Text.Contains([Наименование работ], "очистное", Comparer.OrdinalIgnoreCase) then "Бурение очистное" 
                                    else if Text.Contains([Наименование работ], "СЭР", Comparer.OrdinalIgnoreCase) then "Бурение СЭР" 
                                    else if Text.Contains([Наименование работ], "Водоотлив", Comparer.OrdinalIgnoreCase) then "Водоотлив" 
                                    else null),
    ЗаполнениеНаименований_ПГР = Table.FillDown(СтолбецПроцессПГР,{"Наименование процесса", "Наименование подразделения"}),
    #"Table_Добыча рудыПГР" = Table.SelectRows(ЗаполнениеНаименований_ПГР, each Text.Contains([Наименование процесса], "Добыча руды")),
    #"Строки с примененным фильтром" = Table.SelectRows(ЗаполнениеНаименований_ПГР, each Text.Contains([Наименование процесса], "бурение", Comparer.OrdinalIgnoreCase)),
    #"Удаленные верхние строки" = Table.RemoveFirstN (#"Строки с примененным фильтром", 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Анфисинский") <> true),
    Table_БурениеПГР = Table.SelectRows(#"Удаленные верхние строки", each ([Наименование работ] <> "в т.ч. Бурение под крепь" and [Наименование работ] <> "в т.ч. Очистное" and [Наименование работ] <> "в т.ч. Проходческое" and [Наименование работ] <> "в т.ч. СЭР")),
    #"Строки с примененным фильтром1" = Table.SelectRows(ЗаполнениеНаименований_ПГР, each ([Наименование процесса] = "Перевозка")),
    Перевозка = Table.RemoveFirstN (#"Строки с примененным фильтром1", 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "в т.ч. ООО ""Полиметалл ДВ""") <> true),
    Table_ПеревозкаПГР = Table.ReplaceValue(Перевозка,"в т.ч.","",Replacer.ReplaceText,{"Наименование работ"}),
    #"Строки с примененным фильтром2" = Table.SelectRows(ЗаполнениеНаименований_ПГР, each Text.Contains([Наименование процесса], "Закладка")),
    #"Замененное значение" = Table.ReplaceValue(#"Строки с примененным фильтром2","в т.ч. ","",Replacer.ReplaceText,{"Наименование работ"}),
    Table_ЗакладкаПГР = Table.SelectRows(#"Замененное значение", each ([Наименование подразделения] <> "Ресурсы Албазино")),
    #"Строки с примененным фильтром3" = Table.SelectRows(ЗаполнениеНаименований_ПГР, each ([Наименование процесса] = "Крепление")),
    Table_КреплениеПГР = Table.RemoveFirstN (#"Строки с примененным фильтром3", 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Анфисинский") <> true),
    #"Строки с примененным фильтром4" = Table.SelectRows(ЗаполнениеНаименований_ПГР, each ([Наименование процесса] = "Водоотлив")),
    #"Замененное значение1" = Table.ReplaceValue(#"Строки с примененным фильтром4","в т.ч. ","",Replacer.ReplaceText,{"Наименование работ"}),
    Table_ВодоотливПГР = Table.RemoveFirstN (#"Замененное значение1", 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Ольгинский") <> true),
    #"Строки с примененным фильтром5" = Table.SelectRows(ЗаполнениеНаименований_ПГР, each ([Наименование процесса] = "Проходка горных выработок")),
    #"Замененное значение2" = Table.ReplaceValue(#"Строки с примененным фильтром5","в т.ч. ","",Replacer.ReplaceText,{"Наименование работ"}),
    Table_ПроходкаПГР = Table.RemoveFirstN (#"Замененное значение2", 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Анфисинский") <> true),
    ПГР_Resultat = #"Table_Добыча рудыПГР" & Table_ПеревозкаПГР & Table_ЗакладкаПГР & Table_ПроходкаПГР & Table_КреплениеПГР & Table_БурениеПГР & Table_ВодоотливПГР,
    ОГР_OriginalTable = Table.SelectRows(СтартоваяТаблица, each ([#"ПГР / ОГР"] = "ОГР")),
    УдалВерхИтоги = Table.RemoveFirstN (ОГР_OriginalTable, 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Анфисинский") <> true),
    СтолбецПроцессОГР = Table.AddColumn(УдалВерхИтоги, "Наименование процесса", each if Text.Contains([Наименование работ], "добыча руды", Comparer.OrdinalIgnoreCase) then "Добыча руды" 
                                    else if Text.Contains([Наименование работ], "перевозка", Comparer.OrdinalIgnoreCase) then "Перевозка" 
                                    else if Text.Contains([Наименование работ], "вскрыша", Comparer.OrdinalIgnoreCase) then "Вскрыша" 
                                    else if Text.Contains([Наименование работ], "бурение взрывных", Comparer.OrdinalIgnoreCase) then "Бурение взрывных" 
                                    else if Text.Contains([Наименование работ], "бурение контурное", Comparer.OrdinalIgnoreCase) then "Бурение контурное" 
                                    else if Text.Contains([Наименование работ], "бурение СЭР", Comparer.OrdinalIgnoreCase) then "Бурение СЭР" 
                                    else if Text.Contains([Наименование работ], "Взрывание", Comparer.OrdinalIgnoreCase) then "Взрывание скважин" 
                                    else if Text.Contains([Наименование работ], "Дробление", Comparer.OrdinalIgnoreCase) then "Дробление щебня" 
                                    else if Text.Contains([Наименование работ], "Водоотлив", Comparer.OrdinalIgnoreCase) then "Водоотлив" 
                                    else if Text.Contains([Наименование работ], "Осушени", Comparer.OrdinalIgnoreCase) then "Осушение борта" 
                                    else null),
    ЗаполнениеНаименований_ОГР = Table.FillDown(СтолбецПроцессОГР,{"Наименование подразделения", "Наименование работ", "Ед. изм.", "Наименование процесса"}),
    Table_ДобычаОГР = Table.SelectRows(ЗаполнениеНаименований_ОГР, each Text.Contains([Наименование процесса], "Добыча руды")),
    Table_ПеревозкаОГР = Table.SelectRows(ЗаполнениеНаименований_ОГР, each Text.Contains([Наименование процесса], "перевозка", Comparer.OrdinalIgnoreCase)),
    #"Строки с примененным фильтром6" = Table.SelectRows(ЗаполнениеНаименований_ОГР, each Text.Contains([Наименование процесса], "Вскрыша")),
    #"Строки с примененным фильтром7" = Table.SelectRows(#"Строки с примененным фильтром6", each not Text.Contains([Наименование работ], "всего")),
    Table_ВскрышаОГР = Table.ReplaceValue(#"Строки с примененным фильтром7","в т.ч. ","",Replacer.ReplaceText,{"Наименование работ"}),
    #"Строки с примененным фильтром8" = Table.SelectRows(ЗаполнениеНаименований_ОГР, each Text.Contains([Наименование процесса], "Взрывание")),
    Table_Взрывание = Table.SelectRows(#"Строки с примененным фильтром8", each not Text.Contains([Наименование работ], "всего")),
    Пользовательская1 = Table.SelectRows(ЗаполнениеНаименований_ОГР, each Text.Contains([Наименование процесса], "Водоотлив") or Text.Contains([Наименование процесса], "Дробление") or Text.Contains([Наименование процесса], "Осушение")),
    Table_Водоотлив = Table.ReplaceValue(Пользовательская1,"Екатерина 1 + Екатерина 2","Общий",Replacer.ReplaceText,{"Наименование подразделения"}),
    Бурение = Table.SelectRows(ЗаполнениеНаименований_ОГР, each Text.Contains([Наименование процесса], "Бурение")),
    УдалВерхСтрок_ОГР = Table.RemoveFirstN (Бурение, 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Анфисинский") <> true),
    #"Строки с примененным фильтром9" = Table.SelectRows(УдалВерхСтрок_ОГР, each not Text.Contains([Наименование работ], "всего") and not Text.Contains([Наименование работ], "итого")),
    Пользовательская2 = Table.RemoveLastN (#"Строки с примененным фильтром9", 
                                each List.Contains( try Record.FieldValues(_) otherwise "FALSE", "Итого бурение ООО ""Ресурсы Албазино""") <> true),
    Table_Бурение = Table.RemoveLastN(Пользовательская2,1),
    ОГР_Resultat = Table_ДобычаОГР & Table_ПеревозкаОГР & Table_ВскрышаОГР & Table_Бурение & Table_Взрывание & Table_Водоотлив,
    Resultat = ПГР_Resultat & ОГР_Resultat
in
    Resultat
 
 
 
#10
29.11.2019 10:21:51
Сегодня перепишу вариант решения на условный столбец с несколькими критериями ввода и на группировку по процессам и отпишусь. Проанализирую по времени запросов.
 
 
 
#11
29.11.2019 11:27:18
Планировал сегодня вам показать вариант. Кстати, в вашем мегазапросев любом случае некорректно выводилась часть данных, не так как вы показали в таблице, которую хотелось бы получить. Ну и очень много ненужных действий. Я так понимаю что делали по принципу "как умею". Но именно из-за этого происходит многократное обращение к источнику и запрос зависает на большом массиве.
И вот последняя загадка для меня такая, у вас есть строка:
Перевозка руды на ОФ "Полиметалл ДВ" в количестве 2 штуки, и в первом случае вы её не хотите показывать в итоговом массиве, а во втором хотите, при этом в строках никаких отличий нету, как определиться в общем случае показывать или нет?
Изменено: PooHkrd - 29.11.2019 12:14:03
Вот горшок пустой, он предмет простой...
 
 
 
#12
29.11.2019 13:05:23
Да показываем, в первом варианте я её вообще упустил из виду. Заранее Вам спасибо. В Power Query учусь, да есть есть такое дело: по принципу "как умею". Но не останавливаюсь, прогрессирую :)  
 
 
 
#13
29.11.2019 17:09:09
В общем, на мой взгляд самым эффективным вариантом разбора вашей структуры будет использование справочников. Я тут накидал прикидки как я это вижу. Но там есть что допилить напильником. В общем еще накидаете чего-нибудь в справочники, потом пара-тройка фильтров и по идее результат будет готов, целиком вашу задачу не сделаю, т.к. по сути она тянет на ветку работа. Но вектор куда двигаться вам задал. Пробуйте.
Вот горшок пустой, он предмет простой...
 
 
 
#14
30.11.2019 08:38:08
Я готов оплатить за Ваш вектор направления. Вы потратили на меня время и дали новый импульс изучения Power Query. Тем более я делаю это для облегчения своей жизни на работе, а то "копировать и вставить" - надоело. За раздел "Работа" спасибо. Попробую сначала сформулировать и изложить свою идею на бумагу и первое это обращусь к Вам, а затем напишу туда.  
 
 
 
Страницы: 1
Наверх