I have a query that is fine when refreshing on Desktop but when I publish to the service and try and refresh (both manually and by schedule) I get an error saying:
We cannot convert the value "[Table]" to type Table.. The exception was raised by the IDataReader interface.
The issue is where I've used a custom function on each of the sheets on a workbook to do a set of transformations before they are then combined into one dataset.
The first part of the query gets a list of files from SharePoint then expands the Binary for all of them then filters to give me a list of the sheets that I'm interested in.
let
Source = SharePoint.Files("xxx", [ApiVersion = 15]),
#"Filtered Rows3" = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "xxx")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows3", "Data Content", each Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Data Content", "Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "File Name"}}),
#"Expanded Data Content" = Table.ExpandTableColumn(#"Renamed Columns", "Data Content", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Data Content", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Item] <> "Budget" and [Item] <> "Labour>" and [Item] <> "MasterData" and [Item] <> "Sites>" and [Item] <> "Summary")),
#"Removed Columns" = Table.Buffer(Table.RemoveColumns(#"Filtered Rows1",{"Name", "Kind", "Hidden"})),
The next part of the query then applies a custom function to do a set of transformations against each of the tables. Before applying a few more transformations agains the combined dataset.
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "fx_IrelandFlashTx", each fx_IrelandFlashTx([Data])),
#"Expanded fx_IrelandFlashTx" = Table.ExpandTableColumn(#"Invoked Custom Function", "fx_IrelandFlashTx", {"Account", "Flash", "Ref", "Period"}, {"Account", "Flash", "Ref", "Period"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded fx_IrelandFlashTx",{"Account"},#"Site Flash COA",{"Account"},"Site Flash COA",JoinKind.LeftOuter),
#"Expanded Site Flash COA" = Table.ExpandTableColumn(#"Merged Queries", "Site Flash COA", {"COA1"}, {"COA1"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Site Flash COA",{"Account"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"File Name", "Ref", "Period", "COA1"}, {{"Flash", each List.Sum([Flash]), type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Ref", type text}, {"Period", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type",{"File Name"}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Columns2", {"Flash"}, "Scenario", "Period Value"),
#"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Period", "YearPeriod"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2","P","2020",Replacer.ReplaceText,{"YearPeriod"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value", "FxLookup", each Number.FromText([YearPeriod]) - 1, type number),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom2", {"FxLookup"}, FX_Rates, {"YearPeriod"}, "FX_Rates", JoinKind.LeftOuter),
#"Expanded FX_Rates" = Table.ExpandTableColumn(#"Merged Queries1", "FX_Rates", {"FX Rate"}, {"FX Rate"}),
#"Added Custom3" = Table.AddColumn(#"Expanded FX_Rates", "GBP", each Number.Round([Period Value]/[FX Rate],2)),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"FxLookup", "FX Rate"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns3", "Source", each "Flash"),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Period Value", "EUR"}}),
#"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Renamed Columns1", {"GBP", "EUR"}, "Currency", "Period Value"),
#"Rounded Off" = Table.TransformColumns(#"Unpivoted Only Selected Columns1",{{"Period Value", each Number.Round(_, 2), type number}}),
#"Extracted First Characters" = Table.TransformColumns(#"Rounded Off", {{"Ref", each Text.End(_, 3) & "_" & Text.Start(_, 3), type text}})
in
#"Extracted First Characters"The issue seems to be with using the [Table] values in the Data column as part of my custom function. There's not problems with refreshing this in Desktop, just when I publish it to service.
I have another query that works in a similar way that doesn't seem to have any problems. Rather than using the Data column as a parameter I add in an index column. Instead of just opening the relevant table, my custom function instead has to filter the index by the current parameter then open the first table.
