Solved: Re: Custom function Expression Error: 1 argument p...

deirdrebclark48 · ‎08-29-2024

I've been staring at this code for 2 days. Receiving the following error: Error: Expression.Error: 1 arguments were passed to a function which expects 2. Arguments = <ccon>{Binary.FromText(...)}</ccon>. RootActivityId = b8c328d4-0a47-452c-a361-1940dee8f7ac.Param1 = Expression.Error: 1 arguments were passed to a function which expects 2. Arguments = <ccon>{Binary.FromText(...)}</ccon> Request ID: b8912e9d-e594-cdda-1245-233c1130b63b.

Here is the code from the Advanced Editor:

let

Source = Excel.Workbook(Parameter, null, true),

#"Filtered rows" = Table.SelectRows(Source, each ([Name] = "WIP REPORT" or [Name]= "REPORT")){0}[Data],

#"Added index" = Table.AddIndexColumn(#"Filtered rows", "Index", 1, 1, Int64.Type),

#"Unpivoted other columns" = Table.UnpivotOtherColumns(#"Added index", {"Index"}, "Attribute", "Value"),

#"Changed column type 1" = Table.TransformColumnTypes(#"Unpivoted other columns", {{"Value", type text}}),

#"Removed errors" = Table.RemoveRowsWithErrors(#"Changed column type 1", {"Value"}),

Nav = Table.SelectRows(#"Removed errors", each Text.Contains([Value], "SOURCE DATA REFRESH") or Text.Contains([Value], "REPORT DATE")),

Custom = Table.SelectColumns(#"Filtered rows", #"Nav"[Attribute]),

#"Custom 1" = Table.Skip(Custom, #"Nav"[Index]{0}),

#"Kept top rows" = Table.FirstN(#"Custom 1", 1),

#"Renamed columns" = Table.RenameColumns(#"Kept top rows", {{#"Nav"[Attribute]{0}, "REPORT DATE"}}),

#"Changed column type" = Table.TransformColumnTypes(#"Renamed columns", {{"REPORT DATE", type datetime}}),

#"Changed column type 2" = Table.TransformColumnTypes(#"Changed column type", {{"REPORT DATE", type date}}),

#"Drill down" = #"Changed column type 2"{0}[REPORT DATE],

#"Custom 2" = Source,

#"Navigation 1" = #"Custom 2"{[Item = "WIP", Kind = "Table"]}[Data],

#"Removed other columns" = Table.SelectColumns(#"Navigation 1", {"ORDER GROUP NUMBER", "PROJECTED CONTRACT TOTAL", "BILLED LAST MONTH", "BILLED THIS MONTH", "BILLED", "BALANCE TO FINISH"}),

#"Changed column type 5" = Table.TransformColumnTypes(#"Removed other columns", {{"PROJECTED CONTRACT TOTAL", type number}, {"BILLED LAST MONTH", type number}, {"BILLED THIS MONTH", type number}, {"BILLED", type number}, {"BALANCE TO FINISH", type number}}),

#"Replaced value 10" = Table.ReplaceValue(#"Changed column type 5", "", null, Replacer.ReplaceValue, {"PROJECTED CONTRACT TOTAL", "BILLED LAST MONTH", "BILLED THIS MONTH", "BILLED", "BALANCE TO FINISH"}),

Split = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Replaced value 10", {{"ORDER GROUP NUMBER", type text}}), {{"ORDER GROUP NUMBER", Splitter.SplitTextByDelimiter(", "), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ORDER GROUP NUMBER"),

#"Merged queries 1" = Table.NestedJoin(Split, {"ORDER GROUP NUMBER"}, Orders, {"Order Number"}, "Orders", JoinKind.LeftOuter),

#"Expanded Orders" = Table.ExpandTableColumn(#"Merged queries 1", "Orders", {"Order Group Primary Number"}, {"Order Group Primary Number"}),

#"Replaced value 1" = Table.ReplaceValue(#"Expanded Orders", each [ORDER GROUP NUMBER], each if [Order Group Primary Number]= null then [ORDER GROUP NUMBER] else [Order Group Primary Number], Replacer.ReplaceText, {"ORDER GROUP NUMBER"}),

#"Grouped rows 1" = Table.Group(#"Replaced value 1", {"ORDER GROUP NUMBER", "Order Group Primary Number"}, {{"PROJECTED CONTRACT TOTAL", each List.Sum([PROJECTED CONTRACT TOTAL]), type any}, {"BILLED LAST MONTH", each List.Sum([BILLED LAST MONTH]), type any}, {"BILLED THIS MONTH", each List.Sum([BILLED THIS MONTH]), type any}, {"BILLED", each List.Sum([BILLED]), type any}, {"BALANCE TO FINISH", each List.Sum([BALANCE TO FINISH]), type any}}),

#"Merged queries 2" = Table.NestedJoin(#"Grouped rows 1", {"ORDER GROUP NUMBER"}, PrimaryLabels, {"Order Group Primary Number"}, "PrimaryLabels", JoinKind.LeftOuter),

#"Expanded PrimaryLabels" = Table.ExpandTableColumn(#"Merged queries 2", "PrimaryLabels", {"Salesperson1", "Project Name", "Client Name"}, {"PROJECT EXECUTIVE", "PROJECT NAME", "CLIENT NAME"}),

#"Added custom 1" = Table.AddColumn(#"Expanded PrimaryLabels", "ACTIVE", each true, type logical),

#"Custom 3" = Orders4Snap,

#"Renamed columns 2" = Table.RenameColumns(#"Custom 3", {{"Order Group Primary Number", "ORDER GROUP NUMBER"}}),

#"Removed other columns 1" = Table.SelectColumns(#"Renamed columns 2", {"ORDER GROUP NUMBER"}),

#"Merged queries" = Table.NestedJoin(#"Removed other columns 1", {"ORDER GROUP NUMBER"}, #"Added custom 1", {"ORDER GROUP NUMBER"}, "Removed other columns 1", JoinKind.LeftOuter),

#"Expanded Removed other columns 1" = Table.ExpandTableColumn(#"Merged queries", "Removed other columns 1", {"PROJECT EXECUTIVE", "CLIENT NAME", "PROJECT NAME", "PROJECTED CONTRACT TOTAL", "BILLED", "BALANCE TO FINISH", "ACTIVE", "BILLED THIS MONTH", "BILLED LAST MONTH"}, {"PROJECT EXECUTIVE", "CLIENT NAME", "PROJECT NAME", "PROJECTED CONTRACT TOTAL", "BILLED", "BALANCE TO FINISH", "ACTIVE", "BILLED THIS MONTH", "BILLED LAST MONTH"}),

#"Replaced value 4" = Table.ReplaceValue(#"Expanded Removed other columns 1", null, false, Replacer.ReplaceValue, {"ACTIVE"}),

#"Added custom" = Table.AddColumn(#"Replaced value 4", "REPORT DATE", each #"Drill down", type date),

#"Replaced value" = Table.ReplaceValue(#"Added custom", each [REPORT DATE], each if [REPORT DATE]> #date(2022,12,31) and [REPORT DATE]< #date(2024,1,12) then Date.AddDays([REPORT DATE], -1) else [REPORT DATE], Replacer.ReplaceValue, {"REPORT DATE"}),

#"Changed column type 3" = Table.TransformColumnTypes(#"Replaced value", {{"REPORT DATE", type date}}),

#"Merged queries 3" = Table.NestedJoin(#"Changed column type 3", {"ORDER GROUP NUMBER"}, BillingGrp2ProjectID, {"Billing Group"}, "BillingGrp2ProjectID", JoinKind.LeftOuter),

Exp = Table.ExpandTableColumn(#"Merged queries 3", "BillingGrp2ProjectID", {"Project ID"}, {"Project ID"}),

#"Replaced value 2" = Table.ReplaceValue(Exp, null, each [ORDER GROUP NUMBER], Replacer.ReplaceValue, {"Project ID"}),

#"Filter🪚" = if Filter = "YES" then Table.SelectRows(#"Replaced value 2", each List.Contains(Table[Value], [ORDER GROUP NUMBER])) else #"Replaced value 2",

Chg = Table.TransformColumnTypes(#"Filter🪚", {{"Project ID", type text}}),

#"Merged queries 7" = Table.NestedJoin(Chg, {"Project ID"}, PrimaryCloseDates, {"Project ID"}, "PrimaryCloseDates", JoinKind.LeftOuter),

#"Expanded PrimaryCloseDates" = Table.ExpandTableColumn(#"Merged queries 7", "PrimaryCloseDates", {"Order Delivery Date"}, {"PRIMARY CLOSE DATE ORIG"}),

#"Added custom 2" = Table.AddColumn(#"Expanded PrimaryCloseDates", "RETRO2MO", each Date.EndOfMonth (Date.AddMonths ( [REPORT DATE], -2)), type date),

#"Custom 7" = Table.AddColumn(#"Added custom 2", "RETRO1MO", each Date.EndOfMonth (Date.AddMonths ( [REPORT DATE], -1)), type date),

#"Added custom 4" = Table.AddColumn(#"Custom 7", "CurrentMo", each Date.IsInCurrentMonth([REPORT DATE]), type logical),

#"Grouped rows 2" = Table.Group(#"Added custom 4", {"Project ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

#"Merged queries 9" = Table.NestedJoin(#"Added custom 4", {"Project ID"}, #"Grouped rows 2", {"Project ID"}, "Grouped rows 2", JoinKind.LeftOuter),

#"Expanded Grouped rows 2" = Table.ExpandTableColumn(#"Merged queries 9", "Grouped rows 2", {"Count"}, {"Count"}),

#"Custom 4" = Table.AddColumn(#"Expanded Grouped rows 2", "RFMS BILLED", each if [CurrentMo] = true then List.Sum( Table.SelectRows(#"OrdersRemoveCol", (x)=> x[Order Group Primary Number]= [ORDER GROUP NUMBER] and x[Order Delivery Date]<= [RETRO2MO])[Total Charge Excluding Sales Tax]) else List.Sum( Table.SelectRows(#"OrdersRemoveCol", (x)=> x[Order Group Primary Number]= [ORDER GROUP NUMBER] and x[Order Delivery Date]<= [RETRO1MO])[Total Charge Excluding Sales Tax]), type number),

#"Custom 8" = Table.AddColumn(#"Custom 4", "RFMS BILLED2", each List.Sum( Table.SelectRows(#"OrdersRemoveCol", (x)=> x[Project ID]= [Project ID] and x[Order Delivery Date]<= [REPORT DATE])[Total Charge Excluding Sales Tax])),

#"Replaced value 9" = Table.ReplaceValue(#"Custom 8", each [RFMS BILLED], each if [RFMS BILLED]= null then [RFMS BILLED2] else [RFMS BILLED], Replacer.ReplaceValue, {"RFMS BILLED"}),

#"Filtered rows 2" = Table.SelectRows(#"Replaced value 9", each ([BILLED LAST MONTH] <> null)),

#"Merged queries 8" = Table.NestedJoin(#"Replaced value 9", {"Project ID"}, #"Filtered rows 2", {"Project ID"}, "Filtered rows 2", JoinKind.LeftOuter),

#"Removed columns 1" = Table.RemoveColumns(#"Merged queries 8", {"BILLED THIS MONTH", "BILLED LAST MONTH"}),

#"Expanded Filtered rows 2" = Table.ExpandTableColumn(#"Removed columns 1", "Filtered rows 2", {"BILLED THIS MONTH", "BILLED LAST MONTH"}, {"BILLED THIS MONTH", "BILLED LAST MONTH"}),

#"Replaced value 8" = Table.ReplaceValue(#"Expanded Filtered rows 2", null, 0, Replacer.ReplaceValue, {"BILLED THIS MONTH", "BILLED LAST MONTH"}),

#"Added custom 3" = Table.AddColumn(#"Replaced value 8", "BILLED2", each if [BILLED]= null then [RFMS BILLED2] else [RFMS BILLED]+[BILLED LAST MONTH]+[BILLED THIS MONTH]),

#"Removed other columns 3" = Table.SelectColumns(#"Added custom 3", {"Project ID", "ACTIVE"}),

#"Removed duplicates 5" = Table.Distinct(#"Removed other columns 3"),

#"Grouped rows 3" = Table.Group(#"Removed duplicates 5", {"Project ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

#"Replaced value 11" = Table.ReplaceValue(#"Grouped rows 3", 1, true, Replacer.ReplaceValue, {"Count"}),

#"Replaced value 12" = Table.ReplaceValue(#"Replaced value 11", 2, false, Replacer.ReplaceValue, {"Count"}),

#"Renamed columns 1" = Table.RenameColumns(#"Replaced value 12", {{"Count", "ActiveMatch"}}),

#"Changed column type 8" = Table.TransformColumnTypes(#"Renamed columns 1", {{"ActiveMatch", type logical}}),

#"Merged queries 11" = Table.NestedJoin(#"Added custom 3", {"Project ID"}, #"Changed column type 8", {"Project ID"}, "Changed column type 8", JoinKind.LeftOuter),

#"Expanded Changed column type 8" = Table.ExpandTableColumn(#"Merged queries 11", "Changed column type 8", {"ActiveMatch"}, {"ActiveMatch"}),

#"Replaced value 13" = Table.ReplaceValue(#"Expanded Changed column type 8", each [BILLED], each if [ACTIVE] = false and [ActiveMatch]= false then [RFMS BILLED] else [BILLED], Replacer.ReplaceValue, {"BILLED"}),

#"Removed other columns 2" = Table.SelectColumns(#"Replaced value 13", {"BILLED", "Project ID"}),

ReplaceNull = Table.ReplaceValue(#"Removed other columns 2", null, "BLANK", Replacer.ReplaceValue, {"BILLED"}),

#"Removed duplicates 3" = Table.Distinct(ReplaceNull),

#"Custom 6" = #"Replaced value 13",

#"Merged queries 6" = Table.NestedJoin(#"Custom 6", {"Project ID"}, #"Removed duplicates 3", {"Project ID"}, "Custom 6", JoinKind.LeftOuter),

ExpCust6 = Table.ExpandTableColumn(#"Merged queries 6", "Custom 6", {"BILLED"}, {"BILLED.1"}),

#"Replaced value 3" = Table.ReplaceValue(ExpCust6, each [BILLED], each if [BILLED.1]= "BLANK" and [BILLED2] <> null then [BILLED2] else [BILLED], Replacer.ReplaceValue, {"BILLED"}),

#"Replaced value 7" = Table.ReplaceValue(#"Replaced value 3", null, 0, Replacer.ReplaceValue, {"BALANCE TO FINISH"}),

#"Changed column type 4" = Table.TransformColumnTypes(#"Replaced value 7", {{"BILLED", type number}}),

#"Custom 9" = #"Removed duplicates 3",

#"Filtered rows 3" = Table.SelectRows(#"Custom 9", each ([BILLED] = "BLANK")),

#"Removed duplicates 4" = Table.Distinct(#"Filtered rows 3"),

#"Merged queries 10" = Table.NestedJoin(#"Changed column type 4", {"Project ID"}, #"Removed duplicates 4", {"Project ID"}, "Removed duplicates 4", JoinKind.LeftOuter),

#"Expanded Removed duplicates 4" = Table.ExpandTableColumn(#"Merged queries 10", "Removed duplicates 4", {"BILLED"}, {"BILLED.2"}),

#"Changed column type 7" = Table.TransformColumnTypes(#"Expanded Removed duplicates 4", {{"BILLED2", type number}}),

#"Replaced value 6" = Table.ReplaceValue(#"Changed column type 7", each [BILLED], each if [BILLED.2]= "BLANK" then [BILLED2] else if [REPORT DATE]> [PRIMARY CLOSE DATE ORIG] then [BILLED2]*[Count] else if [PRIMARY CLOSE DATE ORIG]= null then Number.IntegerDivide([BILLED], [Count]) else [BILLED], Replacer.ReplaceValue, {"BILLED"}),

#"Changed column type 6" = Table.TransformColumnTypes(#"Replaced value 6", {{"BILLED", type number}}),

#"Rounded off" = Table.TransformColumns(#"Changed column type 6", {{"BILLED", each Number.Round(_, 0), type number}}),

#"Removed columns 2" = Table.RemoveColumns(#"Rounded off", {"PROJECT EXECUTIVE", "CLIENT NAME", "PROJECT NAME",

PwerQueryKees · ‎08-30-2024

the error seems to be with Binary.FromText. But that is nowhere in your querey, so there must be more.... I totally agree with @Anonymous though, nobody is going to read through all that M code.

Word of advide: Do yourself a favor and clean up your query first:

remove all columns you don't need in the first step
do change type as soon as you can
split you query in logical blocks. For eaxmple start a new query after a group by referring to the result of the group by.

View solution in original post

Anonymous · ‎09-09-2024

Hi @deirdrebclark48 ,
Have you tried his first suggestion, if it helps you can mark his post as a solution, if not you can provide example data with sensitive information hidden so we can help you faster.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

deirdrebclark48 · ‎09-10-2024

Thanks! I followed his solution. It is working now. Thanks again!

PwerQueryKees · ‎08-30-2024

the error seems to be with Binary.FromText. But that is nowhere in your querey, so there must be more.... I totally agree with @Anonymous though, nobody is going to read through all that M code.

Word of advide: Do yourself a favor and clean up your query first:

remove all columns you don't need in the first step
do change type as soon as you can
split you query in logical blocks. For eaxmple start a new query after a group by referring to the result of the group by.

deirdrebclark48 · ‎09-04-2024

Thank You! I'll work on your recommendations!

dufoq3 · ‎09-05-2024

Hi, I do not recommend 2nd and 3rd advice 😉

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

deirdrebclark48 · ‎09-05-2024

Thank you for that! 😊 I truly appreciate your help! I'll try your query. Thanks again!

dufoq3 · ‎09-05-2024

Which one? I haven't provided any in this thread 😉

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

deirdrebclark48 · ‎09-05-2024

I was under the impression that the link in your Note would help. I haven't clicked on it yet, though.

Anonymous · ‎08-29-2024

Nobody is reading through all that for you. Which step gives you an error?

--Nate

Custom function Expression Error: 1 argument passsed which expects 2 arguments.

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025

Party with Power BI’s own Guy in a Cube

Custom function Expression Error: 1 argument passsed which expects 2 arguments.

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025