The default UI sets your column to type any.You can use the optional argument of Table.AddColumn to set it to numberOr you can declare your function’s return type
Why doesn’t the original [Num] * 2 work?
Powerquery does not know what type will be returned by your function. That’s because each is by definition a function that returns type any
The only function that Power BI is missing is Facebook.Graph (on my machine)
* This query checks for missing identifiers (which may be functions). ( You can filter by type if you convert #shared to a table instead of calling Record.FieldNames() )
Generating the list using #shared
To get a list of all identifiers (functions, variables, constants) I use the variable named #shared . Create a new blank query, then paste this
let
IdentifierList = List.Sort(Record.FieldNames( #shared ))
in
IdentifierList
I copy using Copy Entire List, then storing the results into a PowerShell variable. I Repeat the same with Power BI.
That’s more than I expected.
To find out exactly which functions are different, use the Power Shell operator -NotIn
# Find functions in PBI but not Excel
$MissingExcel = $PowerBI | ? { $_ -notin $Excel }
# Find functions in Excel but not PBI
$MissingPowerBI = $Excel | ? { $_ -notin $PowerBI }
The list of functions Power BI is Missing
Facebook.Graph
The list of functions Excel is Missing
Note: This is the list for today, on my machine. Run the #shared query to find any changes.
ForEach-Object ( Note: this is a command, not a control loop )
It’s first argument is a ScriptBlock so it appears like a control loop.
Notice return verses break in ForEach-Object . Remember that the { ... stuff ... } in this case is an anonymous function , not a language control statement It is a parameter to the ForEach-Object . When not specified, it’s the -Process Parameter
The Model view allows you to set a global default “Summarize By” type for every column. You still have the ability to override the aggregation type per-visual in Report view