Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Преобразование шапки таблицы в Power Query, Прошу поделиться приёмом преобразования сложных шапок через Power Query
 
#1
06.07.2018 15:40:22
   Выгружаю отчёт по продажам из 1С. Требуется приведение таблицы из сложной в плоскую для дальнейшего анализа. Причем преобразовывать таблицу нужно через Power Query.Проблема в том, что по каждому клиенту за каждый месяц есть 2 показателя: сумма и кол-во. Изначально дата в шапке объединена на 2 ячейки, после загрузки в PQ дата остаётся только у "Кол-ва".
  Прошу поделиться приёмом преобразования сложных шапок средствами Power Query. Причём показателей может быть больше 3 или 4 (прибыль, объём).

Я искал по форуму, но похожие темы не дают вопрос на мой ответ. Таблицу прикладываю, на втором листе желаемый результат.

Работаю в Excel 2013 + надстройка PQ.
 
 
 
#2
06.07.2018 15:55:12
Слегка изменил ваш пример - не нужно загонять в заголовки первую строку таблицы. Так преобразовывать проще. Но если для вас принципиально, то можно сделать и с вашим вариантом, просто больше ненужной фигни зачищать придется.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Транспонированная таблица" = Table.Transpose(Источник),
    #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Сведено"),
    #"Транспонированная таблица1" = Table.Transpose(#"Объединенные столбцы"),
    #"Замененное значение" = Table.ReplaceValue(#"Транспонированная таблица1",";","",Replacer.ReplaceText,{"Column1", "Column2", "Column3"}),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Замененное значение", [PromoteAllScalars=true]),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Повышенные заголовки", {"Клиент", "Менеджер", "Тип бизнеса"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Другие столбцы с отмененным свертыванием","Атрибут",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Атрибут.1", "Атрибут.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Атрибут.1", type datetime}, {"Атрибут.2", type text}}),
    #"Сведенный столбец" = Table.Pivot(#"Измененный тип1", List.Distinct(#"Измененный тип1"[Атрибут.2]), "Атрибут.2", "Значение", List.Sum),
    #"Переименованные столбцы" = Table.RenameColumns(#"Сведенный столбец",{{"Атрибут.1", "Дата"}})
in
    #"Переименованные столбцы"
Изменено: PooHkrd - 06.07.2018 15:57:39
Вот горшок пустой, он предмет простой...
 
 
 
#3
06.07.2018 16:08:08
Просто для справки: https://www.youtube.com/watch?v=pV8Rw5gSjFI
 
 
 
#4
06.07.2018 17:03:57
Спасибо за ответ!
PooHkrd написал:

Цитата
Слегка изменил ваш пример - не нужно загонять в заголовки первую строку таблицы. Так преобразовывать проще.
PooHkrd, Дело в том, что в дальнейшем, в определенную папку, будет выгружаться отчёт из 1С, а PQ будет обрабатывать новый файл и пользователь должен будет только обновлять запрос, поэтому вариант с изменением шапки не подходит.

Поэтому хочется понять что можно сделать с шапкой именно в таком варианте? Правда, файл можно выгружать с шапкой во втором варианте с указанием периода над шапкой. Прилагаю файл.
Тогда можно убирать верхние 3 строки, оставляя одну пустую над таблицей. Это упрощает преобразование?


Dark1589, Спасибо за информацию, обязательно посмотрю позже. Просто на работе заблокирован youtube.
 
 
 
#5
06.07.2018 17:09:33
Не понял, у вас из 1С отчет сразу же выгружается с таблицей в файле? И давно это с ним?
А по обновленному примеру: загружаете запрос из книги Excel выбираете лист, на котором расположен отчет. Потом удаляете 4 верхних строки и далее по коду приведенному ранее.
Изменено: PooHkrd - 06.07.2018 17:11:07
Вот горшок пустой, он предмет простой...
 
 
 
#6
06.07.2018 17:20:24
PooHkrd,ну нет конечно. Я возможно не так выразился. Файл из 1С я немного обработал - не имею права загружать первоначальный вид.
Во вложении вариант выгрузки из 1С, с обрезанными итогами (часть таблицы).
 
 
 
#7
06.07.2018 17:28:31
Путь к файлу только поменяйте:
Код
let
    Источник = Excel.Workbook(File.Contents("E:\Вопрос по PQ-2.xls"), null, true),
    TDSheet1 = Источник{[Name="TDSheet"]}[Data],
    #"Удаленные верхние строки" = Table.Skip(TDSheet1,4),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Удаленные верхние строки",{"Column2", "Column3", "Column5"}),
    #"Транспонированная таблица" = Table.Transpose(#"Удаленные столбцы"),
    #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Сведено"),
    #"Транспонированная таблица1" = Table.Transpose(#"Объединенные столбцы"),
    #"Замененное значение" = Table.ReplaceValue(#"Транспонированная таблица1",";","",Replacer.ReplaceText,{"Column1", "Column2", "Column3"}),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Замененное значение", [PromoteAllScalars=true]),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Повышенные заголовки", {"Клиент", "Менеджер", "Тип бизнеса"}, "Атрибут", "Значение"),
    #"Измененный тип" = Table.TransformColumnTypes(#"Другие столбцы с отмененным свертыванием",{{"Значение", type number}}),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Измененный тип","Атрибут",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Атрибут.1", "Атрибут.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Атрибут.1", type datetime}, {"Атрибут.2", type text}}),
    #"Сведенный столбец" = Table.Pivot(#"Измененный тип1", List.Distinct(#"Измененный тип1"[Атрибут.2]), "Атрибут.2", "Значение", List.Sum),
    #"Переименованные столбцы" = Table.RenameColumns(#"Сведенный столбец",{{"Атрибут.1", "Дата"}})
in
    #"Переименованные столбцы"

И зачем такие извращения? Не проще вывод результата в самом отчете 1С изменить? Там обычно это все не сложно делается.
Изменено: PooHkrd - 06.07.2018 17:30:37
Вот горшок пустой, он предмет простой...
 
 
 
#8
06.07.2018 18:20:47
Цитата
PooHkrd написал:
Не проще вывод результата в самом отчете 1С изменить?
Исключительно правильный совет.
OfficeProPlus 365x64
Win64forWorkstation
 
 
 
#9
06.07.2018 18:20:53
Цитата
PooHkrd написал:
И зачем такие извращения? Не проще вывод результата в самом отчете 1С изменить? Там обычно это все не сложно делается.
2 причины:
  • Анализ проводить нужно, а с программистом 1С не договориться.
  • Я PQ изучаю только несколько дней и мне было важно понять алгоритм преобразования. Благодаря вам, теперь я смогу обрабатывать и другие отчёты, с другими шапками  :)
Правда возникла одна проблема, по шагам делал преобразование, и на этапе отмены свёртывания:
Цитата
#"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Повышенные заголовки", {"Клиент", "Менеджер", "Тип бизнеса"}, "Атрибут", "Значение"),
пропадают строки с в которых у фирмы значения отсутствуют. Что есть нехорошо...Я решил эту проблему через Table.ReplaceValue(#"Измененный тип1",null,0,Replacer.ReplaceValue,{
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Столбец1", type text}, {"Столбец2", type any}, {"Столбец3", type text}, {"Столбец4", type text}, {"Столбец5", type any}, {"Столбец6", type text}, {"Столбец7", type any}, {"Столбец8", type any}, {"Столбец9", type any}, {"Столбец10", type any}, {"Столбец11", type any}, {"Столбец12", type any}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",4),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Удаленные верхние строки",{"Столбец2", "Столбец3", "Столбец5"}),
    #"Транспонированная таблица" = Table.Transpose(#"Удаленные столбцы"),
    #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Сведено"),
    #"Транспонированная таблица1" = Table.Transpose(#"Объединенные столбцы"),
    #"Замененное значение" = Table.ReplaceValue(#"Транспонированная таблица1",";","",Replacer.ReplaceText,{"Column1", "Column2", "Column3"}),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Замененное значение", [PromoteAllScalars=true]),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Клиент", type text}, {"Менеджер", type text}, {"Тип бизнеса", type text}, {"01.01.2016 0:00:00;Количество", Int64.Type}, {"01.01.2016 0:00:00;Сумма", type number}, {"01.02.2016 0:00:00;Количество", Int64.Type}, {"01.02.2016 0:00:00;Сумма", type number}, {"01.03.2016 0:00:00;Количество", Int64.Type}, {"01.03.2016 0:00:00;Сумма", type number}}),
    #"Замененное значение1" = Table.ReplaceValue(#"Измененный тип1",null,0,Replacer.ReplaceValue,{"01.01.2016 0:00:00;Количество", "01.01.2016 0:00:00;Сумма", "01.02.2016 0:00:00;Количество", "01.02.2016 0:00:00;Сумма", "01.03.2016 0:00:00;Количество", "01.03.2016 0:00:00;Сумма"}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Замененное значение1", {"Клиент", "Менеджер", "Тип бизнеса"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Другие столбцы с отмененным свертыванием", "Атрибут", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Атрибут.1", "Атрибут.2"}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Атрибут.1", type datetime}, {"Атрибут.2", type text}}),
    #"Сведенный столбец" = Table.Pivot(#"Измененный тип2", List.Distinct(#"Измененный тип2"[Атрибут.2]), "Атрибут.2", "Значение", List.Sum),
    #"Переименованные столбцы" = Table.RenameColumns(#"Сведенный столбец",{{"Атрибут.1", "Дата"}}),
    #"Измененный тип3" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Дата", type date}})
in
    #"Измененный тип3

