Power query ошибка binary

Such alert could appear when you try to use Power BI connector on Excel file. It’s understandable if the source file is corrupted and can’t be opened in Excel. However, it looks strange if Excel opens the file in question and shows nothing wrong.

Based on our experience above is usually means what something is wrong with XML scheme of the Excel workbook.

Mushup trace (Data->New Query->Query Options->Diagnostics->Enable tracing) could give some additional information, but often not enough to find the reason.

We had two main scenarios

• XML scheme is not complete

Usually if Excel file was generated by third-party tool. Such tool could generate quite limited XML scheme which is enough to open the file in Excel and to work with it, but not enough for Power BI connector. As an example, trace log shows

[DataFormat.Error] The input couldn't be recognized as a valid Excel document.rnStackTrace:n…
…
[DataFormat.Error] We couldn't find a part named '/xl/sharedStrings.xml' in the Excel package.rnStackTrace:n…

Such case is easy to fix – it’s enough to open the file in Excel and save it (without any changes) – Excel is clever enough to fix the scheme. For the routine regular tasks we use poweshell script which does exactly the same in background.

• There is the link within Excel file which is not recognizable as valid

Usually if Excel file is synced/kept with some cloud storage. One of the variants, wrong link could appear with copy/paste from another such file. That could be active link in one of the cells; or the link within conditional formatting formula; or even the link which actually isn’t used by Excel but kept somewhere inside the scheme. For example, in one of the files I found in Data->Consolidate->All references the link like

'\drive.tresorit.com@7235Tresors….[file.xlsx]Sheet'!$AC$6:$AC$357

on the file which was deleted long ago and isn’t used, but for some strange reason the link was kept within the scheme.

Unfortunately for such case trace log doesn’t give enough information to localize the issue, it looks like

[DataFormat.Error] The input couldn't be recognized as a valid Excel document.rnStackTrace:n…
…
nExceptionType: System.UriFormatException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089rnMessage: Invalid URI: The hostname could not be parsed.rnStackTrace:n

Perhaps I have not enough knowledge for more straight forward localization of the problem, but the only way is to exclude Excel file parts one by one and check if the issue disappeared. Another way could be to unzip Excel file and check if wookbook.xml or sheetNN.xml have something suspicious inside.

Such alert could appear when you try to use Power BI connector on Excel file. It’s understandable if the source file is corrupted and can’t be opened in Excel. However, it looks strange if Excel opens the file in question and shows nothing wrong.

Based on our experience above is usually means what something is wrong with XML scheme of the Excel workbook.

Mushup trace (Data->New Query->Query Options->Diagnostics->Enable tracing) could give some additional information, but often not enough to find the reason.

We had two main scenarios

• XML scheme is not complete

Usually if Excel file was generated by third-party tool. Such tool could generate quite limited XML scheme which is enough to open the file in Excel and to work with it, but not enough for Power BI connector. As an example, trace log shows

[DataFormat.Error] The input couldn't be recognized as a valid Excel document.rnStackTrace:n…
…
[DataFormat.Error] We couldn't find a part named '/xl/sharedStrings.xml' in the Excel package.rnStackTrace:n…

Such case is easy to fix – it’s enough to open the file in Excel and save it (without any changes) – Excel is clever enough to fix the scheme. For the routine regular tasks we use poweshell script which does exactly the same in background.

• There is the link within Excel file which is not recognizable as valid

Usually if Excel file is synced/kept with some cloud storage. One of the variants, wrong link could appear with copy/paste from another such file. That could be active link in one of the cells; or the link within conditional formatting formula; or even the link which actually isn’t used by Excel but kept somewhere inside the scheme. For example, in one of the files I found in Data->Consolidate->All references the link like

'\drive.tresorit.com@7235Tresors….[file.xlsx]Sheet'!$AC$6:$AC$357

on the file which was deleted long ago and isn’t used, but for some strange reason the link was kept within the scheme.

Unfortunately for such case trace log doesn’t give enough information to localize the issue, it looks like

[DataFormat.Error] The input couldn't be recognized as a valid Excel document.rnStackTrace:n…
…
nExceptionType: System.UriFormatException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089rnMessage: Invalid URI: The hostname could not be parsed.rnStackTrace:n

Perhaps I have not enough knowledge for more straight forward localization of the problem, but the only way is to exclude Excel file parts one by one and check if the issue disappeared. Another way could be to unzip Excel file and check if wookbook.xml or sheetNN.xml have something suspicious inside.

Файлы к уроку:

• Для спонсоров 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

Hello Community!

I’m facing an issue in Power Query and can’t seem to find a reason to why its happening. I could really use some advice on the reason to the problem. 

This is the error I’m getting  —

Expression.Error: We cannot convert the value "[Binary]" to type Binary. 

When am I facing it? — When trying to merge or append 2 queries. 

What are the data soruces of the two queries? — 2 Excel files kept in folders in 2 different SharePoint sites. The privacy of both are kept as ‘Private’. (I’ve also faced this issue while trying to merge an SQL query with an Excel)