Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Определенные имена столбцов в Power Query в любые в зависимости от источников
 
#1
24.01.2023 11:13:41
Добрый день, уважаемые форумчане!
У меня вопрос: есть возможность изменить код Power Query, который собирает информацию с множества книг Excel,
(который нам любезно показал Николай  :)) , чтобы не было жёсткой привязки к наименованию столбцов. Так как они в других файлах будут изменены, например: Н1 план, на Н2 план и т.д.
Буду очень благодарен!
Вот код:
Код
let
    Источник = Folder.Files("C:\Users\Desktop\объмы"),
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Пользовательская", each getData([Folder Path]&[Name])),
    #"Замененное значение" = Table.ReplaceValue(#"Добавлен пользовательский объект",".xlsx","",Replacer.ReplaceText,{"Name"}),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Замененное значение", "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Name.1", "Name.2"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Измененный тип", "Name.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Name.1.1", "Name.1.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю1",{{"Name.1.1", type text}, {"Name.1.2", type text}}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Измененный тип1",{"Name.1.2", "Name.2", "Пользовательская"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Другие удаленные столбцы",{{"Name.1.2", "Подрядчик"}, {"Name.2", "Месяц"}}),
    #"Развернутый элемент Пользовательская" = Table.ExpandTableColumn(#"Переименованные столбцы", "Пользовательская", {"Секция", "Наименование работ", "Кол-во по договору", "План на отчетный месяц", "Итоговый факт на месяц  ", "план на дату", "факт на дату", "Отклонение", "Н1 план", "Н1 факт", "Н1 Отклонение", "Н2 план", "Н2 факт", "Н2 Отклонение", "Н3 план", "Н3 факт", "Н3 Отклонение", "Н4 план", "Н4 факт", "Н4 Отклонение"}, {"Секция", "Наименование работ", "Кол-во по договору", "План на отчетный месяц", "Итоговый факт на месяц  ", "план на дату", "факт на дату", "Отклонение", "Н1 план", "Н1 факт", "Н1 Отклонение", "Н2 план", "Н2 факт", "Н2 Отклонение", "Н3 план", "Н3 факт", "Н3 Отклонение", "Н4 план", "Н4 факт", "Н4 Отклонение"})
in
    #"Развернутый элемент Пользовательская"
 
 
 
#2
24.01.2023 12:08:44
Код
#"Переименованные столбцы" = Table.RenameColumns(#"Другие удаленные столбцы",{{"Name.1.2", "Подрядчик"}, {"Name.2", "Месяц"}}),
#"Развернутый элемент Пользовательская" =
     Table.ExpandTableColumn(
          #"Переименованные столбцы",
          "Пользовательская",
          Table.ColumnNames(#"Переименованные столбцы"[Пользовательская]{0})
    )

это если в колонке "Пользовательская" сидят таблицы с одинаковыми именами колонок. А так см ответ Дмитрий(The_Prist) Щербаков ниже.

Ну или пройтись по этой колоночке предварительно на предмет сбора всех имен колонок таблиц, которые там есть.

Код
List.Distinct(
   List.Combine(
      List.Transform(
          #"Переименованные столбцы"[Пользовательская],
          Table.ColumnNames
      )
   )
)
Изменено: Alien Sphinx - 24.01.2023 12:24:46
Пришелец-прораб.
 
 
 
#3
24.01.2023 12:11:23
Цитата
Tamagafk написал:
чтобы не было жёсткой привязки к наименованию столбцов
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#4
25.01.2023 11:27:05
Цитата
написал:
Цитата
Tamagafk написал:
чтобы не было жёсткой привязки к наименованию столбцов
 Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Отличный способ! Спасибо за информацию. Но, если я правильно понимаю, в книгах эксель, первая строка является Заголовками столбцов. А как быть, если в моих таблицах, Заголовки столбцов начинаются с 7 строки. Из-за чего все заголовки из всех книг, попадают в область данных. Прошу простить, если не смог понятно объяснить. Во вложении, пример того, что получается и что надо, чтобы получилось)
 
 
 
#5
31.01.2023 08:14:10
Добрый день, уважаемые знатоки. Всё перепробовал, что знаю. Не получается результат приложенных в предыдущем сообщении. Хелп ми! :cry:  
 
 
 
#6
31.01.2023 08:18:11
Tamagafk, а где пара файлов с данными для примера и Ваш запрос, который возвращает неподходящий результат?
 
 
 
#7
31.01.2023 15:08:51
Цитата
написал:
Tamagafk, а где пара файлов с данными для примера и Ваш запрос, который возвращает неподходящий результат?
surkenny, вот два примера файлов и мой запрос:
Код
let
    Источник = Folder.Files("C:\Users\Desktop\объмов"),
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Данные", each Excel.Workbook([Content])),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Добавлен пользовательский объект",{"Name", "Данные"}),
    #"Замененное значение" = Table.ReplaceValue(#"Другие удаленные столбцы",".xlsx","",Replacer.ReplaceText,{"Name"}),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Замененное значение", "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Name.1", "Name.2"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Измененный тип", "Name.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Name.1.1", "Name.1.2"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю1",{{"Name.1.1", type text}, {"Name.1.2", type text}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип1",{"Name.1.1"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Name.1.2", "Подрядчик"}, {"Name.2", "Месяц"}}),
    #"Развернутый элемент Данные" = Table.ExpandTableColumn(#"Переименованные столбцы", "Данные", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Развернутый элемент Данные", each ([Kind] = "Sheet")),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Name", "Item", "Kind", "Hidden"}),
    ColNames = Table.AddColumn(#"Удаленные столбцы1", "cols", each Table.ColumnNames([Data]), type list),
    allCols = List.Sort(List.Distinct(List.Combine(ColNames[cols]))),
    #"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", allCols, allCols),
    #"Удаленные столбцы2" = Table.RemoveColumns(#"Развернутый элемент Data",{"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
    #"Строки с примененным фильтром1" = Table.SelectRows(#"Удаленные столбцы2", each [Column4] <> null and [Column4] <> ""),
    #"Удаленные столбцы3" = Table.RemoveColumns(#"Строки с примененным фильтром1",{"Column2", "Column25", "Column26", "Column27", "Column28", "Column29", "Column3", "Column30", "Column31", "Column32", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column47", "Column48", "Column49", "Column5", "Column50", "Column51", "Column52", "Column53", "Column54", "Column58", "Column59", "Column6", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82"}),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Удаленные столбцы3", "Наименование работ", each [Column7]&", "&[Column8]),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Добавлен пользовательский объект1",{"Подрядчик", "Месяц", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column33", "Column34", "Column35", "Column4", "Column44", "Column45", "Column46", "Column55", "Column56", "Column57", "Column66", "Column67", "Column68", "Наименование работ", "Column7", "Column8", "Column9"}),
    #"Удаленные столбцы4" = Table.RemoveColumns(#"Переупорядоченные столбцы",{"Column7", "Column8"}),
    #"Переупорядоченные столбцы1" = Table.ReorderColumns(#"Удаленные столбцы4",{"Подрядчик", "Месяц", "Наименование работ", "Column4", "Column9", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column33", "Column34", "Column35", "Column44", "Column45", "Column46", "Column55", "Column56", "Column57", "Column66", "Column67", "Column68"})
in
    #"Переупорядоченные столбцы1"
 
 
 
#8
01.02.2023 00:06:22
Код
let
    f = (x) => [a = Excel.Workbook(x){[Kind="Sheet"]}[Data],
        b = Table.PromoteHeaders(Table.Skip(a ,6)),
        c = Table.SelectRows(b, (x)=> (x[Секция] <> null))][c],
    f1 = List.Buffer(List.Distinct(List.Combine(Table.AddColumn(c, "q", (x)=> Table.ColumnNames(x[Content]))[q]))),
    a = Folder.Files("C:\1\11"),
    b = Table.TransformColumns(a, {{"Content", f}, {"Name", (x)=> Table.FromList({List.RemoveLastN(List.LastN(Splitter.SplitTextByAnyDelimiter({" ", "."})(x), 3), 1)}, (x)=>x, {"Подрядчик", "Месяц"})}}),
    c = Table.ExpandTableColumn(b, "Name", {"Подрядчик", "Месяц"}),
    d = Table.ExpandTableColumn(c, "Content", f1)
in
    d
 
 
 
#9
02.02.2023 15:59:26
Антон, спасибо большое. Всё попробую сделать на фактических таблицах.
 
 
 
#10
15.10.2023 16:27:17
Дабы не создавать новую тему спрошу здесь. Во время создания запроса, query берет названия всех столбцов и записывает из в запрос. Если соответственно  в таблице изменить название столбца (к примеру орфографическую ошибку исправили), то запрос уже не работает, приходится изменять название везде где идёт этот запрос. Другое есть решение? Спасибо.
 
 
 
#11
16.10.2023 11:26:16
Цитата
Константин написал:
Дабы не создавать новую тему спрошу здесь.
А какое отношение к теме имеет Ваш вопрос?
Цитата
Константин написал:
Во время создания запроса, query берет названия всех столбцов и записывает из в запрос.
Нет. PQ так не делает. Он не берет названия столбцов и тем более их никуда не записывает.

Если Вы пользовались кнопками для создания запроса, то в части "шагов" у Вас может быть жесткая привязка к именам столбцов.
К примеру, вы для столбца "sales amaunt" задали тип number, а потом наименование столбца у Вас поменялось на "sales amount" (как Вы пишите, исправили орфографическую ошибку). В таком случае будет ошибка.

В общем, без Вашего конкретного примера "что есть" и "что нужно" + запроса гадать не вижу смысла. В случае проблемы именно в типизации можете почитать последнее сообщение в телеграм-канале "Для тех, кто в танке".
 
 
 
#12
21.11.2023 08:48:47
Цитата
написал:
Другое есть решение?
Если ещё актуально - можно в начале запроса (перед первым шагом, в котором используется старое имя столбца) добавить шаг по переименованию столбца с новым названием в старое.  
 
 
 
Страницы: 1
Наверх