Power query укажите способ подключения

PostgreSQL

Сводка

Элемент	Описание
Состояние выпуска	Общедоступная версия
Продукты	Power BI (наборы данных) Power BI (потоки данных) Power Apps (потоки данных) Excel Dynamics 365 Customer Insights Службы Analysis Services
Поддерживаемые типы проверки подлинности	База данных (имя пользователя и пароль)
Справочная документация по функциям	PostgreSQL.Database

Некоторые возможности могут присутствовать в одном продукте, но не в других из-за расписаний развертывания и возможностей конкретного узла.

Предварительные требования

по состоянию на декабрь 2019 NpgSQL 4.0.10 поставляется с Power BI Desktop, а дополнительная установка не требуется. установка глобального кэша сборок переопределяет версию, указанную в Power BI Desktop, которая будет использоваться по умолчанию. обновление поддерживается как в облаке в службе Power BI, так и локально через шлюз. в службе Power BI будет использоваться NpgSQL 4.0.10, а при локальном обновлении будет использоваться локальная установка NpgSQL, если она доступна, и иным образом использовать NpgSQL 4.0.10.

для Power BI Desktop версий, выпущенных до декабря 2019, необходимо установить поставщик NpgSQL на локальном компьютере. Чтобы установить поставщик NpgSQL, перейдите на страницу выпусков и скачайте соответствующий выпуск. Архитектура поставщика (32-разрядная или 64-разрядная) должна соответствовать архитектуре продукта, в котором планируется использовать соединитель. При установке убедитесь, что выбрана установка NpgSQL GAC, чтобы гарантировать, что NpgSQL будет добавлен на компьютер.

Рекомендуем NpgSQL 4.0.10. NpgSQL 4,1 и up не будут работать из-за несовместимости версий .NET.

Поддерживаемые возможности

• Используя режим Import
• DirectQuery (только Power BI)
• Расширенные параметры
  • Время ожидания команды в минутах
  • собственная SQLная инструкция
  • Столбцы связи
  • Навигация с помощью полной иерархии

Подключение базы данных PostgreSQL из Power Query Desktop

После установки соответствующего поставщика Npgsql можно подключиться к базе данных PostgreSQL. Чтобы установить соединение, сделайте следующее:

Выберите параметр базы данных PostgreSQL в выбранном соединителе.

В появившемся диалоговом окне базы данных PostgreSQL укажите имя сервера и базы данных.

Выберите режим подключения к данным Импорт или DirectQuery.

Если вы подключаетесь к этой базе данных в первый раз, введите учетные данные PostgreSQL в полях имя пользователя и пароль типа проверки подлинности базы данных . Выберите уровень, к которому следует применить параметры проверки подлинности. В этом случае выберите Подключиться.

Дополнительные сведения об использовании методов проверки подлинности см. в статье Проверка подлинности с помощью источника данных.

Если подключение не зашифровано, отобразится следующее сообщение.

нажмите кнопку ок , чтобы подключиться к базе данных с помощью незашифрованного соединения, или следуйте инструкциям в разделе включение зашифрованных соединений с ядро СУБД , чтобы настроить зашифрованные подключения к базе данных PostgreSQL.

В навигаторе выберите нужные сведения о базе данных, а затем нажмите кнопку загрузить , чтобы загрузить данные или преобразовать данные , чтобы продолжить их преобразование в Power Query редакторе.

Подключение базу данных PostgreSQL из Power Query в сети

Чтобы установить соединение, сделайте следующее:

Выберите параметр базы данных PostgreSQL в выбранном соединителе.

В появившемся диалоговом окне базы данных PostgreSQL укажите имя сервера и базы данных.

Выберите имя локального шлюза данных, который вы хотите использовать.

Выберите тип обычной проверки подлинности и введите учетные данные MySQL в полях имя пользователя и пароль .

Если подключение не зашифровано, снимите флажок использовать зашифрованное соединение.

Нажмите кнопку Далее , чтобы подключиться к базе данных.

В навигаторе выберите нужные данные, а затем выберите преобразовать данные для преобразования данных в Power Query редакторе.

Подключение с помощью дополнительных параметров

Power Query Desktop предоставляет набор дополнительных параметров, которые можно добавить в запрос при необходимости.

В следующей таблице перечислены все дополнительные параметры, которые можно задать в Power Query Desktop.

Дополнительный параметр	Описание
Время ожидания команды в минутах	Если подключение длится дольше 10 минут (время ожидания по умолчанию), можно ввести другое значение в минутах, чтобы соединение было открыто дольше. Этот параметр доступен только в Power Query Desktop.
Инструкция SQL	Дополнительные сведения см. в подразделе Импорт данных из базы данных с помощью собственного запроса к базе данных.
Включить столбцы отношений	Если флажок установлен, включает столбцы, которые могут иметь связи с другими таблицами. Если этот флажок снят, эти столбцы отображаться не будут.
Навигация с помощью полной иерархии	Если флажок установлен, навигатор отображает полную иерархию таблиц в базе данных, к которой выполняется подключение. Если флажок не установлен, навигатор отображает только те таблицы, столбцы и строки которых содержат данные.

Выбрав нужные дополнительные параметры, нажмите кнопку ОК в Power Query рабочем столе, чтобы подключиться к базе данных PostgreSQL.

Сворачивание машинного запроса

По умолчанию встроенное сворачивание запросов включено. Операции, которые способны выполнить сворачивание, будут применены поверх собственного запроса в соответствии с обычной логикой импорта или прямого запроса. Встроенное сворачивание запросов не применяется с необязательными параметрами, присутствующими в value. нативекуери ().

В редких случаях, когда сворачивание не работает со встроенным сворачиванием запросов, его можно отключить. Чтобы отключить встроенное сворачивание запросов, установите для EnableFolding флага false значение. нативекуери () в расширенном редакторе.

Образец. Value.NativeQuery(target as any, query, null, [EnableFolding=false])

Устранение неполадок

Собственный запрос может вызвать следующую ошибку:

We cannot fold on top of this native query. Please modify the native query or remove the ‘EnableFolding’ option.

Базовым этапом решения проблемы является проверка того, что запрос в value. нативекуери () вызывает ту же ошибку с limit 1 предложением вокруг него:

Источник

Excel

Сводка

Элемент	Описание
Состояние выпуска	Общедоступная версия
Продукты	Power BI (наборы данных) Power BI (потоки данных) Power Apps (потоки данных) Excel Dynamics 365 Customer Insights Службы Analysis Services
Поддерживаемые типы проверки подлинности	Анонимный (в сети) Базовый (в сети) Учетная запись организации (в сети)
Справочная документация по функциям	Excel.Workbook Excel.CurrentWorkbook

Некоторые возможности могут присутствовать в одном продукте, но не в других из-за расписаний развертывания и возможностей конкретного узла.

Предварительные требования

для подключения к устаревшей книге (например, .xls или xlsb) требуется поставщик Access ядро СУБД OLEDB (или ACE). Чтобы установить этот поставщик, перейдите на страницу загрузки и установите соответствующую версию (32 разрядов или 64 бит). Если он не установлен, при подключении к устаревшим книгам вы увидите следующую ошибку:

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

Запись ACE не может быть установлена в средах облачных служб. поэтому, если эта ошибка возникает на облачном узле (например, Power Query в сети), необходимо использовать шлюз с установленным элементом управления доступом для подключения к устаревшим файлам Excel.

Поддерживаемые возможности

Подключение Excel книгу с Power Query Desktop

Чтобы установить подключение из Power Query рабочего стола:

выберите параметр Excel в выбранном соединителе.

найдите и выберите книгу Excel, которую требуется загрузить. Щелкните Открыть.

если книга Excel находится в режиме в сети, подключитесь к книге с помощью веб-соединителя .

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

Подключение Excel книгу из Power Query в сети

Чтобы установить подключение из Power Query в сети:

выберите параметр Excel в выбранном соединителе.

в появившемся диалоговом окне Excel укажите путь к Excel книге.

при необходимости выберите локальный шлюз данных для доступа к книге Excel.

если вы впервые получили доступ к этой Excel книге, выберите тип проверки подлинности и войдите в свою учетную запись (при необходимости).

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

Диагностика

Точность чисел (или «Почему мои числа изменились?»)

при импорте данных Excel можно заметить, что некоторые числовые значения незначительно меняются при импорте в Power Query. например, если выбрать ячейку, содержащую 0,049, в Excel, это число будет отображаться в строке формул как 0,049. Но если вы импортируете одну и ту же ячейку в Power Query и выберете ее, в предварительной версии отобразятся данные в виде 0.049000000000000002 (даже если в таблице предварительного просмотра он имеет формат 0,049). В чем причина?

ответ является немного сложным и должен быть связан с тем, как Excel хранит числа с помощью двоичной нотации с плавающей запятой. в нижней строке есть определенные числа, которые Excel не могут представляться с 100% precision. Если вы взломать файл .xlsx и взгляните на фактическое значение, вы увидите, что в файле .xlsx 0,049 фактически хранится как 0.049000000000000002. Это значение Power Query считывает из .xlsx, поэтому значение, отображаемое при выборе ячейки в Power Query. (Дополнительные сведения о точности чисел в Power Query см. в подразделах «десятичное число» и «фиксированное десятичное число» типов данных в Power Query.)

подключение к книге Excel в сети

если вы хотите подключиться к Excel документу, размещенному в Sharepoint, это можно сделать через веб- соединитель в Power BI Desktop, Excel и потоках данных, а также с соединителем Excel в потоках данных. Чтобы получить ссылку на файл, сделайте следующее:

1. откройте документ в Excel Desktop.
2. Откройте меню файл , выберите вкладку сведения , а затем щелкните Копировать путь.
3. Скопируйте адрес в поле » путь к файлу» или «URL- адрес» и удалите ? Web = 1 из конца адреса.

