PQ преобразовать объединенный запрос с файла на папку файлов

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » PQ преобразовать объединенный запрос с файла на папку файлов (Макросы/Sub)

Anis625

Дата: Вторник, 27.08.2019, 17:15 | Сообщение № 1

Группа: Заблокированные

Ранг: Ветеран

Сообщений: 674

Репутация: 31 ±

Замечаний: 20% ±

Excel 2013

Добрый вечер, участникам форума!

Обращаюсь как начинающий пользователь PQ.
У меня получается пока преобразовывать ОДИН запрос в функцию для последующего обращения к папке.

Задача усложнилась когда объединил два запроса, и вот где менять переменную правильно не знаю. Попробовал как примерно понимаю, но увы получилась каша (работал на копии).

Подскажите пожалуйста где правильно менять переменные в коде в двух запросах?

К сообщению приложен файл: ___.xlsx (27.8 Kb) · ___.-_2019_-1_-.xlsm (95.0 Kb)

Ответить

Anis625

Дата: Понедельник, 02.09.2019, 15:39 | Сообщение № 2

Группа: Заблокированные

Ранг: Ветеран

Сообщений: 674

Репутация: 31 ±

Замечаний: 20% ±

Excel 2013

У меня получается только первую часть преобразовать в формулу:

[vba]

Код

let
    Источник = (filepath)=>
