Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Трансформация таблицы расписания в power query
 
#1
29.02.2020 09:03:06
Уважаемые форумчане,

Можно ли трансформировать таблицу с расписанием через PQ в удобный для использования и анализа вид?
Во вложении
1. входящий файл
2. результат(то есть то, что я хочу получить на выходе (на примере 1 каб)
 
 
 
#2
29.02.2020 10:56:35
extrafant, не совсем гладко получилось
Код
let
    Source = Excel.Workbook(File.Contents("C:\abc\Входящие данные.xlsx"), null, true),
    #"1_Sheet" = Source{[Item="1",Kind="Sheet"]}[Data],
    Add = Table.AddColumn(#"1_Sheet", "a", each if Value.Is([Column1], type number) then [Column1] else null),
    Add2 = Table.AddColumn(Add, "b", each if [a] = null and [Column1] <> "#" then [Column1] else null),
    Down = Table.PromoteHeaders(Table.Skip(Table.FillDown(Add2,{"b"}),6)),
    Filter = Table.SelectRows(Down, each [Column8] <> null and [Column8] <> ""),
    Remove = Table.RemoveColumns(Filter,{"#"}),
    Replace = Table.ReplaceValue(Remove,null," ",Replacer.ReplaceValue,{"Понедельник", "Вторник", "Среда", "Четверг", "Пятница", "Суббота"}),
    Unpivot = Table.UnpivotOtherColumns(Replace, {"Column8","101В"}, "Атрибут", "Значение"),
    Insert = Table.AddColumn(Unpivot, "Неделя", each Text.PositionOf(" ПоВтСрЧеПяСуВс",Text.Start([Атрибут], 2)), type number),
    Sort = Table.Sort(Insert,{{"Неделя", Order.Ascending}, {"101В", Order.Ascending}, {"Column8", Order.Ascending}}),
    Remove1 = Table.RemoveColumns(Sort,{"Неделя"}),
    Split = Table.SplitColumn(Remove1, "Значение", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Значение.1", "Значение.2", "Значение.3"})
in
    Split
НА МЯСО В ПРОФИЛЕ
 
 
 
#3
29.02.2020 11:05:10
Спасибо огромное! Я бегло посмотрел и вроде все хорошо! А что не гладко? Заголовки поправить не проблема вовсе. Какие там подводные камни, которых я не вижу?
 
 
 
#4
29.02.2020 11:09:34
Цитата
extrafant написал:
подводные камни
Надо следить чтоб 101В был первым в очереди :)  
НА МЯСО В ПРОФИЛЕ
 
 
 
#5
29.02.2020 11:11:44
А как там в запросе логика работает? Смысл в том, что тот каб, который первый по алфавиту должен быть первым в таблице? Если да, то входящие данные всегда так работаю и проблем не должно быть
 
 
 
#6
29.02.2020 13:10:07
Цитата
extrafant написал:
входящие данные всегда так работаю
Все верно
Код
let
    Source = 
        Excel.Workbook(
            File.Contents(
                "C:\abc\Входящие данные.xlsx"
            )
        )
                    {[Item="1",Kind="Sheet"]}
                        [Data],
    Filter = 
        Table.PromoteHeaders(
           Table.SelectRows(
               Table.ReplaceValue(
                   Table.FillDown(
                       Table.AddColumn(
                           Source, "b", 
                               each if not Value.Is([Column1], type number
                                           ) 
                                       and [Column1] <> "#" 
                                           then [Column1] else null
                       ),
                                       {"b"}
                  ),
                                           0,
                                           each [Column1],
                                               (a,b,c)=> 
                                           if c = "#" 
                                               then null else a,
                                           {"b"}      
               ), 
                                           each Value.Is([Column1], type number) or [b] = null  
           )
       ),
    Filter2 = 
        Table.SelectRows(
            Table.UnpivotOtherColumns(
                Table.ReplaceValue(
                    Filter,null," ",
                        Replacer.ReplaceValue,
                            Table.ColumnNames(Filter)
                ),
                                {"Column8","#"},
                                    "День","a"
            ),
                                         each  [Column8] <> " "
        )
    in  Table.SplitColumn(
            Table.RemoveColumns(
                Table.Sort( 
                    Table.AddColumn(
                        Filter2,
                            "b", 
                                each Text.PositionOf(" ПоВтСрЧеПяСуВс",
                                    Text.Start([День], 2)
                                    )
                    ),
                                        {{"b", 0}, 
                                            {"Column8", 0}, 
                                                {"#", 0}}
                ),
                                                    {"b"}
            ),
                                                        "a",
                                                            Splitter.SplitTextByDelimiter(", ",1),
                                                                {"Класс", "Учитель", "Предмет"}
        )
А че, модно получилось
Изменено: Михаил Л - 29.02.2020 20:01:20
НА МЯСО В ПРОФИЛЕ
 
 
 
#7
29.02.2020 15:22:03
Огромное спасибо!
 
 
 
#8
29.02.2020 16:53:17
еще вариант  
Код
let
    Source = 
        Excel.Workbook(
            Web.Contents(
                "https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=383076&action=download"
            ), 
            null, 
            true
        ){[Name="1"]}[Data],
    NeededRows = 
        Table.ToRows(
            Table.Skip(
                Source ,
                (r)=>not List.AnyTrue(
                    List.Transform(
                        {0..9},
                        each Text.Contains(
                            r[Column1],
                            Text.From(_)
                        )
                    )
                )
            )
        ),
    SplitToTables =
        List.Transform(
            List.RemoveMatchingItems(
                List.Split(
                    NeededRows,
                    19
                ),
                {{}}
            ),
            Table.FromRows
        ),
    Transform = 
        List.Transform(
            SplitToTables,
            (t)=>let 
                Cab = 
                    t[Column1]{0},
                Skip = 
                    Table.RemoveLastN(
                        Table.Skip(
                            t, 
                            each [Column1]<>"#"
                        ),
                        each [Column1]=null
                    ),
                Promote = 
                    Table.PromoteHeaders(
                        Skip,
                        [PromoteAllScalars=true]
                    ),
                Replace = 
                    Table.ReplaceValue(
                        Promote,
                        null,
                        [],
                        (a,b,c)=>
                            if a=b then c 
                            else Record.FromList(
                                     {Cab}&Text.Split(a,", "),
                                     {"Кабинет","Класс","Учитель","Предмет"}
                                 ),
                        List.Skip(Table.ColumnNames(Promote))), 
                unpivot = 
                    Table.UnpivotOtherColumns(
                        Table.Skip(Replace,1),
                        {"#"},
                        "День",
                        "Урок"
                    ) 
            in 
                Table.ExpandRecordColumn(
                    unpivot, 
                    "Урок", 
                    {"Кабинет","Класс","Учитель","Предмет"}
                )
        ),
    Result = 
        Table.Combine(Transform)
    
in
    Result
 
 
 
#9
29.02.2020 16:58:42
Уважаемый Андрей, а в чем принципиальное отличие? В принципе, и 1 вариант работает! Я глянул и пока багов не нашел! Есть ли смысл изучить второй Ваш вариант?
 
 
 
#10
29.02.2020 17:11:24
и еще до кучи
Код
let
    Source = 
        Excel.Workbook(
            Web.Contents(
                "https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=383076&action=download"
            ), 
            null, 
            true
        ){[Name="1"]}[Data],
    NeededRows = 
        Table.Skip(
                Source ,
                (r)=>not List.AnyTrue(
                    List.Transform(
                        {0..9},
                        each Text.Contains(
                            r[Column1],
                            Text.From(_)
                        )
                    )
                )
            ),
    Grouped = 
        Table.Group(
            NeededRows, 
            {"Column1"}, 
            {
                { "t", 
                  each let 
                    Skip = 
                        Table.RemoveLastN(
                            Table.Skip(
                                _, 
                                each [Column1]<>"#"
                            ),
                            each [Column1]=null
                        ),
                    Promote = 
                        Table.PromoteHeaders(
                            Skip,
                            [PromoteAllScalars=true]
                        ),
                    Replace = 
                        Table.ReplaceValue(
                            Promote,
                            null,
                            [],
                            (a,b,c)=>
                                if a=b then c 
                                else Record.FromList(
                                         Text.Split(a,", "),
                                         {"Класс","Учитель","Предмет"}
                                     ),
                            List.Skip(Table.ColumnNames(Promote))), 
                    unpivot = 
                        Table.UnpivotOtherColumns(
                            Table.Skip(Replace,1),
                            {"#"},
                            "День",
                            "Урок"
                        ) 
                  in 
                    Table.ExpandRecordColumn(
                        unpivot, 
                        "Урок", 
                        {"Класс","Учитель","Предмет"}
                    ) 
                }
            },
            0,
            (a,b)=>
                Number.From(
                    Value.Is(
                        b[Column1],
                        type text
                    ) 
                    and b[Column1]<>"#"
                )
        ),
    Result = 
        Table.RenameColumns(
            Table.ExpandTableColumn(
                Grouped, 
                "t", 
                {"#","День","Класс","Учитель","Предмет"}
            ),
            {
                {
                  "Column1",
                  "Кабинет"
                }
            }
        )
       
in
    Result
 
 
 
#11
29.02.2020 17:13:08
В чем различие методов? В плане логики работы
 
 
 
#12
29.02.2020 20:30:49
В плане логики и, возможно, скорости работы
 
 
 
Страницы: 1
Наверх