Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Advocate V
Advocate V

Cannot convert value [Table] to type Table

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.



    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}})
    #"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.



Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors