Иногда для таблиц со слишком большим количеством столбцов, а также для баз данных со слишком большим количеством таблиц вам нужна небольшая помощь для исследования данных. В качестве примера; вы знаете, что вы ищете столбец с именем «статус учетной записи», но столбец не существует в таблице учетных записей. Вам нужно выполнить поиск по всем таблицам базы данных для этого столбца и узнать, какая таблица имеет в нем это значение. Power Query имеет отличную функцию, которая может помочь в таком сценарии. Table.ColumnNames - это функция, которую мы собираемся проверить в этой статье.

Проблема

У вас есть база данных с сотнями таблиц или даже больше, и каждая таблица имеет много столбцов. Вы ищете конкретный столбец в базе данных и хотите найти таблицы, в которых такой столбец существует. В Power Query есть функция с именем Table.ColumnNames, которая выводит вам список всех столбцов в таблице. Посмотрим, как работает эта функция.

Пример набора данных

Для этого примера вы можете подключиться к базе данных AdventureWorksDW. Затем выберите любую из таблиц для импорта данных из исходной базы данных.

В списке шагов; вы найдете один шаг под названием « Navigation». Это шаг, который мы переместили к таблице. Удалите этот шаг.

Тогда вы, скорее всего, увидите список всех баз данных, к которым у вас есть доступ (если вы не выбрали конкретную базу данных при подключении к разделу SQL Server). Нажмите «Table» в строке «Data» базы данных AdventureWorks2014 (или любых других баз данных, которые вы хотите иccледовать в ней).

За счет этого действия вы получите список всех таблиц в этой базе данных.

Теперь предположим, что мы хотим найти столбец «Account» во всей базе данных. Посмотрим, как можно это сделать.

Table.ColumnNames

Функция Table.ColumnNames в Power Query предоставит вам список всех столбцов в любой таблице. Все, что вам нужно сделать, - это вызвать эту функцию, передав таблицу в качестве входа, и вы получите список имен столбцов в качестве вывода. Чтобы использовать его в существующем примере, нажмите  Add Column, а затем на Add Custom Column.

В разделе выражения Custom Column вы можете задать функцию Table.ColumnNames с входным параметром Data (Data - это столбец, который содержит таблицу данных). Обратите внимание, что Power Query чувствителен к регистру, а Table.ColumnNames должны быть написаны точно так, как указано в этом сообщении в блоге.

=Table.ColumnNames([Data])

Это действие даст вам новый столбец со списком в каждой ячейке. Этот список содержит имена столбцов для каждой таблицы.

Теперь, чтобы получить список всех столбцов во всех таблицах, вам просто нужно развернуть этот столбец;

После расширения вы будете иметь все столбцы во всех таблицах, перечисленных в этом поле «Column». Вы можете преобразовать его в текстовый тип данных.

Поиск по столбцам

Теперь, когда мы получили список всех имен столбцов, поиск по нему очень прост. Вы можете использовать основной поиск, но помните, если в вашем источнике данных имеется более 1000 столбцов, у вас отобразится ограниченный список.

Лучший способ поиска в этом списке - использовать параметры расширенного поиска. Там вы можете выбрать такие критерии, как Contains, Equals, Begins With, Ends With и т. д.

Например, поиск для Contains ... «Учетная запись» в итоге покажет следующий результат:

Будьте осторожны с чувствительностью к регистру

Power Query - это чувствительный к регистру язык. Существует различие между «Account» и «account» в качестве текста. Один использует капитал A, а другой; нижний регистр a. Чтобы убедиться, что вы всегда можете найти элемент, независимо от его чувствительности; вы можете сначала преобразовать имена столбцов в нижний регистр или в верхний регистр. Для этого выберите поле «Column», а на вкладке «Transform» выберите «Transform to Lower».

Теперь весь список имен столбцов будет в нижнем регистре, и вы можете искать его только в нем.

Резюме

Иногда простые преобразования, такие как получение списка столбцов из таблицы, могут быть большой помощью для исследования данных. В этом сообщении вы увидели, как это может быть полезно для поиска по всем столбцам в базе данных. Такой подход может использоваться для любых источников данных, независимо от того, являются ли они базами данных SQL Server или какими-либо другими. Пока источник данных имеет структуру таблицы, вы можете получить список всех столбцов из этой таблицы. Этот подход особенно полезен при подключении к базам данных с тысячами таблиц, каждая из которых содержит сотни столбцов. Примерами таких источников данных  являются CRM или Dynamics.

No Comments