Sometimes you’ll need to run a command with the same input with different logic. This can be a hassle using a slow command like Get-ADUser or Get-ChildItem on a lot of files like ~ (Home) with -Depth / -Recurse
let
QueryStr_UsingDuplicateKeys = (key as text, values as list) as text =>
// values are the 'value' of 'key'-> 'value' pairs
let
escapedList = List.Transform(
values,
each
key & "=" & Uri.EscapeDataString( Text.From(_) )
),
joinedArgs = Text.Combine(escapedList, "&")
in
joinedArgs
in
QueryStr_UsingDuplicateKeys
Note: BaseUrl is for the static part of the url. Everything else should be in options[RelativePath] or options[Query] See docs: Web.Contents for details.
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