Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
@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
Thank you, it worked!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.