Страницы: 1
RSS
Преобразование построчных данных в таблице в столбцы, решение через Power Query
 
Друзья, приветствую. Помогите с решением.

Есть таблица в которой несколько разных типов данных находятся друг под другом. Хочу преобразовать их в плоскую таблицу  для анализа через сводную.
Никак  не могу найти решение.

Пример прикрепляю (как есть- как надо)

Заранее благодарен.
P.S. Через макрос тоже вариант. Но предпочтительнее PQ так как таблица каждый день будет обновляться и расти
Изменено: Dyroff - 11.09.2018 16:20:29
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Добрый день

Цитата
Dyroff написал:
Через макрос тоже вариант.
Собственно, вариант через макрос :)
 
webley, Спасибо Вам за труд. Отлично.
Но надеюсь что появится решение на PQ:)
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Цитата
Dyroff написал:
Но надеюсь что появится решение на PQ
я бы на самом деле тоже посмотрел такой вариант  
 
webley,  Значит ждем специалистов:)
Либо Андрей VG, либо PooHkrd  ну или Максим Зеленский:)
Они точно знают ответ.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Ну, вот натыкал мышкой впопыхах, а так-то если подумать, то наверное можно и изящнее, да и по-короче.
И еще комментарий "не работает" по структуре исходной таблицы относится к номеру авто, а не к типу, так что если нужно к типу, то заполняйте каждую вторую строку в паре, а не первую.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Замененное значение" = Table.ReplaceValue(Источник,null,"",Replacer.ReplaceValue,{"10.09.2018", "11.09.2018"}),
    #"Пониженные заголовки" = Table.DemoteHeaders(#"Замененное значение"),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Пониженные заголовки", "Индекс", 2, 1),
    #"Столбец с выполненным целочисленным делением" = Table.TransformColumns(#"Добавлен индекс", {{"Индекс", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Сгруппированные строки" = Table.Group(#"Столбец с выполненным целочисленным делением", {"Индекс"}, {{"Столб1", each Text.Combine(_[Column1],"="), type text}, {"Столб2", each Text.Combine(_[Column2],"="), type text}, {"Столб3", each Text.Combine(_[Column3],"="), type text}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Сгруппированные строки",{"Индекс"}),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Удаленные столбцы","Столб1",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Столб1.1", "Столб1.2"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Столб1.1", type text}, {"Столб1.2", type text}}),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Измененный тип", [PromoteAllScalars=true]),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Повышенные заголовки", {"ФИО", "Перевозчик"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Другие столбцы с отмененным свертыванием","Атрибут",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Атрибут.1", "Атрибут.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю1",{{"Атрибут.1", type date}, {"Атрибут.2", type text}}),
    #"Разделить столбец по разделителю2" = Table.SplitColumn(#"Измененный тип1","Значение",Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv),{"Значение.1", "Значение.2"}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по разделителю2",{{"Значение.1", type text}, {"Значение.2", type text}}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Измененный тип2",{{"Атрибут.1", "Дата"}, {"Атрибут.2", "День недели"}, {"Значение.1", "Номер авто"}, {"Значение.2", "Тип авто"}}),
    #"Сортированные строки" = Table.Sort(#"Переименованные столбцы",{{"Дата", Order.Ascending}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Сортированные строки",{"Дата", "ФИО", "Перевозчик", "День недели", "Тип авто", "Номер авто"})
in
    #"Переупорядоченные столбцы"
Вот горшок пустой, он предмет простой...
 
PooHkrd, оу, спасибо большое, круто! Оч ждал Вас и, оказалось, не зря)
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Где вы, блин, такие исходники берете? Это ж башку сломать можно при заполнении.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Где вы, блин, такие исходники берете? Это ж башку сломать
PooHkrd, мне думается, пора отдельную ветку в курилке создавать - делиться исходниками, с которыми приходится работать. Эдакую кунсткамеру. Порой такую извращённую фантазию встречаешь, что ого-го! Причём, кода тебе такой файл показывают, этот процесс обязательно сопровождается фразой: "На самом деле, тут всё логично".
 
Да я даже без поллитры не смогу своему оператору объяснить как такое заполнять вручную, приучил к плоским таблицам на свою голову. А оказывается вон оно как надо!
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Где вы, блин, такие исходники берете?
Это система формирует такой отчет из имеющихся у нее данных. А вот кто его таким мог составить и что должно было происходить в голове у этого человека- я не могу понять.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
А, опять 1С с чудо клиентами: Хочу отчет чтобы было "красиво".
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
чтобы было "красиво".
И чтобы распечатать было удобно - главный критерий.
 
Цитата
alexleoix: пора отдельную ветку в курилке создавать — кунсткамеру
+++  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
PooHkrd, абсолютно согласен с Dyroff - действительно круто :)
Пытался сам сделать, но... не вышло
 
webley, да ладна-а-а, тут задачки такого типа раз в месяц всплывают регулярно. Сегодня вон Бахтиёр с точно такой же бедой обратился, только там чуть проще - столбцов по-меньше. Просто практики нужно много, чего без форума, кстати, тяжело добиться - задачи в основном решаешь одними и теми же шаблонами. А на таких вот ярких представителях идиотской структуры данных можно и голову чутка поломать. В данном случае, кстати, приведено одинковое количество строк на одно событие, а бывает что это количество разное - вот где засада.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
да ладна-а-а, тут задачки такого типа раз в месяц всплывают регулярно
ну... мое присутствие на форуме в последнее время достаточно эпизодично и нерегулярно, поэтому данное решение всё-равно кажется мне эффектным - сколько бы ни было подобных :)
 
Тоже потренировался. Почти всё кнопками, кроме кастомной группировки
Код
// Разбор
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Added Index" = Table.AddIndexColumn(#"Removed Top Rows", "Индекс", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Индекс", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Индекс"}, {{"t1", each Record.FromList(_[ФИО],{"ФИО","Организация"})},{"ttt", each Table.Transpose(Table.DemoteHeaders(Table.RemoveColumns(_,{"ФИО", "Индекс"}))), type table}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Индекс"}),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Removed Columns", "t1", {"ФИО", "Организация"}, {"ФИО", "Организация"}),
    #"Expanded {0}1" = Table.ExpandTableColumn(#"Expanded {0}", "ttt", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded {0}1",{{"ФИО", type text}, {"Организация", type text}, {"Column2", type text}, {"Column3", type text}, {"Column1", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Название дня", each Date.DayOfWeekName([Column1]), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Day Name",{{"Column1", "Дата"}, {"Column2", "Номер авто"}, {"Column3", "Тип авто"}, {"Название дня", "День недели"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Дата", "ФИО", "Организация", "День недели", "Тип авто", "Номер авто"})
in
    #"Removed Other Columns"
F1 творит чудеса
 
Максим Зеленский, Спасибо Вам за вариант) Оч хорошо получается!
Изменено: Dyroff - 12.09.2018 18:19:00
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
Страницы: 1
Наверх