но обязательно ли это? Иначе нельзя, или я что-то не так сделал на этапе Table.UnpivotOtherColumns  ???
 
 
 
#10
06.07.2018 18:23:43
Цитата
vector1 написал:
Исключительно правильный совет.
Наверное вы правы, но к сожалению, такой возможности у меня нет.
 
 
 
#11
09.07.2018 09:43:03
Цитата
Kintoho написал:
но обязательно ли это?
Да, иначе будут пропадать, так что вы все правильно сделали. Если нули в таблице потом не нужны, можно (только осторожно ;) ) обратную замену сделать 0 на null.
Вот горшок пустой, он предмет простой...
 
 
 
#12
09.07.2018 10:55:27
Доброе время суток.
Цитата
PooHkrd написал:
обратную замену сделать 0 на null.
Коллега, но ведь может же быть, что в таблице присутствую вполне себе важные 0, которые необходимо сохранить.
Более привязанный к структуре вариант. Жаль, что ТС поленился представить полную структуру выгрузки. Так что для последующего допиливания напильником.
Скрытый текст
 
 
 
#13
09.07.2018 11:14:24
Цитата
Андрей VG написал:
Коллега, но ведь может же быть, что в таблице присутствую вполне себе важные 0
Так потому и написал, что осторожно. Менять нужно только в столбце с числовым типом, и только те что равны 0, а не содержат ноль.
Вот горшок пустой, он предмет простой...
 
 
 
#14
09.07.2018 15:33:08
Цитата
PooHkrd написал:
Менять нужно только в столбце с числовым типом
Увы, далеко не всегда можно определить тип столбца в таблице заранее. В прочем, в любом случае решение  детализационных задач - это дело всегда ТС. Ну, или отдельной темы.
 
 
 
#15
09.07.2018 15:51:50
Цитата
Андрей VG написал:
Жаль, что ТС поленился представить полную структуру выгрузки. Так что для последующего допиливания напильником.
Я не лился, просто не  имею права выкладывать рабочую информацию. А маскировать таблицу на 1000+ строк не хочется. Да и смысла нет, все возможные варианты присутствуют в кратком примере. Рассмотрю ваш код позже, надеюсь и там почерпну полезную информацию.
Цитата
PooHkrd написал:
Так потому и написал, что осторожно. Менять нужно только в столбце с числовым типом, и только те что равны 0, а не содержат ноль.
Меня более чем устраивает вариант с заменой null на 0. Обратная замена, в моём случае смысла не имеет.
PooHkrd, большое спасибо за помощь! Уже во всю использую в работе  :)

PQ конечно мощный инструмент, жаль литературы русскоязычной не найти... Хочется подробно изучить, особенно "Расширенный редактор".
Цитата
Dark1589 написал:
Просто для справки:  https://www.youtube.com/watch?v=pV8Rw5gSjFI
Наглядное видео, хотя на мой взгляд, много лишних действий. Спасибо!
 
 
 
#16
09.07.2018 15:59:48
Kintoho, вполне возможно. В PQ нужно всегда думать на дальнюю перспективу, по этому готовые решения с форма не всегда получается адаптировать в случае изменений, в отличии от своего, хоть и кривоватого решения. Уж лучше самому разобраться и сделать как нужно именно вам.

ps. Хотя я бы всё сделал, чтоб убрать все эти объединения и уровни до создания запроса.
 
 
 
#17
09.07.2018 16:38:10
Цитата
Kintoho написал:
имею права выкладывать рабочую информацию.
А я об этом писал? Вы серьёзно не видите разницы между структурой данных и фактическими данными? :(
 
 
 
#18
09.07.2018 16:54:06
Цитата
Kintoho написал:
жаль литературы русскоязычной не найти...
:oops: работаем над этим  ;) хотя перевести гораздо быстрее, конечно, и думать не особо надо
F1 творит чудеса
 
 
 
#19
09.07.2018 16:55:16
Dark1589, ну мне не готовое решение нужно было, а алгоритм/метод/подсказка. Я их получил в полной мере и уже применяю к другим отчётам, за что всем спасибо!

Цитата
Dark1589 написал:
ps. Хотя я бы всё сделал, чтоб убрать все эти объединения и уровни до создания запроса.

Так в том и прелесть PQ, что я единожды создаю набор правил обработки для каждого отчёта (а их более 10 вариантов и шапка везде своя), и потом просто обновляю запрос. А каждый раз форматировать отчёт это долго и зачем? Это может делать компьютер.
В идеале ещё бы прикрутить, автоматическую подкачку файла со свежей датой из определенной папки, и тогда вообще красота будет.... Но этим я займусь немного позже, как я уже понял PQ и это под силу  8)


Андрей VG, разницу я понимаю. Выложить пустую таблицу можно конечно.... но смысл? Фактически, я обрезал только итоги по столбцам и строкам, а также поменял имена.
 
 
 
Страницы: 1
Наверх