Устаревший соединитель ACE

Power Query считывает устаревшие книги (например, .xls или. xlsb), используйте поставщик Access ядро СУБД (или ACE) OLEDB. По этой причине при импорте устаревших книг, которые не возникают при импорте книг OpenXML (например, .xlsx), могут возникнуть непредвиденные поведения. Ниже приведены некоторые распространенные примеры.

Непредусмотренное форматирование значения

из-за записей ACE значения из устаревшей Excel книги могут быть импортированы с меньшей точностью или достоверностью, чем предполагается. например, представьте, что файл Excel содержит номер 1024,231, который вы отформатированы как «1 024,23». При импорте в Power Query это значение представлено как текстовое значение «1 024,23», а не как базовое полное число (1024,231). Это связано с тем, что в данном случае ACE не отображает базовое значение для Power Query, но только значение, отображаемое в Excel.

Непредвиденные значения NULL

Когда элемент управления доступом загружает лист, он анализирует первые восемь строк, чтобы определить типы данных столбцов. Если первые восемь строк не соответствуют более поздним строкам, ACE может применить неверный тип к этому столбцу и возвращать значение NULL для любого значения, которое не соответствует типу. Например, если столбец содержит числа в первых восьми строках (например, 1000, 1001 и т. д.), но содержит нечисловые данные в последующих строках (например, «100Y» и «100Z»), ACE завершает, что столбец содержит числа, а все нечисловые значения возвращаются как null.

Несоответствие форматирования значений

В некоторых случаях ACE возвращает совершенно разные результаты во всех обновлениях. Используя пример, описанный в разделе Форматирование, вы можете увидеть значение 1024,231 вместо «1 024,23». это различие может быть вызвано тем, что старая книга открыта в Excel при импорте в Power Query. Чтобы устранить эту проблему, закройте книгу.

данные Excel отсутствуют или неполны

иногда Power Query не удается извлечь все данные из листа Excel. Эта ошибка часто бывает вызвана тем, что лист имеет неправильные размеры (например, наличие измерений, A1:C200 когда фактические данные занимают более трех столбцов или 200 строк).

Как диагностировать неверные размеры

Чтобы просмотреть размеры листа, выполните следующие действия.

1. Переименуйте XLSX-файл с помощью расширения .zip.
2. Откройте файл в проводнике.
3. Переход к ксл\воркшитс.
4. Скопируйте XML-файл для проблемной таблицы (например, Sheet1.xml) из файла ZIP в другое расположение.
5. Проверьте первые несколько строк файла. Если файл достаточно мал, откройте его в текстовом редакторе. Если файл слишком большой для открытия в текстовом редакторе, выполните следующую команду в командной строке: more Sheet1.xml.
6. Найдите тег (например, ).

Если файл имеет атрибут измерения, указывающий на одну ячейку (например ,), Power Query использует этот атрибут для поиска начальной строки и столбца данных на листе.

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

Как исправить неверные размеры

Проблемы, вызванные неправильными измерениями, можно устранить одним из следующих способов.

Откройте и повторно сохраните документ в Excel. Это действие перезапишет неверные измерения, хранящиеся в файле, с правильным значением.

убедитесь, что средство, создавшее файл Excel, исправлено для правильного вывода измерений.

Обновите запрос M, чтобы игнорировать неверные размеры. По состоянию на декабрь 2020 Power Query Excel.Workbook теперь поддерживает InferSheetDimensions параметр. Если значение равно true, функция будет игнорировать измерения, хранящиеся в книге, и определить их путем проверки данных.

Ниже приведен пример того, как можно предоставить этот параметр.

Excel.Workbook(File.Contents(«C:\MyExcelFile.xlsx»), [DelayTypes = true, InferSheetDimensions = true])

медленная или низкая производительность при загрузке данных Excel

снижение скорости загрузки данных Excel может быть вызвано неправильными измерениями. Однако в этом случае медленная работа вызывается большим объемом измерений, чем требуется, а не слишком маленьким. Чрезмерно большие измерения приводят к тому, что Power Query читать гораздо больший объем данных из книги, чем на самом деле требуется.

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

Низкая производительность при загрузке данных из SharePoint

при извлечении данных из Excel на компьютере или из SharePoint рассмотрите как объем данных, участвующих в них, так и сложность книги.

При получении очень больших файлов из SharePoint вы заметите снижение производительности. Однако это лишь одна часть проблемы. при наличии важной бизнес-логики в файле Excel, полученном из SharePoint, эту бизнес-логику может потребоваться выполнить при обновлении данных, что может привести к сложным вычислениям. рассмотрите возможность статистической обработки и предварительной выработки данных или перемещения большей части бизнес-логики из Excel слоя в слой Power Query.

ошибки при использовании соединителя Excel для импорта CSV-файлов

хотя CSV-файлы можно открывать в Excel, они не Excel файлы. Вместо этого используйте соединитель Text/CSV .

Источник