Как я могу объединить первые две строки в 52 файлах в качестве строки заголовка и объединить все файлы в один в Power Query?

ProgramBox

Как я могу объединить первые две строки в 52 файлах в качестве строки заголовка и объединить все файлы в один в Power Query?

Post author:admin
Запись опубликована:15 апреля, 2023
Post category:Вопросы по программированию

#excel #powerquery

Вопрос:

У меня есть 52 файла с > 50 столбцами. Первые две строки в каждом из них являются строками заголовка, поэтому их нужно объединить в одну строку, и я хочу объединить их все в один файл.

Есть несколько файлов с разным количеством столбцов или разными именами столбцов, поэтому, если PowerQuery идентифицирует их, я могу работать с ними отдельно.

Единственный способ, который я могу придумать, это открыть каждый файл, запустить макрос для объединения первых двух строк во всех файлах, но есть ли более эффективный способ сделать это в Power query для всех файлов?

Спасибо!

Ответ №1:

Если файл имеет одинаковую структуру и вам нужно их объединить, вы можете использовать опцию ‘Загрузить из папки‘. Вы проходите через параметры загрузки из папки как обычно, а затем выбираете «Объединить и преобразовать данные».

В редакторе запросов найдите «Вспомогательные запросы».

Этот запрос использует выбранный пример файла, чтобы определить метаданные для остальной части загрузки файла. Если вы посмотрите на «Файл примера преобразования», вы можете добавить свои шаги для объединения строк. При загрузке будут применены любые приведенные здесь шаги к каждому файлу в папке. Затем вы можете «Использовать первую строку в качестве заголовков», чтобы продвинуть объединенные строки в заголовок. Возможно, вам потребуется удалить автоматически применяемый шаг «расширенные заголовки», если Power Query это уже сделал.

Как только это будет сделано, файлы будут объединены в одну таблицу в Power Query. Лучше всего сделать это на образцовом наборе файлов, чтобы увидеть, работает ли это, а затем добавить остальные файлы. Для тех файлов, которые имеют разную структуру, это может немного запутать, поэтому было бы лучше создать для них разные папки и загрузить их таким образом.

1. Это интересно. Из 52, возможно, 2-3 могут иметь разные структуры. Итак, я согласен работать с ними по отдельности, пока PowerQuery их идентифицирует, потому что без открытия каждого файла, подсчета столбцов и проверки совпадения заголовков я не смог бы определить, одинакова ли структура. Для преобразования я бы просто транспонировал набор данных, объединив первые два столбца, а затем снова транспонировал? Это занимает действительно много времени, и в итоге у меня получается более 12000 столбцов. Есть ли лучший способ для этого?

2. Если имена столбцов совпадают, они будут объединены / добавлены, а не добавлены в новые столбцы.

3. Есть ли способ удалить из первой строки две строки файлов 2-52? После объединения и продвижения заголовка у меня все еще есть строки заголовка в файлах, которые я объединил

4. Если вы добавили процесс в файл примера преобразования, он должен применить их к более поздним файлам, и их не должно быть в наборе данных

Ответ №2:

 let Source = Folder.Files("C:directorysubdirectory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),

// get all data from all XLS files
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
// filter for specific sheet
#"Filtered Rows1" = Table.SelectRows(#"Expanded GetFileData", each ([Sheet] = "Sheet1")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Content", "Hidden", "Item", "Kind"}),

//demote, then combine first two rows of each column, then recombine with original data
#"Added Custom1" = Table.AddColumn(#"Removed Columns","Top", each Table.TransformColumnTypes(Table.Transpose(Table.FirstN(Table.DemoteHeaders([Data]),2)),{{"Column1", type text}, {"Column2", type text}})),
#"Added Custom2" = Table.AddColumn(#"Added Custom1" ,"Bottom", each Table.Transpose(Table.CombineColumns([Top] ,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2" ,"Data3", each Table.PromoteHeaders( [Bottom]  amp; Table.Skip(Table.DemoteHeaders([Data]),2))),

// expand all columns and remove extras
List = List.Union(List.Transform(#"Added Custom3"[Data3], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom3", "Data3", List,List),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Data",{"Data", "Top", "Bottom"})
in #"Removed Columns1"

Вот как я бы это сделал

Открывает все файлы xlsx в каталоге (измените расширение и путь по мере необходимости)
Чтение во всех столбцах на всех вкладках в каждом файле
В каждой таблице данных объединяет первые две строки каждого столбца, а затем объединяет с данными в этом
Затем разворачивает все таблицы