Cannot convert a value of type Table to type Binary.

[jeongkim]

Hi,

It's randomly get error to get Table under custom column. 

It sometimes works if I duplicate and work on other dataset. 

Please help. 

let
// Get list of files in SharePoint folder
Source = SharePoint.Contents("https://nokia.sharepoint.com/sites/XXXXXXXXX-XXXXXXX"),
// Get list of files in SharePoint folder
Documents = Source{[Name="Documents"]}[Content],
// Get list of files in SharePoint folder
#"LGUplus Site Progress" = Documents{[Name="LGUplus Site Progress"]}[Content],
// Get list of files in SharePoint folder
#"TOSS service PO_master" = #"LGUplus Site Progress"{[Name="TOSS service PO_master"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"TOSS service PO_master",{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Backup for Previous Data" = #"Added Custom"{[Name="Backup for Previous Data"]}[Custom]
in
#"Backup for Previous Data"

[Anonymous]

Hi @jeongkim ,

During my investigation I have found users in the past who have had similar experiences to you.

For original post, you may refer to:Solved: Expression.Error: We cannot convert a value of typ... - Microsoft Fabric Community

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Thejeswar]

Hi @jeongkim ,

In your M Query I see that you are adding a Custom Column with the content of the excel workbook. I assume the content of your workbook is a table. 

To this custom column that holds the entire content of your excel workbook, you are assigning the binary data type, which can have only 2 values (1 and 0). This is the reason it is showing as error.

You should keep the datatype to preferably the same as the original content.

By the way, I am not really sure what you are trying to do here by adding a new custom column that contains the data of the entire workbook tables.

In case you are doing this to extract the tables from the workbook, you can do that by just clicking the table link in the first column to extract the respective tables

Regards,

[jeongkim]

Hi, I'm still wondering below part:

To this custom column that holds the entire content of your excel workbook, you are assigning the binary data type, which can have only 2 values (1 and 0). This is the reason it is showing as error.

You should keep the datatype to preferably the same as the original content.

binary type can only have 1 and 0, how can I understand it? 

[Thejeswar · Accepted solution]

@jeongkim ,

It is the property of the Binary data type. Refer the below ss for the example

Also in Microsoft Learn I see that Binary data type is not supported in Power BI and will throw an error if used

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

Regards,

[jeongkim]

Thanks for your reply. 

If I click table link manually, can I get both 'Backup for previous data' and 'Weekly Data'?

As I recall, I have to choose one of them but I need both data in one single sheet. 

[Thejeswar]

@jeongkim ,

Create two queries with one table in each one of them by clicking the table link and append these two queries to make a single table out of them.

This Final table you can use for any purpose

Otherwise you can click on the arrow at the top as shown above and expand all tables at once into a single table

However I would recommend this only if the two tables are identical

Regards,

[jeongkim]

I used to implement that way but two things make risk.

1. Too many dataset created so I wanted to make it as minimum as I can. 

2. Manual clicking expands columns with specific column names such Column1, Column2,  ...
this occurs error any small change in total number of columns.

[Thejeswar]

@jeongkim ,

As I said in my earlier message, it is ok to expand all at once only if the tables are identical. If there are differences in your tables, the expanded tables will reflect those differences.

[Thejeswar]

@jeongkim ,

Post expanding, you have to promote header using the option shown in the below screenshot to get the right column names

If this helps, kindly mark it as a Solution! Appreciate a Kudo !

[jeongkim]

I did. 

I think we are misunderstanding each.. 

Thanks though