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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:-2147467259
Microsoft.Data.Mashup.ValueError.Reason:Expression.Error
Microsoft.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. 

1 ACCEPTED SOLUTION

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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!

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you, it worked!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors