Страницы: 1
RSS
Преобразование табеля в плоскую таблицу с помощью Power Query
 
Добрый день! Коллеги, прошу помощи в решении задачи. Есть типовой табель, который нужно преобразовать в плоскую таблицу. Файл во вложении. Кое что уже сделал самостоятельно, осталось к столбцам , ФИО, Табельный номер добавить 3 столбца Дата, Вид рабочего времени и Табельное время. Последние 2 можно разделить по маркеру "Столбцы", а дата это столбцы с 1 по 31.

Вроде бы с помощью инструмента "Отменить свертывание столбцов" должно все решаться, но без дополнительных преобразований не удается добиться желаемого результата.
Изменено: Murderface_ - 26.11.2019 10:06:02
 
Цитата
Murderface_ написал:
добиться желаемого результата
какой он, желаемый результат?
может так достаточно?
Код
= Table.UnpivotOtherColumns(#"Переупорядоченные столбцы", {"№", "ФИО", "Табельный номер", "Столбцы"}, "Атрибут", "Значение")
 
Цитата
artyrH написал:
какой он, желаемый результат?
вот такой
Цитата
Murderface_ написал:
к столбцам №, ФИО, Табельный номер добавить 3 столбца Дата, Вид рабочего времени и Табельное время
Цитата
artyrH написал:
может так достаточно?
К сожалению, нет. В вашем варианте вид рабочего времени и табельное время в одном столбце, а необходимо, чтобы они были в 2х разных столбцах.
 
Murderface_, добавил формулу отсюда
Код
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Переупорядоченные столбцы", {"№", "ФИО", "Табельный номер", "Столбцы"}, "Атрибут", "Значение"),
    Custom1 = Table.SplitColumn(#"Unpivoted Other Columns", "Значение", (x)=>let a = try Number.From(x) otherwise null
                                                                in {if a = null then x else null, a},
                                                           {"вид рабочего времени", "табельное время"})
in
    Custom1
 
artyrH, это не совсем то, что нужно. Вид рабочего времени и табельное время в этом случае находятся в разных строках и дальнейшее их сопоставление невозможно.
 
Цитата
Murderface_ написал:
находятся в разных строках
в начальной таблице они и в разных столбцах.
потому и спрашивал про желаемый результат. наглядный желаемый результат, заполненный вручную
 
artyrH, я обновил в первом сообщении файл с примером, добавил лист "желаемый результат".
 
после шага Строки с примененным фильтром
Код
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Строки с примененным фильтром", {"№", "ФИО", "Табельный номер", "Столбцы"}, "Атрибут", "Значение"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Значение", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"№", "ФИО", "Табельный номер","Атрибут"},  {{"Co", each Text.Combine(_[Значение], "  "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Co", Splitter.SplitTextByDelimiter("  ", QuoteStyle.Csv), {"табельное время","вид рабочего времени"})
in
    #"Split Column by Delimiter"
 
artyrH, круто, спасибо! Я пытался решить по другому, через объединение столбцов а затем через транспонирование таблицы, но не вышло(
 
Так?
Код
let
    Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="source"]}[Content]{0}[Column1]), null, true){[Item="табель",Kind="Sheet"]}[Data],
    FilledDown = Table.FillDown(Source,{"Column1", "Column2", "Column3"}),
    Skipped = Table.Skip(FilledDown,10),
    SelectedColumns = Table.SelectColumns(Skipped,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35"}),
    PromotedHeaders = Table.PromoteHeaders(SelectedColumns, [PromoteAllScalars=true]),
    GroupedRows = Table.Group(PromotedHeaders, {"№ п/п", "Фамилия И.О.", "Табельный номер"}, {{"tab", each Table.Transpose( Table.DemoteHeaders( Table.RemoveColumns( _,{"№ п/п", "Фамилия И.О.", "Табельный номер"}) ) ), type table}}),
    Expanded = Table.ExpandTableColumn(GroupedRows, "tab", {"Column1", "Column5", "Column3"}, {"Дата", "Вид рабочего времени", "Табельное время"})
in
    Expanded
Вот горшок пустой, он предмет простой...
 
PooHkrd, да, спасибо! Очень круто) Жаль мышкой такое не сделать.
 
Цитата
Murderface_ написал:
Жаль мышкой такое не сделать.
Было дело, при приеме на работу новичка на должность оператора или специалиста отключал ему мышку от компа и пока не научится работать с Экселем без неё не отдавал обратно. Попробуйте - будет интересный для вас опыт.   ;)
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Попробуйте
Попробую  ;)

Получилось решить задачу с помощью мышки. Но для себя возьму вариант PooHkrd. Прилагаю файл со всеми 3 решениями, может кому то будет полезно.
 
Цитата
Murderface_ написал:
Попробую
Только не воспринимайте это буквально.  :D Лично я работаю в режиме: накликал кнопками и доработал напильником.
Скажем надо преобразовать столбец, и я в принципе знаю как написать формулу, но мне лень, поэтому я тыкаю в "добавить префикс", а потом в строке формул или в редакторе изменяю формулу обработки элементов столбца.
Или скажем надо написать функцию обработки таблицы при группировки, типа той, что сделал выше, тогда я детализирую одну из таблиц, измываюсь над ней кнопками, а потом получившиеся функции вкладываю друг в друга, и в результате в группировку.
Так что мышку я не выкидываю, но понимать синтаксис и знать как работают самые нужные функции нужно наизусть, для всего остального есть справочник.
Вот горшок пустой, он предмет простой...
 
немного мозговыноса
Код
let
    Источник    = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="source"]}[Content]{0}[Column1]), null, true){[Item="табель",Kind="Sheet"]}[Data],
    Date        = Excel.CurrentWorkbook(){[Name="date"]}[Content]{0}[Column1],
    Headers     = Table.ToRows(Table.TransformColumns(Table.Transpose(Table.Range(Источник,3,8)),List.Zip({List.Transform({1..8},each "Column"&Text.From(_)),List.Repeat({each Text.From(_)},8)}))),
    NewHeaders  = List.Generate(()=>[i=0,r=List.RemoveLastN(Headers{i},each _=null),n=0,v=r],each [i]<List.Count(Headers),each [i=[i]+1,r=List.RemoveLastN(Headers{i},each _=null),n=List.PositionOf(r,null,0,(a,b)=>a<>b),v=let prev=[v] in List.Generate(()=>[i=0,s=if r{i}=null or r{i}="Из них по причинам" then prev{i} else r{i}], each [i]<List.Count(r),each [i=[i]+1,s=if r{i}=null or r{i}="В т.ч." then prev{i} else r{i}], each try if [s]="Из них" then null else [s] otherwise null)],each Text.Combine([v],"|")),
    AddHeaders  = Table.PromoteHeaders(Table.InsertRows(Table.Skip(Источник,11),0,{Record.FromList(NewHeaders,Table.ColumnNames(Источник))})),
    FilledDown  = Table.FillDown(AddHeaders,{"№ п/п", "Фамилия И.О.", "Табельный номер"}),
    ReplaceNull = Table.ReplaceValue(FilledDown,null,"",Replacer.ReplaceValue,Table.ColumnNames(FilledDown)),
    Indexed     = Table.TransformColumns(Table.AddIndexColumn(ReplaceNull, "Индекс", 1),{{"Индекс", each Number.Mod(_-1, 4)+1, type number}}),
    Unpivot     = Table.UnpivotOtherColumns(Indexed, {"№ п/п", "Фамилия И.О.", "Табельный номер","Индекс"}, "Атрибут", "Значение"),
    ToDate      = Table.SplitColumn(Unpivot, "Атрибут", each {try #date(Date.Year(Date),Date.Month(Date),Number.From(Text.AfterDelimiter(_,"|"))) otherwise null}, {"Дата"}),
    Group       = Table.Group(ToDate, {"№ п/п", "Фамилия И.О.", "Табельный номер", "Дата"}, {{"_",(t)=>let Rows=List.RemoveMatchingItems(List.Split(List.Transform({3,1,4,2},each t{[Индекс=_]}?[Значение]?),2),{{}}) in Table.SelectRows(Table.FromRows(Rows,{"Вид рабочего времени","Табельное время"}), each [Вид рабочего времени]<>null and [Вид рабочего времени]<>""), type table}}),
    Filtered    = Table.SelectRows(Group, each ([Дата] <> null) and Table.RowCount([_])>0),
    Expand      = Table.ExpandTableColumn(Filtered, "_", {"Вид рабочего времени", "Табельное время"}),
    Result      = Table.ReplaceValue(Expand,"",null,Replacer.ReplaceValue,Table.ColumnNames(Expand))
in
    Result
 
Цитата
Андрей Лящук написал:
немного мозговыноса
Для меня так точно  :D . Особенно на шаге NewHeaders. Спасибо.
Страницы: 1
Наверх