expression error we cannot convert value 117 to type text

[josedc]

Here is my source code. There is no data type tranformation happening in the source. I tried trimming & cleaning but the trimming tends to convert the data to text. I tried replacing blank cells with a value as well. 

And this value 117 is nowhere to be found in my data which is making it hard  to trouble shoot.

let
Source = CostSheets,
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Vendor cost sheet")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Source.Name", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each [Column1] = "MATERIAL NAME" or [Column1] = "PRELIMINARY MATERIAL" or [Column1] = "PRIMARY MATERIAL" or Text.StartsWith([Column1], "Material")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
#"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([MATERIAL NAME] <> "MATERIAL NAME")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Column28", "Column29"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"02443_D237645_0123_HTHR_SIZE16.xlsm", "Source"}})
in
#"Renamed Columns"

[v-shex-msft]

HI @josedc,

I'd like to suggest you take a look at below link has similar error message:

Power Query Error Expression.Error: We cannot convert the value .. to type Text

Regards,

Xiaoxin Sheng

[josedc]

@Xiaoxin thank you for your reply. So the code I posted is at its basic form. I have done data type transformations, step by step queries to see where the issue may arise and everything this post you sent suggest prior making my post.

To give a little background. I combined a little over 5,000 workbooks with the same data structure. When I try to import a random 10-50 workbooks it works with no problem. The moment I reach the 3 digit range issues occur.

Any feedback is appreciated. Thanks again.

[v-shex-msft]

HI @josedc,

>>To give a little background. I combined a little over 5,000 workbooks with the same data structure. When I try to import a random 10-50 workbooks it works with no problem. The moment I reach the 3 digit range issues occur.

I guess it may related to power query limit(parameter characters length reached to limit), you can take a look at below link to know more about this.

Power Query specifications and limits

Regards,

Xiaoxin Sheng