Добрый день, уважаемые знатоки Excel!
Я пока только начинаю осваивать Power Query, и не могу найти эффективное решение для моей задачи.
Прошу вашей помощи в оптимизации
Задача следующая:
Дано:
По API получаю json с несложной структурой. Например, такой:
К сожалению, поставщик данных всё время что-нибудь меняет: может изменить имя существующего ключа или добавить новый.
При обычном применении метода Table.ExpandRecordColumn в запрос записываются все имена ключей:
Проблема:
Если добавить новые ключи в файл json и обновить запрос, то новые ключи не добавятся как новые столбцы.
Моё решение:
К сожалению, на больших объёмах данных зависает (собственно, это было ожидаемо).
Функция, которая:
0 - получает на вход таблицу записей из json,
1 - заходит в каждую запись, запоминает список ключей,
2 - соединяет все ключи записей в один список,
3 - отбирает уникальные ключи
f_HeadersList (в приложенном файле):
Прикладываю пример файла json (в архиве) + файл Excel с двумя вариантами запросов (обычный, без использования функции – q_Example00 и с функцией – q_Example01).
Если можно, подскажите, пожалуйста, более изящное и быстродействующее решение.
Спасибо большое, что обратили внимание на мой вопрос!
Я пока только начинаю осваивать Power Query, и не могу найти эффективное решение для моей задачи.
Прошу вашей помощи в оптимизации
Задача следующая:
Дано:
По API получаю json с несложной структурой. Например, такой:
| Код |
|---|
[
{"Поле1":1,"Поле2":123},
{"Поле1":1,"Поле2":456,"Поле3":"Текст0"},
{"Поле1":1,"Поле3":"Текст1"},
{"Поле1":1}
] |
При обычном применении метода Table.ExpandRecordColumn в запрос записываются все имена ключей:
| Код |
|---|
Table.ExpandRecordColumn(JsonToTbl, "Column1", {"Поле1", "Поле2", "Поле3"}) |
Проблема:
Если добавить новые ключи в файл json и обновить запрос, то новые ключи не добавятся как новые столбцы.
Моё решение:
К сожалению, на больших объёмах данных зависает (собственно, это было ожидаемо).
Функция, которая:
0 - получает на вход таблицу записей из json,
1 - заходит в каждую запись, запоминает список ключей,
2 - соединяет все ключи записей в один список,
3 - отбирает уникальные ключи
f_HeadersList (в приложенном файле):
| Код |
|---|
(Tbl as table) as list =>
let
Rows = Table.RowCount(Tbl),
// List.Accumulate parameters
list_numbers = {0 .. Rows-1},
seed = {},
fn = (state, current) => state & Record.FieldNames(Tbl{current}[Column1]),
HeadersList0 = List.Accumulate(list_numbers, seed, fn),
HeadersList = List.Distinct(HeadersList0)
in
HeadersList |
Прикладываю пример файла json (в архиве) + файл Excel с двумя вариантами запросов (обычный, без использования функции – q_Example00 и с функцией – q_Example01).
Если можно, подскажите, пожалуйста, более изящное и быстродействующее решение.
Спасибо большое, что обратили внимание на мой вопрос!
Изменено: - 30.08.2022 17:06:59
