Power query xlsb error - Исправление ошибок и поиск оптимальных решений проблем

Fix Error When Reading XLSB File In Power Query

A XLSB File error can come to you by surprise. Recently one of my clients built a Power Query solution. In one of the steps, the query ran into an unknown error. The query started with data from a CSV file. And then merges different Excel files to enrich the dataset. One of the merges results in the following error message.

“An error occurred in the ‘TBL_MAPPING_B2G’ query. DataFormat.Error: External table is not in the expected format. Details: TBL_MAPPING_B2G.xlsb”

This message indicates the name of the Query that causes an error. Yet it’s not very clear what’s going on. The table is not in the expected format…

Error Checking

So I did some basic error checking. This was my approach.

First I examined the source data. Does the source data contain any errors? Strange formatting? Perhaps symbols that are not accepted? Inspecting the source file didn’t give me any indication of strange symbols or errors. Data seemed structured, without errors or strange symbols.
Next, I looked at what the query looked like after importing it in Power Query. Are columns perhaps defined with the wrong data type? Do errors occur in any of the columns? Again, I found nothing noteworthy.
The error message occurs in the step where the base query merged with ‘TBL_MAPPING_B2G’. To make sure nothing went wrong there, I perform the merge once more by deleting the old step and doing the merge again. The error message appeared again. The riddle continued.

I’m slowly running out of ideas. The query involves many different merges. So how is this particular merge different from the others?

Solution

The clue to the solution appeared when inspecting the source files. The merge resulting in an error is the only file saved with a .XLSB file type. All other files types are either .XLSX or .CSV.

Perhaps this influences how Power Query reads the data. Since the file causing the error contains macro’s, I save it as .XLSM. Next I change the source file in Power Query, press refresh and VOILA! The error message is gone!

That’s strange. The error message indicates the table is not in the expected format, yet changing the file extension solves the problem.

The exact reason of the XLSB File Error is still unclear to me. It appears there is a bug in Power Query when reading the .XLSB file. Yet changing the file type seems to solve the issue.

Checking Google for similar issues doesn’t give much input either. It’s not much, but on this page someone seems to have a similar problem. Until it’s fixed, the way to go is to change the file format.

Have you experienced similar issues? And what was your solution to it? Please let me know in the comments below. Also make sure to check out how to import files from a Folder if you want to expand your Power Query superpowers!

Источник

Всем доброго времени суток, столкнулся с тем, что в запросе Power Query который обрабатывает xlsb файл возникает ошибка сразу после открытия файла с запросом.
Кто сталкивался? Как решается эта проблема?
На англоязычных сайтах просто советуют принудительно обновить и якобы помогает.
Прошу отзывов только тех, кто сталкивался с этим.

Изменено: Konstantin Zhi — 13.07.2019 22:25:47

PooHkrd

Пользователь

Сообщений: 6602
Регистрация: 22.02.2017

Excel x64 О365 / 2016 / Online / Power BI

15.07.2019 09:14:55

Цитата
Konstantin Zhi написал: Кто сталкивался? Как решается эта проблема?

Так вы воспользуйтесь

поиском по форуму

и сразу увидите кто и сколько раз сталкивался. Обсуждался вопрос раз 10 уже.
Для подключения к бинарным файлам PQ использует, если мне не изменяет мой склероз, ACE.OLEDB. И вот у этой штуковины регулярно возникают проблемы при обращении к файлам в бинарном формате, это xlsb и xls. И даже если считать инфу получается, то делает это очень медленно.
Так что настоятельно рекомендую в качестве источника данных использовать текстовые файлы — никаких глюков и скорость чтения раз в 5 выше чем из того же xlsx.

Изменено: PooHkrd — 15.07.2019 09:15:22

Вот горшок пустой, он предмет простой…

не знаю поверите Вы мне или нет, я искал по форуму, только в запросе я переставил xlsb и Power Query — мне ничего не нашло..
За рекомендацию — спасибо, но я вынужден использовать именно xlsb формат, т.к. в том файле есть UDF, она обрабатывает первичные данные (выгрузку из 1с) возвращая цифру отступа в ячейке — это единственный способ потом обработать строки в запросе.

Правильно я понял Ваш ответ по сути, что нормального решения проблемы нет?

PooHkrd

Пользователь

Сообщений: 6602
Регистрация: 22.02.2017

Excel x64 О365 / 2016 / Online / Power BI

16.07.2019 14:05:23

Konstantin Zhi, а кто мешает UDF запихать в xlsm формат? И никаких клюков с запросами PQ.

Цитата
Konstantin Zhi написал: это единственный способ потом обработать строки в запросе

