I am trying to update my dataset on Power BI Web, but it is not working. I am able to update my dataset on Power BI Desktop without errors. Código de erro subjacente:-2147467259 Table: Budget.Mensagem de erro subjacente:We cannot convert the value "[Function]" to type Function.DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259Microsoft.Data.Mashup.ValueError.Reason:Expression.ErrorMicrosoft.Data.Mashup.ValueError.Value:[Function] The report has two folders as data sources ( all files in both folders ) and two other xlsx file. Besides that, it also has 3 Power Query functions. Two of them is to format the files located on each folder data source, the other one is a function to trim and clean a given table. I am using the lastest version of Power BI Desktop and Power BI Personal Gateway, and the gateway works correctly with other reports. Thanks.

Hi Anonymous, To be honest, I didn't find anything wrong with the code. I think here could be the solution. Please give it a try. Best Regards, Dale

Power BI dataset refresh error - We cannot convert the value "[Function]" to type Function

4 Replies

v-jiascu-msft

Microsoft Employee

8 years ago

Hi Anonymous,

It seems something wrong in the code. Can you share it? I would suggest you change the function name and try again if a function is named "Function".

Best Regards,

Dale

Anonymous

Not applicable

8 years ago

v-jiascu-msft, there is not a function named "Function". These are the codes:

1- General table formatting

let
    FormatBudget = (RawOpex as binary) =>
    let
        #"Imported Table" = Csv.Document(#"RawOpex",[Delimiter="#", Columns=44, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        TrimAndClean = TableTrimAndClean(#"Imported Table"),
        #"Promoted Headers" = Table.PromoteHeaders(#"TrimAndClean", [PromoteAllScalars=true]),
        #"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        #"Removed Other Columns" = Table.SelectColumns(#"Removed Blank Rows",{"Ano", "C.Custo", "Conta", "JAN Budget", "FEV Budget", "MAR Budget", "ABR Budget", "MAI Budget", "JUN Budget", "JUL Budget", "AGO Budget", "SET Budget", "OUT Budget", "NOV Budget", "DEZ Budget"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Ano", "C.Custo", "Conta"}, "Attribute", "Value"),
        #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Columns","JAN Budget","1",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","FEV Budget","2",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","MAR Budget","3",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","ABR Budget","4",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","MAI Budget","5",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","JUN Budget","6",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","JUL Budget","7",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","AGO Budget","8",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","9SET Budget","9",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","9SET Budget","9",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","SET Budget","9",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","OUT Budget","10",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","NOV Budget","11",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","DEZ Budget","12",Replacer.ReplaceText,{"Attribute"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value14",{{"Attribute", Int64.Type}, {"C.Custo", type text}, {"Conta", Int64.Type}, {"Ano", Int64.Type}, {"Value", type number}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Mês"}}),
        #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"C.Custo"},#"Centro de Custos",{"C.Custo"},"Centro de Custos",JoinKind.LeftOuter),
        #"Expanded Centro de Custos" = Table.ExpandTableColumn(#"Merged Queries", "Centro de Custos", {"ID"}, {"ID"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Expanded Centro de Custos",{"ID", "Ano", "C.Custo", "Conta", "Mês", "Value"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"ID", "CCustoID"}}),
        #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns1",{"Conta"},#"Conta Contábil",{"Conta"},"Conta Contábil",JoinKind.LeftOuter),
        #"Expanded Conta Contábil" = Table.ExpandTableColumn(#"Merged Queries1", "Conta Contábil", {"ID"}, {"ID"}),
        #"Renamed Columns2" = Table.RenameColumns(#"Expanded Conta Contábil",{{"ID", "ContaID"}}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"CCustoID", "ContaID", "Ano", "C.Custo", "Conta", "Mês", "Value"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"C.Custo", "Conta"}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Ano", type text}, {"Mês", type text}}, "pt-BR"),{"Ano", "Mês"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
        #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}}),
        #"Renamed Columns3" = Table.RenameColumns(#"Changed Type2",{{"Merged", "Date"}, {"Value", "Budget"}}),
        #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns3", {{"CCustoID", type text}, {"ContaID", type text}, {"Date", type text}}, "pt-BR"),{"CCustoID", "ContaID", "Date"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Link")
    in
        #"Merged Columns1"
in
    FormatBudget

2- General table formatting

let
    FormatTable = (BinaryRazao as binary) =>
