Skip to main content
cancel
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.

Reply
Veles
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.

 

 

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.

 

1 REPLY 1

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors