Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
Anonymous
Not applicable

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

‎05-16-2018 12:10 PM

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. 

Labels:
Message 1 of 5
9,607 Views
0
1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎05-17-2018 06:02 AM

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

Message 4 of 5
9,833 Views
4 REPLIES 4
v-jiascu-msft
Microsoft Employee
Microsoft Employee

‎05-17-2018 02:12 AM

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.
Message 2 of 5
9,590 Views
0
Anonymous
Not applicable
In response to v-jiascu-msft

‎05-17-2018 04:34 AM

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

Message 3 of 5
9,584 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to Anonymous

‎05-17-2018 06:02 AM

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.
Message 4 of 5
9,834 Views
Anonymous
Not applicable
In response to v-jiascu-msft

‎05-17-2018 06:35 AM

Thank you, it worked!

Message 5 of 5
9,566 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Kudoed Authors
View All