let
    Источник = Excel.Workbook(File.Contents(filepath), null, true),
    #"Мониторинг КП_ежекв_Sheet" = Источник{[Item="Мониторинг КП_ежекв",Kind="Sheet"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Мониторинг КП_ежекв_Sheet",{"Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Другие удаленные столбцы", each ([Column2] <> null) and ([Column4] <> null) and ([Column3] <> "-")),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Строки с примененным фильтром", [PromoteAllScalars=true]),
    Пользовательская1 = Table.SelectRows(#"Другие удаленные столбцы", each ([Column3] <> null) ),
    #"Другие удаленные столбцы1" = Table.SelectColumns(Пользовательская1,{"Column4"}),
    L_ = #"Другие удаленные столбцы1"[Column4],
    L_f = List.LastN(List.FirstN(L_,1),1),
    Пользовательская2 = Table.AddColumn(#"Повышенные заголовки", "ФИО", each L_f{0}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(Пользовательская2,{"ФИО", "№#(lf)п.п.", "Наименование проекта", "Заказчик / Инициатор#(lf)проекта#(lf)(И.О. Фамилия)", "Куратор#(lf)проекта#(lf)(И.О. Фамилия)", "Руководитель#(lf)проекта#(lf)(И.О. Фамилия)"})
in
    #"Переупорядоченные столбцы"
in
    Источник

[/vba]

А как связать полученную формулу во второй части не получается сделать:

[vba]

Код

let
    Источник = (filepath)=>
let
    Источник = Excel.Workbook(File.Contents(filepath), null, true),
    #"Оценка проектов (по году)_Sheet" = Источник{[Item="Оценка проектов (по году)",Kind="Sheet"]}[Data],
    #"Строки с примененным фильтром" = Table.SelectRows(#"Оценка проектов (по году)_Sheet", each ([Column3] <> null) and ([Column3] <> "-") and ([Column5] <> null) and ([Column3] <> 2)),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Строки с примененным фильтром",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column11", "Column12", "Column18"}),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Другие удаленные столбцы", [PromoteAllScalars=true]),
    #"Переименованные столбцы" = Table.RenameColumns(#"Повышенные заголовки",{{"Column5", "Качество балл (факт)"}, {"Column7", "График балл (факт)"}}),
    Пользовательская1 = Table.SelectRows(#"Оценка проектов (по году)_Sheet", each ([Column4] <> null)),
    L_ = #"Пользовательская1"[Column4],
    L_f = List.LastN(List.FirstN(L_,1),1),
    #"Добавил ФИО" = Table.AddColumn(#"Переименованные столбцы", "ФИО", each L_f{0}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Добавил ФИО",{"ФИО", "№ п.п.", "Наименование проекта", "Вес проекта,#(lf)% ", "Качество / Результат проекта #(lf)(балл)", "Качество балл (факт)", "График#(lf)(балл)", "График балл (факт)", "Уд.вес по#(lf)критерию#(lf)качество,#(lf)%*", "Уд.вес по#(lf)критерию#(lf)график,#(lf)%*", "Общая#(lf)оценка по#(lf)критериям#(lf)КГ#(lf)(балл)"}),
    #"Объединенные запросы" = Table.NestedJoin(#"Переупорядоченные столбцы",{"ФИО", "№ п.п.", "Наименование проекта"},GetData,{"ФИО", "№#(lf)п.п.", "Наименование проекта"},"GetData",JoinKind.LeftOuter),
    #"Развернутый элемент GetData" = Table.ExpandTableColumn(#"Объединенные запросы", "GetData", {"Заказчик / Инициатор#(lf)проекта#(lf)(И.О. Фамилия)", "Куратор#(lf)проекта#(lf)(И.О. Фамилия)", "Руководитель#(lf)проекта#(lf)(И.О. Фамилия)"}, {"GetData.Заказчик / Инициатор#(lf)проекта#(lf)(И.О. Фамилия)", "GetData.Куратор#(lf)проекта#(lf)(И.О. Фамилия)", "GetData.Руководитель#(lf)проекта#(lf)(И.О. Фамилия)"})
in
    #"Развернутый элемент GetData"
in
    Источник

[/vba]

Все ломается

У меня получается только первую часть преобразовать в формулу:

[vba]

Код

[/vba]

А как связать полученную формулу во второй части не получается сделать:

[vba]

Код

[/vba]

Все ломаетсяAnis625

Ответить

СообщениеУ меня получается только первую часть преобразовать в формулу:

[vba]

Код

[/vba]

А как связать полученную формулу во второй части не получается сделать:

[vba]

Код

[/vba]

Все ломается

Автор - Anis625
Дата добавления - 02.09.2019 в 15:39

krosav4ig

Дата: Вторник, 03.09.2019, 02:00 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Здравствуйте
[vba]

Код

let
    fx=(FileContents) => let
        fn =(t as table, SheetName as text, optional NeededColumns as nullable list, optional Filter as nullable text, optional ColumnsToRename as nullable list)=>let
            Sheet      = t{[Item=SheetName,Kind="Sheet"]}[Data],
            NeededCol  = if NeededColumns<>null then Table.SelectColumns(Sheet,NeededColumns) else Sheet,
            Filtered   = if Filter<>null then Table.SelectRows(NeededCol, each Expression.Evaluate(Filter,[_=_])) else NeededCol,
            Promoted   = Table.PromoteHeaders(Filtered, [PromoteAllScalars=true]),
            RenameCol  = if ColumnsToRename<>null then Table.RenameColumns(Promoted,ColumnsToRename) else 1,
            FullName   = NeededCol{[Column3="ФИО работника:"]}[Column4],
            AddName    = Table.AddColumn(RenameCol, "ФИО", each FullName)
        in Table.ReorderColumns(AddName,{"ФИО"}&Table.ColumnNames(RenameCol)),
        Мониторинг_КП_ежекв = fn(
            FileContents,
            "Мониторинг КП_ежекв",
            {"Column2", "Column3", "Column4", "Column5", "Column6"},
            "([Column2] <> null) and ([Column4] <> null) and ([Column3] <> ""-"")",
            {{"№#(lf)п.п.", "№ п.п."}}
        ),
        Оценка_проектов_по_году = fn(
            FileContents,
            "Оценка проектов (по году)",
            {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column11", "Column12", "Column18"},
            "([Column3] <> null) and ([Column3] <> ""-"") and ([Column5] <> null) and ([Column3] <> 2)",
            {{"Column5", "Качество балл (факт)"}, {"Column7", "График балл (факт)"}}
        ),
        Columns = {"ФИО", "№ п.п.", "Наименование проекта"},
        Join = Table.NestedJoin(Оценка_проектов_по_году,Columns,Мониторинг_КП_ежекв,Columns,"GetData",JoinKind.LeftOuter)
    in Table.ExpandTableColumn(Join, "GetData", List.Difference(Table.ColumnNames(Мониторинг_КП_ежекв),Table.ColumnNames(Оценка_проектов_по_году))),
    Folder=Table.SelectRows(Folder.Files("D:\x"),each (not [Attributes][NotContentIndexed]) and ([Attributes][Kind]="Excel File"))[[#"Folder Path"],[Name],[Content]],
    Transform = Table.TransformColumns(Folder, {{"Content",each fx(Table.Buffer(Excel.Workbook(_, null, true)))}})
in
    Transform

[/vba]

Здравствуйте
[vba]

Код

[/vba]krosav4ig

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Ответить

СообщениеЗдравствуйте
[vba]

Код

[/vba]

Автор - krosav4ig
Дата добавления - 03.09.2019 в 02:00

Anis625

Дата: Вторник, 03.09.2019, 15:37 | Сообщение № 4

Группа: Заблокированные

Ранг: Ветеран

Сообщений: 674

Репутация: 31 ±

Замечаний: 20% ±

Excel 2013

krosav4ig,
Спасибо Вам за помощь с PQ у меня он почти сработал. Когда готовил файл выложить на форум то в двух ячейках (в целях сохранения конфиденциальности) поменял название (похоже перестарался). Сейчас их в коде меняю на правильное то ругается. Выдается ошибку на название столбца.

К сообщению приложен файл: -2019-1-.xlsm (95.1 Kb)

Ответить

krosav4ig

Дата: Среда, 04.09.2019, 17:21 | Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

[vba]

Код

let
    fx=(FileContents) => let
        fn =(t as table, SheetName as text, optional NeededColumns as nullable list, optional Filter as nullable text, optional ColumnsToRename as nullable list)=>let
            Sheet      = t{[Item=SheetName,Kind="Sheet"]}[Data],
            NeededCol  = if NeededColumns<>null then Table.SelectColumns(Sheet,NeededColumns) else Sheet,
            Filtered   = if Filter<>null then Table.SelectRows(NeededCol, each Expression.Evaluate(Filter,[_=_])) else NeededCol,
            Promoted   = Table.PromoteHeaders(Filtered, [PromoteAllScalars=true]),
            RenameCol  = if ColumnsToRename<>null then List.Accumulate(ColumnsToRename,Promoted,(a,b)=>try Table.RenameColumns(a,b) otherwise a) else Promoted,
            FullName   = NeededCol{[Column3="ФИО работника:"]}[Column4],
            AddName    = Table.AddColumn(RenameCol, "ФИО", each FullName)
        in Table.ReorderColumns(AddName,{"ФИО"}&Table.ColumnNames(RenameCol)),
        Мониторинг_КП_ежекв = fn(
            FileContents,
            "Мониторинг КП_ежекв",
            {"Column2", "Column3", "Column4", "Column5", "Column6"},
            "([Column2] <> null) and ([Column4] <> null) and ([Column3] <> ""-"")"
        ),
        Оценка_проектов_по_году = fn(
            FileContents,
            "Оценка проектов (по году)",
            {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column11", "Column12", "Column18"},
            "([Column3] <> null) and ([Column3] <> ""-"") and ([Column5] <> null) and ([Column3] <> 2)",
            {{"Column5", "Качество балл (факт)"}, {"Column7", "График балл (факт)"}}
        ),
        Columns = List.Transform({Оценка_проектов_по_году,Мониторинг_КП_ежекв},each List.Range(Table.ColumnNames(_),0,3)),
        Join    = Table.NestedJoin(Оценка_проектов_по_году,Columns{0},Мониторинг_КП_ежекв,Columns{1},"GetData",JoinKind.LeftOuter)
    in Table.ExpandTableColumn(Join, "GetData", List.Difference(Table.ColumnNames(Мониторинг_КП_ежекв),Table.ColumnNames(Оценка_проектов_по_году)&List.Combine(Columns))),
    Folder    = Table.SelectRows(Folder.Files("D:\x"),each (not [Attributes][NotContentIndexed]) and ([Attributes][Kind]="Excel File"))[[#"Folder Path"],[Name],[Content]],
    Transform = Table.TransformColumns(Folder, {{"Content",each fx(Table.Buffer(Excel.Workbook(_, null, true)))}})
in
    Transform

[/vba]

[vba]

Код

let
    fx=(FileContents) => let
        fn =(t as table, SheetName as text, optional NeededColumns as nullable list, optional Filter as nullable text, optional ColumnsToRename as nullable list)=>let
            Sheet      = t{[Item=SheetName,Kind="Sheet"]}[Data],
            NeededCol  = if NeededColumns<>null then Table.SelectColumns(Sheet,NeededColumns) else Sheet,
            Filtered   = if Filter<>null then Table.SelectRows(NeededCol, each Expression.Evaluate(Filter,[_=_])) else NeededCol,
            Promoted   = Table.PromoteHeaders(Filtered, [PromoteAllScalars=true]),
            RenameCol  = if ColumnsToRename<>null then List.Accumulate(ColumnsToRename,Promoted,(a,b)=>try Table.RenameColumns(a,b) otherwise a) else Promoted,
            FullName   = NeededCol{[Column3="ФИО работника:"]}[Column4],
            AddName    = Table.AddColumn(RenameCol, "ФИО", each FullName)
        in Table.ReorderColumns(AddName,{"ФИО"}&Table.ColumnNames(RenameCol)),
        Мониторинг_КП_ежекв = fn(
            FileContents,
            "Мониторинг КП_ежекв",
            {"Column2", "Column3", "Column4", "Column5", "Column6"},
            "([Column2] <> null) and ([Column4] <> null) and ([Column3] <> ""-"")"
        ),
        Оценка_проектов_по_году = fn(
            FileContents,
            "Оценка проектов (по году)",
            {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column11", "Column12", "Column18"},
            "([Column3] <> null) and ([Column3] <> ""-"") and ([Column5] <> null) and ([Column3] <> 2)",
            {{"Column5", "Качество балл (факт)"}, {"Column7", "График балл (факт)"}}
        ),
        Columns = List.Transform({Оценка_проектов_по_году,Мониторинг_КП_ежекв},each List.Range(Table.ColumnNames(_),0,3)),
        Join    = Table.NestedJoin(Оценка_проектов_по_году,Columns{0},Мониторинг_КП_ежекв,Columns{1},"GetData",JoinKind.LeftOuter)
    in Table.ExpandTableColumn(Join, "GetData", List.Difference(Table.ColumnNames(Мониторинг_КП_ежекв),Table.ColumnNames(Оценка_проектов_по_году)&List.Combine(Columns))),
    Folder    = Table.SelectRows(Folder.Files("D:\x"),each (not [Attributes][NotContentIndexed]) and ([Attributes][Kind]="Excel File"))[[#"Folder Path"],[Name],[Content]],
    Transform = Table.TransformColumns(Folder, {{"Content",each fx(Table.Buffer(Excel.Workbook(_, null, true)))}})
in
    Transform

[/vba]krosav4ig

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Ответить

Сообщение[vba]

Код

let
    fx=(FileContents) => let
        fn =(t as table, SheetName as text, optional NeededColumns as nullable list, optional Filter as nullable text, optional ColumnsToRename as nullable list)=>let
            Sheet      = t{[Item=SheetName,Kind="Sheet"]}[Data],
            NeededCol  = if NeededColumns<>null then Table.SelectColumns(Sheet,NeededColumns) else Sheet,
            Filtered   = if Filter<>null then Table.SelectRows(NeededCol, each Expression.Evaluate(Filter,[_=_])) else NeededCol,
            Promoted   = Table.PromoteHeaders(Filtered, [PromoteAllScalars=true]),
            RenameCol  = if ColumnsToRename<>null then List.Accumulate(ColumnsToRename,Promoted,(a,b)=>try Table.RenameColumns(a,b) otherwise a) else Promoted,
            FullName   = NeededCol{[Column3="ФИО работника:"]}[Column4],
            AddName    = Table.AddColumn(RenameCol, "ФИО", each FullName)
        in Table.ReorderColumns(AddName,{"ФИО"}&Table.ColumnNames(RenameCol)),
        Мониторинг_КП_ежекв = fn(
            FileContents,
            "Мониторинг КП_ежекв",
            {"Column2", "Column3", "Column4", "Column5", "Column6"},
            "([Column2] <> null) and ([Column4] <> null) and ([Column3] <> ""-"")"
        ),
        Оценка_проектов_по_году = fn(
            FileContents,
            "Оценка проектов (по году)",
            {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column11", "Column12", "Column18"},
            "([Column3] <> null) and ([Column3] <> ""-"") and ([Column5] <> null) and ([Column3] <> 2)",
            {{"Column5", "Качество балл (факт)"}, {"Column7", "График балл (факт)"}}
        ),
        Columns = List.Transform({Оценка_проектов_по_году,Мониторинг_КП_ежекв},each List.Range(Table.ColumnNames(_),0,3)),
        Join    = Table.NestedJoin(Оценка_проектов_по_году,Columns{0},Мониторинг_КП_ежекв,Columns{1},"GetData",JoinKind.LeftOuter)
    in Table.ExpandTableColumn(Join, "GetData", List.Difference(Table.ColumnNames(Мониторинг_КП_ежекв),Table.ColumnNames(Оценка_проектов_по_году)&List.Combine(Columns))),
    Folder    = Table.SelectRows(Folder.Files("D:\x"),each (not [Attributes][NotContentIndexed]) and ([Attributes][Kind]="Excel File"))[[#"Folder Path"],[Name],[Content]],
    Transform = Table.TransformColumns(Folder, {{"Content",each fx(Table.Buffer(Excel.Workbook(_, null, true)))}})
in
    Transform

[/vba]

Автор - krosav4ig
Дата добавления - 04.09.2019 в 17:21

Anis625

Дата: Четверг, 05.09.2019, 11:45 | Сообщение № 6

Группа: Заблокированные

Ранг: Ветеран

Сообщений: 674

Репутация: 31 ±

Замечаний: 20% ±

Excel 2013

krosav4ig,

Честно круто, то что нужно.

У меня получилось реализовать не самым изящным способом: первый запрос отработал перевел в функцию и применил к папке (пример выше), второй запрос сделал также и потом сделал их объединение в новый запрос. Этот способ пока для меня более понятный. Учился у Sboy за что ему большое спасибо.

Сразу писать в расширенном редакторе на языке М (вроде так называется) пока не получается ... точнее не все получается как у вас. Или ваш код вы тоже вначале отработали преднастроенными командами в PQ?

Ответить

Страница 1 из 1
1