let
        #"Imported File" = Csv.Document(BinaryRazao, [Delimiter="|", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        TrimAndClean = TableTrimAndClean(#"Imported File"),
        PromotedHeaders = Table.PromoteHeaders(TrimAndClean),
        ColumnNames = Table.ColumnNames(PromotedHeaders),
        ToTable = Table.FromList(ColumnNames),
        Trim = Table.TransformColumns(ToTable,{{"Column1", Text.Trim, type text}}),
        Clean = Table.TransformColumns(Trim,{{"Column1", Text.Clean, type text}}),
        Header = Table.Transpose(Clean),
        HeaderAppendedWithTAble = Table.Combine({Header, #"Imported File"}),
        PromoteHeaders1 = Table.PromoteHeaders(HeaderAppendedWithTAble, [PromoteAllScalars=true]),
        SkipFirstRows = Table.Skip(PromoteHeaders1, 3),
        #"Removed Other Columns" = Table.SelectColumns(SkipFirstRows,{"Conta", "Lote", "Data Doc", "NumDoc", "DocOrigem", "Histórico", "Débito", "Crédito", "Centro de Custos"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Conta", Int64.Type}, {"Lote", Int64.Type}, {"Data Doc", type date}, {"NumDoc", type text}, {"DocOrigem", type text}, {"Histórico", type text}, {"Débito", type number}, {"Crédito", type number}, {"Centro de Custos", type text}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type1",{"Centro de Custos"},#"Centro de Custos",{"C.Custo"},"Centro de Custos.1",JoinKind.LeftOuter),
        #"Expanded Centro de Custos.1" = Table.ExpandTableColumn(#"Merged Queries", "Centro de Custos.1", {"ID"}, {"ID"}),
        #"Renamed Columns" = Table.RenameColumns(#"Expanded Centro de Custos.1",{{"ID", "CCustoID"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"CCustoID", "Conta", "Lote", "Data Doc", "NumDoc", "DocOrigem", "Histórico", "Débito", "Crédito", "Centro de Custos"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Centro de Custos"}),
        #"Merged Queries1" = Table.NestedJoin(#"Removed Columns",{"Conta"},#"Conta Contábil",{"Conta"},"Conta Contábil",JoinKind.LeftOuter),
        #"Expanded Conta Contábil" = Table.ExpandTableColumn(#"Merged Queries1", "Conta Contábil", {"ID"}, {"ID"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Expanded Conta Contábil",{{"ID", "ContaID"}}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"CCustoID", "ContaID", "Data Doc", "Conta", "Lote", "NumDoc", "DocOrigem", "Histórico", "Débito", "Crédito"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Conta"}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns1", {{"CCustoID", type text}, {"ContaID", type text}, {"Data Doc", type text}}, "pt-BR"),{"CCustoID", "ContaID", "Data Doc"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Link")
    in
        #"Merged Columns"
in
    FormatTable

3- Create a Calendar table from two given years

let
   Calendar = (From as number, To as number) =>
   let
        StartDate = #date(From, 1, 1),
        EndDate = #date(To+1, 1, 1),
        Diff = EndDate - StartDate,
        Days = Duration.Days(Diff),
        DateList = List.Dates(StartDate, Days, #duration(1, 0, 0, 0)),
        Dates = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in 
        Dates
in
    Calendar

4- Trim and Clean as columns in a table

let
    TrimAndClean = (_table as table) =>
    let
        ColumnNames = Table.ColumnNames(_table),
        ColumnByType = List.Transform(ColumnNames, each {_, type text}),
        ChangedType = Table.TransformColumnTypes(_table, ColumnByType),
        ColumnByOperationTrim = List.Transform(ColumnNames, each {_, Text.Trim, type text}),
        Trim = Table.TransformColumns(ChangedType, ColumnByOperationTrim),
        ColumnByOperationClean = List.Transform(ColumnNames, each {_, Text.Clean, type text}),
        Clean = Table.TransformColumns(Trim, ColumnByOperationClean)
    in
        Clean
in
    TrimAndClean

Thanks for helping me!

v-jiascu-msft
Microsoft Employee
8 years ago
Hi Anonymous,

To be honest, I didn't find anything wrong with the code. I think here could be the solution. Please give it a try.

Best Regards,

Dale

Forum Discussion

Power BI dataset refresh error - We cannot convert the value "[Function]" to type Function

4 Replies

Related content

An unexpected error occurred( file ", line, function ")

"An unexpected error occurred (file '', line , function '')" error

We cannot convert the value \"[Function]\" to type Function

"The credentials provided for the Sharepoint source are invalid" when using custom functions

How use "like"-Function or "not like" in Dax?

Código de erro subjacente:	-2147467259 Table: Budget.
Mensagem de erro subjacente:	We cannot convert the value "[Function]" to type Function.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.Reason:	Expression.Error
Microsoft.Data.Mashup.ValueError.Value:	[Function]