Categories
Adventures In Power Query Experiment Formatting Uncategorized What Not To Do

What Not To Do: When Letting Your Code Breathe Goes Bad

White Space before a function call is allowed

Whitespace between function calls and the name are allowed. Including newlines
These are equivalent statements:

= DoStuff( args )

// and
= DoStuff

                                ( 
args )

Record lookups also allow whitespace.
This is totally valid syntax wise. Not necessarily morally though.

let
    Func = () => [ 
        user = [ Name = "bob" ] 
    ]
in 
    Func  


(

              )
                            
                                             [    
     user  



]

Misleading Comments inside Lists, Records, and Function Calls are allowed

Comments do not affect parsing or execution.
Without syntax highlighting it looks like 4 is the final item in the list

let
    num = List.Count( { 0
                     ..
       5 } & { 3, 4                                                  /*
    }) /* fake ending here  here * /
                
    without syntax highlighting, it looks like the func call ended
    
    later, secretly do more
*/                                                  ,99 } )

in num

Now it’s slightly easier

let
    num = List.Count( { 0
                     ..
       5 } & { 3, 4                                                  /*
    }) /* fake ending here  here * /
                
    without syntax highlighting, it looks like the func call ended
    
    later, secretly do more
*/                                                  ,99 } )

in num

The list operator .. allows a lot of expressions

You can use inline comments, resuming the list expression later

let
    num = List.Count( { 0
..
           5 } & { 3, 4 /*
    now do more */ } )
in num

You don’t have to wrap the try-catch expressions in parenthesis.

let l = { 
          try "foo" + 99 catch (e) => 3           ..
          try File.Contents( "invalid" ) catch () => 7 } 
in 
    l = { 3..7 } // is true

This version gave an interesting error. I thought perhaps try-catch expressions doesn’t work for inline list indices ?

let g = { 
          try 10 catch (e) => 3           ..
          try 27 / 0 catch () => 53 }

in g
Expression.Error: The number is out of range of a 32 bit integer value.
Details:
    Infinity

But then realized division by 0 in power query does not throw an error record. It has the type number.

Categories
Command Line Experiment Formatting

Experiments of 2022-09

Power BI / Power Query

Things to note

  • options is an record, when used this way it’s similar to Python’s kwargs
  • Merging two records with update the existing fields, adding new fields, if they do not yet exist
  • (in PowerQuery) the order of steps don’t change the final result (order of execution is the same) That’s why defaults after config works
  • Sometimes readability improves when placing large “blocks” like values_list out of order so the logic is on top
  • I replaced values *before* converting them to text so you have more control (before coercion )
Text.JoinSpecialValues_impl = (source as list, optional options as nullable record) as text =>
    let
            config = Record.Combine({defaults, options ?? []}),
            defaults = [
                Separator = "|",
                UseSpecialSymbols = true
            ],
            text_list = List.Transform( values_list, Text.From),
            joined_string =  Text.Combine( text_list, config[Separator] ),
            values_list  = if not config[UseSpecialSymbols] then source else
                List.ReplaceMatchingItems( source,
                    {
                        // replace true null, and true empty strings (vs whitespace)
                        { null, "␀"},
                        { "#(cr,lf)", "#(240d)␤" }, // is #(2424)" }
                        { "#(lf)", "␤" }, // is #(2424)" }
                        {"", "␠"}
                    } ) 

        in
            joined_string,
Power Query and Report.pbix (permalink) at github://ninmonkey

Powershell

Goto Everything

Goto /c/foo/bar

# go back
> Goto -Back
> Goto '-'    # normal cd history works too
> Goto '+'

# Goto the world
$Profile            | goto  # go to string's path
Get-Item $PROFILE   | goto  # cd to the FileItem's path
gcm EditFunc        | goto  # jump to function declaration
gmo NameIt          | Goto  # go to module's folder
[CompletionResult]  | goto  # to docs for
# <https://docs.microsoft.com/en-us/dotnet/api/System.Management.Automation.CompletionResult>

# Open git repos in browser
goto git microsoft/powerquery-parser | goto
goto git@github.com:microsoft/powerquery-parser.git | goto 

# goto the newest log
Get-ChildItem 'c:\root\manyLogs' -Recurse
| Sort LastWriteTime -desc -top 1 | goto

Pwsh Cli

Display all parameters
My favorite hotkey, ctrl+spacebar

s

Breaking Formatting

2, 40, 100, 200, 400, <#700, 2000,#> 20000 | %{ $i=$_;
  0..10 | %{ $j = $_;
    0..3000 | Get-Random -Count 300 | %{ $k = $_
      [pscustomobject]@{ DisplayString = [string]$_ }} | fw -Column $i }
        "Was: $i"
        sleep -sec 0.7 }

Is it a bug, or, is the extra y-axis padding working as intended in a situation where intended is not intended ( ie: column count orders of magnitude larger than the terminal’s column count )

Excel functions: What’s New

https://insider.office.com/en-us/blog/text-and-array-manipulation-functions-in-excel

  • [ ] new array ops, make a query like
  • = a1: e1 , except new functions could dynamically change the selection based on variable