При попытке внести изменения в базу данных службы Power BI, например, добавив дополнительные таблицы в базу данных SQL, пользователи Power BI могут столкнуться с различными ошибками формата данных. Некоторые из ошибок включают DataFormat.Error: Мы достигли конца буфера или power bi dataformat.error внешней таблицы не в ожидаемом формате .
Если вы также обеспокоены этими ошибками Power BI, вот несколько советов по устранению неполадок, чтобы решить проблему с несколькими ошибками Dataformat.er.
Если проблема не устранена, выполните следующие действия.
Если проблема не устранена, попробуйте удалить начальный измененный тип данных для даты из числа в текст.
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Hello. I’m having trouble in PowerQuery with the above error, and I don’t see anything in this forum that matches my problem exactly.
I have tried this with different files, different directories, I have copied the query file, I have rebuilt the query file from scratch and nothing seems to help. The problem does not seem to happen when I first write the query, but when I exit out and back in I consistently get the error.
I have pasted the entire query below (none of which is confidential).
Any ideas on this would be greatly appreciated.
При попытке внести изменения в базу данных службы Power BI, например, добавив дополнительные таблицы в базу данных SQL, пользователи Power BI могут столкнуться с различными ошибками формата данных. Некоторые ошибки включают DataFormat.Error: Мы достигли конца буфера или power bi dataformat.error внешней таблицы не в ожидаемом формате.
Если вы также обеспокоены этими ошибками Power BI, вот несколько советов по устранению неполадок, чтобы решить проблему с несколькими ошибками Dataformat.er.
Как исправить типичные ошибки dataformat.er в Power BI
1. DataFormat.Error: мы достигли конца буфера
Проверьте размер файла
- Если ошибка возникает при попытке импортировать данные из нескольких файлов одновременно, это может быть связано с проблемами с размером файла.
- Проверьте размер файла JSON, чтобы убедиться, что он не связан с размером вашего файла.
Подожди, подожди и подожди!
- Если это временная проблема, то нет смысла пытаться устранить проблему вне вашей зоны комфорта.
- Пользователи сообщают, что ошибка формата данных была устранена автоматически через день или два.
- Итак, обратитесь в службу поддержки Power BI, если проблема подходит к концу.
Если проблема не устранена, выполните следующие действия.
- Если вы делаете PowerQuery, попробуйте отказаться от него и настроить промежуточную таблицу в базе данных SQL, которая анализирует JSON с помощью T-SQL.
3. Power BI dataformat.error неверное значение ячейки # имя / #ref
- Попробуйте исправить ошибку в приложении Excel, прежде чем импортировать ее.
- Проверьте, имеет ли какая-либо из формул Excel значение # N / A, которое является основной причиной этой проблемы. Замените значение пустым или пробелом.
Как устранить проблемы с нехваткой места на диске в Windows 10
Если вы получаете уведомления о нехватке места на диске E, отключите защиту системы, щелкнув правой кнопкой мыши «Пуск» и выбрав «Система».
Как устранить проблемы с батареей Surface Pro 3: избавиться от устройства
Поверхность Pro 3 печально известна своими проблемами разрядки батареи. В течение нескольких месяцев Microsoft пыталась решить эти проблемы с батареями, но безрезультатно. Устройства Surface Pro 3, работающие от аккумуляторов LGC, особенно подвержены этой ошибке, и, похоже, это будет продолжаться в течение длительного времени. Microsoft недавно выкатил .
Как устранить ошибку привязки power bi?
Если вы получили ошибку привязки Power BI, исправьте ее сейчас, отладив, переустановив сертификаты SSL, обновив NodeJS или избежав определенных переменных.
Источник
Power query dataformat error внешняя таблица не имеет предполагаемый формат
Есть два файла.
При настройке запроса Power Query на эти файлы «Из папки» запрос не формируется.
DataFormat.Error: Входные данные не удалось распознать как допустимый документ Excel.
Подробные сведения:
Binary
По отдельности запрос на файлики формируется нормально.
Файлы не могу приложить из-за их размера.
В чем может быть проблема?
Есть два файла.
При настройке запроса Power Query на эти файлы «Из папки» запрос не формируется.
DataFormat.Error: Входные данные не удалось распознать как допустимый документ Excel.
Подробные сведения:
Binary
По отдельности запрос на файлики формируется нормально.
Файлы не могу приложить из-за их размера.
В чем может быть проблема? Viper25
Сообщение Есть два файла.
При настройке запроса Power Query на эти файлы «Из папки» запрос не формируется.
DataFormat.Error: Входные данные не удалось распознать как допустимый документ Excel.
Подробные сведения:
Binary
По отдельности запрос на файлики формируется нормально.
Файлы не могу приложить из-за их размера.
В чем может быть проблема? Автор — Viper25
Дата добавления — 19.09.2016 в 17:51
Szekerfehesvar | Дата: Понедельник, 19.09.2016, 21:23 | Сообщение № 2 | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
public async Task UploadEmployeesAsync(string pathToFile) { string excelConnectionString = GetExcelConnectionString(pathToFile); // Метод из BaseDA DataTable excelDataTable = new DataTable(); excelDataTable.Columns.AddRange(new DataColumn[12] { new DataColumn("PersonalNumber",typeof(string)), new DataColumn("LastName",typeof(string)), new DataColumn("FirstName",typeof(string)), new DataColumn("SecondName",typeof(string)), new DataColumn("DateOfBirth",typeof(DateTime)), new DataColumn("PhoneNumber",typeof(string)), new DataColumn("EMail",typeof(string)), new DataColumn("EmploymentDate",typeof(DateTime)), new DataColumn("DismissalDate",typeof(DateTime)), new DataColumn("Foreigner",typeof(int)), new DataColumn("Country",typeof(string)), new DataColumn("Position",typeof(string)) }); string excelQuery = "SELECT PersonalNumber, LastName, FirstName, SecondName, DateOfBirth, PhoneNumber, EMail, " + "EmploymentDate, DismissalDate, Foreigner, Country, [Position] " + "FROM [tblHRDEmployee$]"; try { using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(excelQuery, excelConnectionString)) { oleDbDataAdapter.Fill(excelDataTable); } using (SqlConnection sqlConnection = new SqlConnection(_connectionString)) { await sqlConnection.OpenAsync(); using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction()) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints, sqlTransaction)) { sqlBulkCopy.DestinationTableName = "dbo.tblHRDEmployee"; sqlBulkCopy.ColumnMappings.Add("PersonalNumber", "PersonalNumber"); sqlBulkCopy.ColumnMappings.Add("LastName", "LastName"); sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName"); sqlBulkCopy.ColumnMappings.Add("SecondName", "SecondName"); sqlBulkCopy.ColumnMappings.Add("DateOfBirth", "DateOfBirth"); sqlBulkCopy.ColumnMappings.Add("PhoneNumber", "PhoneNumber"); sqlBulkCopy.ColumnMappings.Add("EMail", "EMail"); sqlBulkCopy.ColumnMappings.Add("EmploymentDate", "EmploymentDate"); sqlBulkCopy.ColumnMappings.Add("DismissalDate", "DismissalDate"); sqlBulkCopy.ColumnMappings.Add("Foreigner", "Foreigner"); sqlBulkCopy.ColumnMappings.Add("Country", "Country"); sqlBulkCopy.ColumnMappings.Add("Position", "Position"); try { await sqlBulkCopy.WriteToServerAsync(excelDataTable); sqlTransaction.Commit(); } catch (Exception tranEx) { sqlTransaction.Rollback(); throw new ApplicationException("Ошибка транзакции вставки данных", tranEx); } } } } } catch (Exception ex) { throw new ApplicationException("Ошибка загрузки данных", ex); } } |
Хитрости »
11 Апрель 2018 10852 просмотров
PowerQuery очень мощный инструмент по работе с файлами
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?
Наиболее часто она применяется для сбора данных с листов и файлов. Я для примера возьму модель из своей статьи: Собрать и просуммировать данные из разных файлов при помощи PowerQuery. Там из папки отбираются все файлы Excel и объединяются в единую таблицу(плюс суммируются по критерию, но это сейчас неважно). Все работает отлично. Но если хотя бы на один файл будет установлен пароль на открытие запрос выдаст ошибку([DataFormat.Error] Внешняя таблица не имеет предполагаемый формат):
а если перейти в редактор запроса, то там будет ошибка вроде такой — Невозможно импортировать данные из книги, защищенной паролем:
чтобы избежать ошибки при обновлении надо держать файл без пароля, что не всегда допустимо. Особенно, когда речь идет о бюджетах и лежат они где-то на сетевом диске. Можно вручную открыть каждый файл, снять пароль, сохранить книгу, закрыть, обновить запрос, а затем установить пароль на все книги заново. Метод хоть и надежный, но совсем не быстрый, если книг с паролем в папке хотя бы 5-10.
Сама модель PowerQuery не умеет(пока что) работать с защищенными паролем файлами и не может снять пароль с книг. Но это можно сделать при помощи Visual Basic for Applications(VBA). При этом код не такой уж сложный сам по себе, но нам надо учесть три вещи:
- Папка с файлами должна определяться кодом автоматически из запроса. Здесь мы можем пойти хитрым путем и указывать путь в умной таблице(подробно про такой подход я описывал в статье: Относительный путь к данным PowerQuery). Тогда код так же сможет получить путь к папке просто из таблицы параметров. Это значит что и запрос PowerQuery и код VBA будут использовать один и тот же путь и это не потребует от нас особых усилий. Плюс модель можно будет перемещать куда угодно — а это тоже большой плюс
- Код должен сам сначала открыть все файлы, снять с них пароль, сохранить и закрыть. После этого обновить запрос и дождаться его выполнения
- И последним шагом код должен установить пароль на файлы обратно. И только после завершения обновления запроса, не раньше
Код просмотра файлов в папке я уже когда-то давно приводил на сайте: Просмотреть все файлы в папке. Нам останется его немного модифицировать и сделать из него функцию, которая будет на основании переданных в неё параметров либо снимать пароль, либо устанавливать. Впрочем, ниже готовый код, в котором достаточно комментариев, чтобы уловить суть:
'--------------------------------------------------------------------------------------- ' Author : The_Prist(Щербаков Дмитрий) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' http://www.excel-vba.ru ' info@excel-vba.ru ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' Purpose: '--------------------------------------------------------------------------------------- Option Explicit Sub RefreshPQ() 'вызываем функцию, убирающую пароль на открытие файлов Call ReOpenFiles(True, "1234") 'обновляем все запросы в книге 'если надо обновить только определенные ' -можно сделать это либо в одном конкретном листе ' (убрать цикл For Each ws In ThisWorkbook.Worksheets) ' -либо взять конкретный запрос(ws.QueryTables(1).Refresh) Dim ws As Worksheet, qt As QueryTable, oc As Object, IsBG_Refresh As Boolean For Each oc In ThisWorkbook.Connections 'запоминаем значение обновления в фоне для запроса IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery 'выставляем принудительно ждать завершения запроса oc.OLEDBConnection.BackgroundQuery = False 'обновляем запрос oc.Refresh 'возвращаем обновление в фоне в первоначальное состояние oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh Next ''======================================================= '' Если надо обновить только один запрос ' ' set oc = ThisWorkbook.Connections("Запрос — Бюджет") ' IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery ' oc.OLEDBConnection.BackgroundQuery = False ' oc.Refresh ' oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh ''======================================================= 'вызываем функцию, чтобы поставить обратно пароль на файлы Call ReOpenFiles(False, "1234") MsgBox "Запросы обновлены", vbInformation, "www.excel-vba.ru" End Sub 'функция открывает каждый файл в папке и удаляет/устанавливает пароль на открытие Function ReOpenFiles(IsDelPWD As Boolean, sPWD As String) Dim sFolder As String, sFiles As String Dim wb As Workbook Dim sNewPWD As String, sOldPWD As String 'определяем, убрать пароль на открытие или вернуть If IsDelPWD Then sOldPWD = sPWD sNewPWD = "" Else sOldPWD = "" sNewPWD = sPWD End If 'получаем путь к папке с файлами из "умной" таблицы "Parameters"(лист "Параметры") sFolder = Range("Parameters").Cells(1, 1).Value sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator) 'отключаем обновление экрана, чтобы наши действия не мелькали Application.ScreenUpdating = False sFiles = Dir(sFolder & "*.xls*") Do While sFiles <> "" 'открываем книгу, убирая/устанавливая пароль на открытие Set wb = Application.Workbooks.Open(sFolder & sFiles, False, Password:=sOldPWD) wb.Password = sNewPWD 'закрываем книгу с сохранением wb.Close True 'если поставить False - книга будет закрыта без сохранения sFiles = Dir Loop 'возвращаем ранее отключенное обновление экрана Application.ScreenUpdating = True End Function
Процедура RefreshPQ содержит в себе основной код обновления запросов.
Функция ReOpenFiles — просматривает все файлы в папке(sFolder = Range(«Parameters»).Cells(1, 1).Value). Если аргумент IsDelPWD передан как True — то пароль с файлов снимается. Это мы делаем перед обновлением запроса. Если аргумент IsDelPWD передан как False — пароль на файлы устанавливается заново. Второй аргумент — это пароль на открытие файлов.
Почему в коде я применяю обновление всех запросов? Потому что я не знаю наверняка, только один запрос использует подключение к книгам или несколько. В смысле я-то точно знаю, что у меня такой запрос один. А вот один ли он будет всегда? Впрочем, в комментариях к коду я обозначил, что можно обновить один единственный запрос, если точно известно его имя(или номер):
' Если надо обновить только один запрос set oc = ThisWorkbook.Connections("Запрос — Бюджет") IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery oc.OLEDBConnection.BackgroundQuery = False oc.Refresh oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh
Конечно, у кого-то точно возникнет вопрос: а что если для каждого файла будет свой пароль? Это уже другая история и в таком случае оптимально создавать список всех файлов на отдельном листе, где записывать имя файла и пароль для него. А уже кодом при просмотре файлов из папки сравнивать имена и при совпадении применять нужный пароль. Хотя я не очень приветствую такой подход, потому что файлы, собираемые в единый отчет лучше и создавать одинаково, в том числе и пароль давать единый. Это впоследствии избавит от многих проблем.
Скачать готовую модель
с рабочим кодом и всеми нужными ссылками:
Модель сбора с защищенных файлов.zip (141,0 KiB, 674 скачиваний)
Т.к. сама PowerQuery просматривает файлы не только в одной указанной папке, но и во всех подпапках — решил дополнить статью кодом, который так же просматривает все файлы не только в указанной папке, но и во всех её подпаках до самой глубоко вложенной. Так же в этом коде реализовано запоминание пароля для каждой книги, если он был установлен. Это может пригодиться, если пароль на открытие установлен не на все книги, а только на некоторые. Предыдущий код в этом случае после первого выполнения устанавливал пароль на открытие на все книги в папке.
'--------------------------------------------------------------------------------------- ' Author : The_Prist(Щербаков Дмитрий) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' http://www.excel-vba.ru ' info@excel-vba.ru ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' Purpose: '--------------------------------------------------------------------------------------- Option Explicit Dim objFSO As Object, objFolder As Object, objFile As Object Dim oPassDic As Object Sub RefreshPQ() Set oPassDic = CreateObject("scripting.dictionary") oPassDic.comparemode = 1 Application.StatusBar = "Снимаю пароль с файлов..." Application.DisplayAlerts = False 'чтобы не показывать окно пароля, если он неверный 'вызываем функцию, убирающую пароль на открытие файлов Call ReOpenFilesFromSubFolders(True, "1234") 'обновляем все запросы в книге 'если надо обновить только определенные ' -можно сделать это либо в одном конкретном листе ' (убрать цикл For Each ws In ThisWorkbook.Worksheets) ' -либо взять конкретный запрос(ws.QueryTables(1).Refresh) Dim ws As Worksheet, qt As QueryTable, oc As Object, IsBG_Refresh As Boolean Application.StatusBar = "Обновляю запросы..." For Each oc In ThisWorkbook.Connections 'запоминаем значение обновления в фоне для запроса IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery 'выставляем принудительно ждать завершения запроса oc.OLEDBConnection.BackgroundQuery = False 'обновляем запрос oc.Refresh 'возвращаем обновление в фоне в первоначальное состояние oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh Next ''======================================================= '' Если надо обновить только один запрос ' ' set oc = ThisWorkbook.Connections("Запрос — Бюджет") ' IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery ' oc.OLEDBConnection.BackgroundQuery = False ' oc.Refresh ' oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh ''======================================================= Application.StatusBar = "Устанавливаю пароль на файлы..." 'вызываем функцию, чтобы поставить обратно пароль на файлы Call ReOpenFilesFromSubFolders(False, "1234") Set oPassDic = Nothing Application.StatusBar = False Application.DisplayAlerts = True MsgBox "Запросы обновлены", vbInformation, "www.excel-vba.ru" End Sub 'функция открывает каждый файл в папке и всех подпапках ' и удаляет/устанавливает пароль на открытие ' если до этого на книгу не был установлен пароль - он не устанавливается Function ReOpenFilesFromSubFolders(IsDelPWD As Boolean, sPWD As String) Dim sFolder As String 'получаем путь к папке с файлами из "умной" таблицы "Parameters"(лист "Параметры") sFolder = Range("Parameters").Cells(1, 1).Value sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator) Application.ScreenUpdating = False Set objFSO = CreateObject("Scripting.FileSystemObject") 'просматриваем файлы в папке и всех подпапках GetSubFolders sFolder, IsDelPWD, sPWD Set objFolder = Nothing Set objFSO = Nothing Application.ScreenUpdating = True End Function Private Function GetSubFolders(sPath, IsDelPWD As Boolean, sPWD As String) Dim sPathSeparator As String, sObjName As String Dim wb As Workbook Dim sNewPWD As String, sOldPWD As String, spass As String 'определяем, убрать пароль на открытие или вернуть If IsDelPWD Then sOldPWD = sPWD sNewPWD = "" Else sOldPWD = "" sNewPWD = sPWD End If Set objFolder = objFSO.GetFolder(sPath) For Each objFile In objFolder.Files If Replace(objFile.Name, objFSO.GetBaseName(objFile), "") Like ".xls*" Then 'открываем книгу, убирая/устанавливая пароль на открытие On Error Resume Next Set wb = Nothing Set wb = Application.Workbooks.Open(sPath & objFile.Name, False, Password:=sOldPWD) 'если пароль подошел и книга открыта If Not wb Is Nothing Then If IsDelPWD Then 'запоминаем текущий пароль к книге, если он есть If wb.HasPassword Then spass = sOldPWD Else spass = "" End If oPassDic.Add wb.FullName, spass wb.Password = "" Else 'устанавливаем пароль к книге, который был до этого spass = oPassDic.Item(wb.FullName) wb.Password = spass End If 'закрываем книгу с сохранением wb.Close True 'если поставить False - книга будет закрыта без сохранения End If End If Next For Each objFolder In objFolder.SubFolders GetSubFolders objFolder.Path & Application.PathSeparator, IsDelPWD, sPWD Next End Function
Скачать модель обновления запроса и снятия пароля со всех файлов папки и подпапок:
Модель сбора с защищенных файлов включая подпапки.zip (137,8 KiB, 579 скачиваний)
Кстати, функцию перебора файлов можно использовать отдельно и не только для снятия/установки пароля на открытие. Если чуть изменить, можно снимать пароли со всех листов всех книг. Для этого надо будет чуть изменить часть кода(надеюсь разберетесь какую часть надо заменить):
Dim ws As Worksheet Do While sFiles <> "" 'открываем книгу, убирая/устанавливая пароль на открытие Set wb = Application.Workbooks.Open(sFolder & sFiles, False, Password:=sOldPWD) wb.Password = sNewPWD For Each ws in wb.Worksheets If IsDelPWD Then ws.Unprotect Password:=sOldPWD Else ws.Protect Password:=sNewPWD End If Next 'закрываем книгу с сохранением wb.Close True 'если поставить False - книга будет закрыта без сохранения sFiles = Dir Loop
Так же см.:
Обновить запросы к защищенным файлам [MulTEx]
Получить данные из файлов XML при помощи Power Query
Собрать и просуммировать данные из разных файлов при помощи PowerQuery
План-фактный анализ в Excel при помощи Power Query
Относительный путь к данным PowerQuery
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика
Запрос Power Query «Из папки» |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
- Remove From My Forums
-
Question
-
I receive a [DataFormat.Error] External table is not in the expected format error while trying to load my query. My query looks to a folder that contains multiple folders with multiple excel file(300+) all in the same formatting, or supposed
to be. It does not tell me where the table is located so that I can try to fix the problem. I need help troubleshooting this issue as it will most likely pop up again.
Answers
-
Hi Davis615.
Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error.
I met this errors permanently with my accounting system exports.The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files.
You can check what file raises an error if you make a query to folder (Folder.Contents or Folder.Files, filter all xlsx files and then add a custom column with formula
Excel.Workbook([Contents])
If file has a mentioned error, this formula will return an error for this file (keep rows with errors), and you can find all files with problems.
Maxim Zelensky Excel Inside
-
Proposed as answer by
Friday, September 8, 2017 11:38 PM
-
Marked as answer by
Imke FeldmannMVP
Sunday, September 17, 2017 6:42 AM
-
Proposed as answer by
Файлы к уроку:
- Для спонсоров Boosty
- Для спонсоров VK
Ссылки:
- Страница курса
- Плейлист YouTube
- Плейлист ВК
Описание
Мы собираемся импортировать таблицу с этой страницы. Когда мы пытаемся изменить тип данных для столбцов с датой, то возвращается столбец с ошибкой DateFormat.Error.
Решение
Если не получается преобразовать столбец с текстом в дату, то значит в строке присутствуют лишние символы.
С помощь функции Text.Length мы можем убедиться, что длина строки намного больше, чем ожидается. Мы видим 10 символов, но функция возвращает 15.
С помощью функции Text.Start извлечем 1 символ сначала строки. Потом получим код этого символа с помощью функции Character.ToNumber. Получаем 8206.
Далее воспользуемся функционалом замены подстроки и исправляем получившуюся формулу:
Table.ReplaceValue(
get_table,
Character.FromNumber(8206),
"",
Replacer.ReplaceText,
{
"Дата начала купонного периода",
"Дата выплаты купона",
"Дата фиксации списка держателей"
}
)
Примененные функции
- Text.Length
- Text.Start
- Character.ToNumber
- Web.Page
- Web.Contents
- Table.SelectRows
- Table.ReplaceValue
- Character.FromNumber
- Replacer.ReplaceText
- Table.TransformColumnTypes
Код
let
source = Web.Page(
Web.Contents(
"https://www.moex.com/ru/issue.aspx?board=TQCB&code=RU000A102036#/bond_4"
)
),
get_table = Table.SelectRows(
source,
each ([ClassName] = "coupon-payment-schedule__table")
){0}[Data],
cols_replace = Table.ReplaceValue(
get_table,
Character.FromNumber(8206),
"",
Replacer.ReplaceText,
{
"Дата начала купонного периода",
"Дата выплаты купона",
"Дата фиксации списка держателей"
}
),
cols_types = Table.TransformColumnTypes(
cols_replace,
{
{"Дата начала купонного периода", type date},
{"Дата выплаты купона", type date},
{"Дата фиксации списка держателей", type date}
}
)
in
cols_types
Этот урок входит в курс Работа с ошибками в Power Query
Номер урока | Урок | Описание |
---|---|---|
1 | Power Query Ошибки №1. Измененный тип, Неверная фильтрация в UI | Почему возникает ошибка Expression.Error Столбец таблицы не найден? Одна из причин — шаг Измененный тип. Почему в вашей итоговой таблицы не все данные, которые должны там оказаться? Вероятно вы совершили ошибку неверной фильтрации в пользовательском интерфейсе. |
2 | Power Query Ошибки №2. Фиксированная ширина столбца Excel | В этом уроке вы узнаете как отключить автоматическое определение ширины столбцов таблицы Excel при обновлении запроса Power Query. |
3 | Power Query Ошибки №3. Formula Firewall, запрос ссылается на… | Вы работаете в Power Query, но при попытке обновить все возникает ошибка Formula Firewall, запрос ссылается на… Что делать? Как исправить? |
4 | Power Query Ошибки №4. Доступ к веб-содержимому | В этом уроке вы узнаете как избавиться от надоедливого окна доступ к веб-содержимому. |
5 | Power Query Ошибки №5. Количество столбцов CSV | При импорте CSV получается неверное количество столбцов. |
6 | Power Query Ошибки №6. Не получается изменить тип данных (DataFormat.Error) | При попытке изменить тип данных с текстового на дату возникает ошибка DataFormat.Error. |
7 | Power Query Ошибки №7. Разные имена листов | В этом уроке мы решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. |
8 | Power Query Ошибки №8. Разные имена листов 2 | В этом уроке мы другими способами решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. |
9 | Power Query Ошибки №9. Разные имена столбцов, Table.TransformColumnNames | В этом уроке мы разберем проблему, когда нам присылают файлы, в которых всегда столбцы названы по-разному. |
10 | Power Query Ошибки №10. Как развернуть все столбцы | В этой задаче мы научимся разворачивать все столбцы табличного столбца не перечисляя каждый из них хардкодом. |
11 | Power Query Ошибки №11. Подключиться к последнему файлу | В этом уроке мы научимся подключаться к самому свежему файлу из нужной нам папки с нужным названием. |
12 | Power Query Ошибки №12. Консолидация и MissingField.Type | Бывает вы ходите выполнить добавление таблиц друг под другом с предварительной обработкой, но получаете ошибку Expression.Error: Столбец таблицы не найден. |
13 | Power Query Ошибки №13. Удалить пустые столбцы | Как без хардкода автоматически удалить пустые столбцы в Power Query, которые есть в импортируемой таблицы. Разберем 2 способа. Один из них прост до безобразия, а для второго понадобится функция Table.Profile. |
14 | Power Query Ошибки №14. Удалить лишние пробелы | В этом уроке мы разберем еще 1 способ удалить лишние пробелы в текстовом столбце Power Query. Для этого мы повторим и изучим несколько новых функций. Text.SplitAny List.Select Character.FromNumber Text.Combine |
15 | Power Query Ошибки №15. Плохо структурированный TXT | Как быть, если текстовый файл разбивается на столбцы неправильно? |
16 | Power Query Ошибки №16. При округлении не совпадает общая сумма | Разберем ошибку, которая возникает при необходимости разбить число на определенные доли, а результаты округлить до двух знаков после запятой. В такой ситуации может получиться, что сумма слагаемых не будет равняться исходному числу. |
17 | Power Query Ошибки №17. Удаление дубликатов, Подводные камни Table. Distinct | Что может пойти не так при использовании функции удаления дубликатов. |
18 | Power Query Ошибки №18. Удаление дубликатов 2, Подводные камни Table. Distinct | Что может пойти не так при использовании функции удаления дубликатов. Другое решение данной проблемы. |
19 | Power Query Ошибки №19. Неверная разбивка на столбцы | При импорте данных из CSV или TXT разбивка на столбцы происходит неправильно. |
20 | Power Query Ошибки №20. Пустая таблица при импорте Excel (XLSX, XLS) | Вы пытаетесь подключиться к книге Excel, но импортируется пустая таблица. |
21 | Power Query Ошибки №21. Подключение к PostgreSQL, Установка драйвера | Вы пытаетесь подключиться к PostgreSQL и получаете ошибку «Перед использование этого соединителя необходимо установить один или несколько дополнительных компонентов» |