Имеется лист книги, в котором ведется одна общая таблица. Данная таблица (в примере это 3 столбца) ведется в несколько колон (в примере это 3 таблицы по 3 столбца). Необходимо их соединить в рамках одного запроса. Это возможно? В примере показал, как я делаю сейчас через 3 запроса + один объединенный.
EvgeniyLFC, Ответ на вопрос 1, в ваш запрос с названием Append1 вставляете такой код:
Код
let
Source = Excel.CurrentWorkbook(),
FilteredRows = Table.SelectRows(Source, each ([Name] <> "Append1")),
RenameTables = Table.TransformColumns(FilteredRows, {{"Content", each [a = _, b = Table.RenameColumns( a, List.Transform(Table.ColumnNames(a), each {_, Text.BeforeDelimiter( _, "." )}))][b], type table}}),
Combine = Table.Combine( RenameTables[Content] ),
DelBlankRows = Table.SelectRows(Combine, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
DelBlankRows
Остальные запросы к отдельным таблицам можете удалить.
На вопрос 2 ответить в общем-то не сложно, но этим вы нарушаете правило форума: одна тема - один вопрос. Публикуйте его отдельно - отвечу. Можете еще поиском по форуму воспользоваться, тут такие вопросы ранее задавались и ответы на них были получены.
PooHkrd, Благодарю. Можете в двух словах объяснить принцип действия? То есть у меня будут файлы с 4-5-ю таблицами, состоящими из 4-6 колонок. Мне также создавать 5 запросов, затем 1 объединенный, И в объединенный запрос подставлять Ваш код, но он сам сообразит какие колонки с какими объединять?
EvgeniyLFC написал: Можете в двух словах объяснить принцип действия?
Там же по шагам все видно Сначала при помощи Excel.CurrentWorkbook() получаем список всех именованных диапазонов книги, потом исключаем из него ту таблицу, в которую будет выводиться результат (чтобы его не задублировать). Потом переименовываем все таблицы из столбца Content, потом объединяем их в единую таблицу, потом зачищаем пустые строки.
Цитата
EvgeniyLFC написал: у меня будут файлы с 4-5-ю таблицами, состоящими из 4-6 колонок
про несколько файлов с таблицами до этого вы ничего не писали. Это я к тому, что это уже отдельная задача и, по идее, нужна новая тема. Тем не менее, если схематично, то тут несколько вариантов: 1. Все файлы лежат в разных папках, тогда делаем запросы к каждому файлу в отдельности и используем мой запрос для объединения таблиц. 2. Если файлы в одной папке, то делаем запрос к папке, потом мой запрос переделываем в функцию и собираем все таблицы из всех файлов, подробная инструкция как это сделать есть здесь. 3. Все файлы лежат в разных папках, НО имеется умная таблица с путями к этим файлам, тогда делаем один запрос к этой таблице, потом при помощи функции File.Content формируем столбец с бинарным содержимым файлов, а далее все делаем как в пункте 2, как будто все файлы лежат в одной папке.
PooHkrd, Я наверное не правильно выразился. У меня несколько файлов, в которых принцип формирования таблиц - колонками. То есть идет таблица с 4-5 столбцами - она занимает столбцы A:E, далее ее продолжение идет на столбцы F:K и т.д. Самая большая такая таблица "растянута" на 6 колон по 6 столбцов в каждой. По факту Power Query загружает ее всю автоматом. Поэтому приходится создавать 6 разных запросов и потом объединять их. И поэтому принцип запроса мне был нужен. Вот таких файлов, к сожалению, много.
EvgeniyLFC, теперь все еще запутаннее, т.е. в оригинале никаких умных таблиц нету? Диапазон I:N это продолжение для диапазона C:H ? И что делать со столбцами Примечание и Отметка?
PooHkrd, dct dthyj - С:H, далее I:N, далее P:U и т.д. Столбцы "Примечание" и "Отметка" не нужны. Да, это не умные таблицы, поэтому я создавал 6 запросов: для C:H, для I:N и т.д., практически одинаковых, потом соединял их.
Собственно, вопрос и был - реально как-то это в одном запросе сделать? Или никак...
В общем ваша задачка на самом деле сводится к ранее здесь обсуждавшейся. Таким образом утаскиваем оттуда универсальную функцию по сборке таблиц с фиксированным количеством повторяющихся столбцов. Потом приводим вашу таблицу к нужному виду и этой функцией собираем все в плоскую таблицу. Пробуйте:
Скрытый текст
Код
let
fnUnpivotRepeatedColumns =
(sourceTable as table,
startFixColumnCount as number,
repeatColumnCount as number,
optional repeatColumnNames as list) as table =>
let
columnNames = List.Buffer(Table.ColumnNames(sourceTable)),
tempNestedName = Text.NewGuid(),
nullList = {List.Repeat({null}, repeatColumnCount)},
defaultRepeatNames=List.Range(columnNames, startFixColumnCount, repeatColumnCount),
fixColumnNames = List.Range(columnNames, 0, startFixColumnCount),
transform = Table.AddColumn(
sourceTable,
tempNestedName,
(rec) =>
let
fieldValues = Record.FieldValues(rec),
repeatFields = List.Skip(fieldValues, startFixColumnCount),
rows = List.Split(repeatFields, repeatColumnCount),
notNullRows = List.RemoveMatchingItems(rows, nullList),
toTable = #table(defaultRepeatNames, notNullRows)
in
toTable
),
neededColumns = Table.SelectColumns(transform, fixColumnNames & {tempNestedName})
in
Table.ExpandTableColumn(
neededColumns,
tempNestedName,
defaultRepeatNames,
if repeatColumnNames = null then defaultRepeatNames else repeatColumnNames),
Source = Excel.Workbook(File.Contents("E:\Пример_таблицы-1.xlsx"), null, true),
Дано_Sheet = Source{[Item="Дано",Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Дано_Sheet, [PromoteAllScalars=true]),
SelectedColumns = Table.SelectColumns( PromotedHeaders,List.Select( Table.ColumnNames( PromotedHeaders ), each Text.Contains( _, "Дата" ) or Text.Contains( _, "Сто" ) ) ),
ColCount = List.Count( List.FirstN( Table.ColumnNames( SelectedColumns ), each not Text.Contains( _, "_" ) ) ) - 1,
Out = fnUnpivotRepeatedColumns( SelectedColumns, 1, ColCount )
in
Out