topic Re: We cannot convert the value "[Table]" to type Table. in Report Server

We cannot convert the value "[Table]" to type Table.

MI — Fri, 07 Jan 2022 05:34:26 GMT

Hi,

I donot face any error in Power BI Desktop. but while running same report on server, I face below error. Waiting for support.

We cannot convert the value "[Table]" to type Table.

Here is the M query.

let
Source = Folder.Files("\\IPACK-SVR-FILE\Production\10-Waste\Employee of the Month"),
RemoveOtherColumns = Table.SelectColumns(Source,{"Content", "Name"}),
SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000),
ListInput = Table.ToRows(RemoveOtherColumns),
ConvertOneFile = (InputRow as list) =>
let
BinaryIn = InputRow{0},
FileName = InputRow{1},
BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64),
SplitUpText = SplitTextFunction(BinaryText),
AddFileName = List.Transform(SplitUpText, each {FileName,_})
in
AddFileName,
ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile(_)),
CombineLists = List.Combine(ConvertAllFiles),
ToTable = #table(type table[Name=text,Pic=text],CombineLists),
AddIndexColumn = Table.AddIndexColumn(ToTable, "Index", 0, 1),
#"Filtered Rows" = Table.SelectRows(AddIndexColumn, each ([Name] <> "Thumbs.db")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Name", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Name.1", "Name.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name.1", "Name"}})
in
#"Renamed Columns"

Re: We cannot convert the value "[Table]" to type Table.

lbendlin — Sun, 09 Jan 2022 00:32:08 GMT

Are there any empty (zero length) files in that folder? Or files that are being written to while you run your query?

Re: We cannot convert the value "[Table]" to type Table.

MI — Sun, 09 Jan 2022 12:40:48 GMT

Hi,

No, there is no empty (zero length) files in that folder. attached query converts high resolution Pictures to display in BI. This works perfect in Desktop but gives error on server

Re: We cannot convert the value "[Table]" to type Table.

lbendlin — Sun, 09 Jan 2022 15:56:30 GMT

Does it fail completely in the service or only for certain files? What's the average size of the image files, and how many are there?

Re: We cannot convert the value "[Table]" to type Table.

MI — Mon, 10 Jan 2022 04:07:44 GMT

It's failing completely. There are 5 files with average size of each file is 450 Kb

Re: We cannot convert the value "[Table]" to type Table.

lbendlin — Tue, 11 Jan 2022 01:04:02 GMT

What gateway version are you using? Can the gateway VMs see the file share? Do you have an option to put the files on a sharepoint or Onedrive instead?

Re: We cannot convert the value "[Table]" to type Table.

MI — Wed, 12 Jan 2022 08:21:34 GMT

Dear Ibendlin,

Thank you for your support. I have found the solution for this problem. actually issue was with Privacy setting of the data source. after fix this my problem is resolved. Thank u

Re: We cannot convert the value "[Table]" to type Table.

v-henryk-mstf — Thu, 13 Jan 2022 01:42:56 GMT

Hi MI ,

If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.🙂

Best Regards,
Henry