Это вам здесь такое сказали?
Покажите пример файла и обрисуйте задачу, которую нужно в PQ реализовать. Но это уже в новой теме.

Вот горшок пустой, он предмет простой…

Konstantin Zhi

Пользователь

Сообщений: 1177
Регистрация: 01.01.1970

16.07.2019 14:19:21

Цитата
PooHkrd написал: а кто мешает UDF запихать в xlsm формат

если так сделать, то постоянно будут выскакивать сообщения, что эта книга содержит макросы — что не удобно.. как-то так..
Вы, вероятно, не очень внимательно прочитали.. UDF определяет цифру отступа… Это как-бы формат ячейки.. А Power Query форматы не видит…

artyrH

Пользователь

Сообщений: 2054
Регистрация: 01.07.2018

16.07.2019 14:55:30

Цитата
Konstantin Zhi написал: Power Query форматы не видит.

на этом форуме увидит

PooHkrd

Пользователь

Сообщений: 6602
Регистрация: 22.02.2017

Excel x64 О365 / 2016 / Online / Power BI

16.07.2019 15:10:37

Цитата
Konstantin Zhi написал: А Power Query форматы не видит…

А вот это — спорное утверждение. Например вот

тема

от Максима Зеленского с функцией, которая как раз определяет иерархию вложенности уровней на основе группировки строк. Функция лезет в xml структуру файла, находит для каждой строки вложенность и джойнит её уже к содержимому этих строк.
Так что возвращаемся к моему посту за №4.

Вот горшок пустой, он предмет простой…

Konstantin Zhi

Пользователь

Сообщений: 1177
Регистрация: 01.01.1970

16.07.2019 15:37:45

Цитата
PooHkrd написал: на основе группировки строк

в исходном файле не будет группировки строк, все равно выкладывать?

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Почему бы и нет. Выкладывайте. Только в новой теме, как новую задачу. Вот горшок пустой, он предмет простой…

PooHkrd

Пользователь

Сообщений: 6602
Регистрация: 22.02.2017

Excel x64 О365 / 2016 / Online / Power BI

#10

10.06.2020 18:12:25

Вроде бы нашел решение проблемы. Можно обращаться к бинарникам и не ловить при этом ошибки о некорректном формате. Для этого придется использовать функцию Binary.Buffer(). Как это выглядит? При создании запроса к файлу автоматически генерится код вида

Код
= Excel.Workbook( File.Contents("C:Folderfile.xlsb" ), null, true)

Так вот его нужно переделать так:

Код
= Excel.Workbook( Binary.Buffer( File.Contents("C:Folderfile.xlsb" ) ), null, true )

В таком случае все содержимое файла уедет в память и дальнейшее обращение функции Excel.Workbook будет происходить именно с памятью, а не с диском. Таким образом мы избегаем конфликта этой функции, которая пытается сделать предпросмотр данных через коннектор ACE.oledb, который к этому не очень предрасположен. Т.е. сначала коннектор полностью грузит содержимое файла в память, и только потом уже PQ лезет в память своими потными ручонками.
Проверял, схема рабочая, НО выгрузка очень медленная. А с более-менее большими файлами работать просто мучение. Так что если уж очень надо, то пользуйтесь, но лучше все же текст или xlsx. Возможно такой же прием поможет избежать и глюков при работе с файлами формата xls, но это не проверял, так что не гарантирую.
Всех благ.

Изменено: PooHkrd — 10.06.2020 18:16:13

Вот горшок пустой, он предмет простой…

Dyroff

Пользователь

Сообщений: 1132
Регистрация: 01.01.1970

#11

12.06.2020 10:14:33

Цитата
Konstantin Zhi написал: если так сделать, то постоянно будут выскакивать сообщения, что эта книга содержит макросы — что не удобно..

Есть два способа решения этого:

1) Файл-> Параметры-> Центр управления безопасностью-> Параметры центра управления безопасностью-> Параметры конфиденциальности-> снять флажок с Удалять персональные данные из свойств файла при сохранении
2) Описан в

этой

теме пользователем ZVI :

Вот этот код нужно записать в модуль ЭтаКнига персональной книги макросов, сохранить эту книгу макросов и перезагрузить Excel:

Код

Private WithEvents App As Application  
 
Private Sub Workbook_Open()  
 Set App = Application  
End Sub  
 
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)  
 On Error Resume Next  
 If Wb.RemovePersonalInformation Then Wb.RemovePersonalInformation = False  
End Sub

Изменено: Dyroff — 12.06.2020 10:20:58

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

PooHkrd

Пользователь

Сообщений: 6602
Регистрация: 22.02.2017

Excel x64 О365 / 2016 / Online / Power BI

#12

16.06.2020 08:04:00

Попробовал еще один вариант. Вроде тоже отрабатывает без ошибок, и так же медленно. Но по идее такой подход должен жрать меньше памяти:
Напоминаю, что это строка кода, которую генерит UI при обращении к файлу.

Код
= Excel.Workbook( File.Contents("C:Folderfile.xlsb" ), null, true)

а можно поменять true на false

Код
= Excel.Workbook( File.Contents("C:Folderfile.xlsb" ), null, false)

Таким образом опять же отключается частичный просмотр файла со стороны Excel.Workbook и пропадает конфликт между коннектором который тащит данные собственно из файла и функцией, которая уже разбирается в бинарнике, где там и что лежит.
А вообще щас попробовал через обновленный O365 тягать бинарники — открывает только в путь безо всяких ухищрений и ошибок. Так что может и починили чего все таки.
З.Ы. Потестировал данный способ в течении месяца, оказался не надёжным. Иногда ошибки таки вылезают. Вариант с Binary.Buffer надежен как скала. Ошибок нет. Но какой же он ме-е-едленный.

Изменено: PooHkrd — 07.10.2020 13:58:18

Вот горшок пустой, он предмет простой…

С бинарниками (xlsb и xls) есть и другая проблема, недавно где-то пробежала инфа от разработчиков, что данные из них посредством этого ACE читаются не по фактическому значению, а по отображаемому. иными словами, читается не Cell.Value, а Cell.Text

PooHkrd

Пользователь

Сообщений: 6602
Регистрация: 22.02.2017

Excel x64 О365 / 2016 / Online / Power BI

#14

07.10.2020 14:08:11

Цитата
Максим Зеленский написал: читается не Cell.Value, а Cell.Text

Тоже погонял эту тему, и как говорится, тут тоже не все так однозначно. Тестировал только с xlsb
Например, если тягать данные без использования Binary.Buffer не с листа, а из «умной» таблицы, то тащит именно Cell.Value. Но тогда ждите ошибки.
А вот после загона бинарника в память, читать можно откуда угодно, тащит исключительно Cell.Text. Зато без ошибок!
Легко проверяется на считывании столбцов с датами.
В результате код присваивающий типы столбцам должен быть разным для вариантов с буфером и без. :idea:
В общем чего они там по-наворотили, без поллитры хрен разберешься.

Вот горшок пустой, он предмет простой…

New Пользователь Сообщений: 4404 Регистрация: 06.01.2013	PQ на сколько я знаю около 4-5 лет, жалко, что они за столько лет не могут решить эту проболему

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI	Вот горшок пустой, он предмет простой…

Glutton

Пользователь

Сообщений: 3
Регистрация: 16.10.2015

#17

17.05.2021 14:55:42

Цитата
PooHkrd написал: = Excel.Workbook( Binary.Buffer( File.Contents(«C:Folderfile.xlsb» ) ), null, true )

Добрый день! Не подскажите, как такое применить к папке?

PooHkrd

Пользователь

Сообщений: 6602
Регистрация: 22.02.2017

Excel x64 О365 / 2016 / Online / Power BI

Glutton, лучше не надо к папке. Будет медленно, и очень большая вероятность, что оперативки не хватит. Один файл в случае крайней необходимости еще можно. Настоятельно рекомендую переделать источники в xlsx либо в текст.

Вот горшок пустой, он предмет простой…

Glutton Пользователь Сообщений: 3 Регистрация: 16.10.2015	#19 18.05.2021 12:40:26 Спасибо! Да, видимо, это единственное здравое решение, а так хотелось выкроить шесть шапок из овцы.

Источник

All forum topics
Previous Topic
Next Topic

shiham

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

‎04-01-2020

12:43 AM

Hi,

My power query not loading the required file into my power bi desktop. I listed the step I followed to load the file from share folder. However, the desktop is working perfectly when I’m using the data source which stored in my local drive.

Follwing URL is the connection to my share folder.

Actually my sharefolder contains two folders. I require only highlighted folder for my working.

So the URL shows all the files when I transform into power query. Despite that I need only highlighted file for my working. So, I filtered only required file.

Please support to rectify the issue.

All forum topics
Previous Topic
Next Topic

4 REPLIES 4

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

‎04-07-2020

08:28 PM

Hey there. You can’t just select the row that contains the files, you have to then select the file itself. Just click on the two arrows icon above the Binary table—good to go!
By the way, XLSB files don’t always play nice with Power Query…

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

‎04-01-2020

10:45 PM

Hi @shiham

Click on «view error» to see details.

Or go to edit queries, see if there is any «Query errors» under the left queries pane.

Share details for further analysis.

You could also check if there is any column where some rows have incorrect data type/format.

Best Regards

Maggie

shiham

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

‎04-02-2020

07:33 AM

Hi @v-juanli-msft

The ‘View Error’ shows as per the below image. On ‘Kept Errors’ it shows the This table is empty. However, According to source, my file ‘Consolidated Sales Report 2019’ contains the table name ‘SalesReport_2019’.

Apart from that, I re-enter my credentials as well in ‘Data source settings’.

Thanks

Shiham

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

Источник

Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui…
When starting a new post, to receive a quicker and more targeted answer,
Please include a sample file in the initial post.

Thread starter

Dokat
Start date

Jan 1, 2017

Hi,

I am trying to run a pivot table using power query. My source data is in another workbook in .xlsb format. When i choose external connections in power pivot and link the data i receive below error message. Has anyone come across similar issue and solve for it?

Hui

Excel Ninja

Have you tried converting the file to an Excel *.xlsm file type and linking to that file?

Is the file in a location that is trusted by Excel?

When i convert it to .xlsm file size increase exponentially. File is 185 mb and it reduces to 76 mb when i convert to .xlsb

Yes file location is trusted by Excel.

Thanks

Hui

Excel Ninja

Don’t worry about 176 MB
Does it load ?

Источник

При попытке внести изменения в базу данных службы 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.

Видео: How to use Microsoft Power BI 2023

При попытке внести изменения в базу данных службы Power BI, например, добавив дополнительные таблицы в базу данных SQL, пользователи Power BI могут столкнуться с различными ошибками формата данных. Некоторые ошибки включают DataFormat.Error: Мы достигли конца буфера или power bi dataformat.error внешней таблицы не в ожидаемом формате.

Как исправить типичные ошибки 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

krosav4ig

Дата: Понедельник, 19.09.2016, 23:45 | Сообщение № 3

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Viper25

Дата: Вторник, 20.09.2016, 12:42 | Сообщение № 4

Viper25

Дата: Вторник, 20.09.2016, 15:28 | Сообщение № 5

krosav4ig

Дата: Четверг, 22.09.2016, 03:43 | Сообщение № 6

«)
),
Книги = Table.CombineColumns(
Table.SelectColumns(
Table.AddColumn(
Файлы,
«Workbook»,
each
Table.SelectRows(
Excel.Workbook([Content]),
each
_[Kind] = «Sheet»
)
),
<«Name», «Folder Path», «Workbook»>
),
<«Folder Path», «Name»>,
Combiner.CombineTextByDelimiter(
«»,
QuoteStyle.None
),
«Файл»
),
Листы = Table.TransformColumns(
Table.ExpandTableColumn(
Книги,
«Workbook»,
<«Name», «Data»>,
<«Лист»,»Data»>
),
<«Data», Table.PromoteHeaders>
),
Столбцы = Table.Distinct(
Table.ExpandListColumn(
Table.AddColumn(
Листы,
«»,
each
Table.ColumnNames([Data])
),
«»
),
<«»>
)[#»»],
Таблица = Table.ExpandTableColumn(
Листы,
«Data»,
Столбцы
)
in
Таблица

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Viper25

Дата: Четверг, 22.09.2016, 09:55 | Сообщение № 7

krosav4ig

Дата: Четверг, 22.09.2016, 14:37 | Сообщение № 8

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Viper25

Дата: Четверг, 22.09.2016, 14:44 | Сообщение № 9

Viper25

Дата: Четверг, 22.09.2016, 14:57 | Сообщение № 10

pupkin-terkin

Дата: Вторник, 27.12.2016, 16:41 | Сообщение № 11

Вот тут ещё пример (выходящий за рамки вопроса) с картинками:
http://www.poweredsolutions.co/2014. y-way-2

Вот тут ещё пример (выходящий за рамки вопроса) с картинками:
http://www.poweredsolutions.co/2014. y-way-2 Автор — pupkin-terkin
Дата добавления — 27.12.2016 в 16:41

telnoff

Дата: Вторник, 12.11.2019, 14:27 | Сообщение № 12

. И ввиду такого названия он автоматически становился первым в списке. Т.е. тебе нужно отфильтровать таблицу с перечнем файлов до того как эти файлы начнут считываться. Делается это в расширенном редакторе.
В Power Bi есть кое какая хитрая система вспомогательных запросов, ты о них не знаешь, но они создаются автоматически и в ходе работы ссылаются на них, так вот этот автоматический запрос также придется редактировать и ставить дополнительный фильтр на исключение временных файлов.

Источник

Adblock
detector

Источник