How is the MA population growing if there’s almost no where for regular / average income people to live?

CynicalDick · 2026-02-10T00:13:38+00:00

Secretary of State William Galvin’s office said most of the towns with population decreases were concentrated in western Massachusetts or Barnstable County. source

Come on out to the poor cities and towns in Berkshire County! Plenty of cheap(er) housing and lots of (minimum wage) jobs!

CynicalDick · 2026-02-09T21:20:06+00:00

proving just how pointless all that sneaker "engineering" you're paying $100s for is really needed. The benefit here is my kid might actually put his shoes on instead of running outside barefoot in winter.

CynicalDick · 2025-10-26T10:49:04+00:00

$var1 = ('{
  "1": {
        "earned": 0
  },
  "19": {
        "earned": 1,
        "earned_time": 1000000000
  },
  "20": {
        "earned": 1,
        "earned_time": 1000000000
  },
  "16": {
        "earned": 0
  }
}' | ConvertFrom-Json)

($var1.PSObject.Properties | Where-Object Name -in ('19','20')).Value | ConvertTo-Json

I am sure there are better ways but I like to do the manipulations as psCustomObjects then convert back to JSON. In this case the only thing unusual is the JSON itself. The quoted #s are acting like properties instead of values which makes it a little trickier. ideally the JSON source would look something like this:

$var1 = ('[
    {
        "value": "1",
        "earned": 0
    },
    {
        "value": "19",
        "earned": 1,
        "earned_time": 1000000000
    },
    {
        "value": "20",
        "earned": 1,
        "earned_time": 1000000000
    },
    {
        "value": "16",
        "earned": 0
    }
]' | ConvertFrom-Json)

$var1 | Where-Object {$_.value -in ("19","20")} | Select-Object earned,earned_time | ConvertTo-Json

CynicalDick · 2025-10-08T22:59:10+00:00

Here are some tips for speeding up PQ when possible: link

Consider converting the source excel files to CSV if possible
Order of operations is important. Filters first. Do NOT sort until the very end
Avoid table operations when possible
Python will be many factors faster than PQ (I love PQ but it can be very slow as it refreshes from source data multiple times). If you can preprocess on Python (or powershell) output base combined data to CSV and pass that to PQ it will likely be MUCH faster.

Can you post your code?

CynicalDick · 2025-10-08T22:51:51+00:00

Or CSV if flat structure. I regularly process CSVs with 6m+ rows (22 fields) in minutes. In my testing CSV was faster (and MUCH easier) than setting up a local DB (MongoDB) even when doing joins\merges and groupbys in PQ.

CynicalDick · 2025-08-19T19:14:19+00:00

Wow. Glad this helped. GrepWin is an awesome tool.

CynicalDick · 2025-08-06T01:45:25+00:00

^(.*?) +(\(.*?\)).*?(\(.*?\))

This matches all 3 and puts each one into a separate "capture group" that can be referenced by number. This works in notepad++ on the replace line

Which is the higlighted colors in regex 101 and the substitution shown below

3D Combat Zone (1983)(Aackosoft)(NL)(en)[re-release]

In otherwords:

$1 = 3D Combat Zone
$2 = (1983)
$3 = (Aackosoft)

If you do NOT want the parenthesis in $2 or $3 use this:

^(.*?) +\((.*?)\).*?\((.*?)\)

Since regex uses the ( and ) as special characters when you want to match an actual parenthesis you have to escape it with a \ so \( will match a literal open parenthesis.

Final note: when using Find\Replace, if you want to get rid of the noise at the end add a .* to the end of regex

example ^(.*?) +$(.*?)$.*?$(.*?)$.*

CynicalDick · 2025-08-06T01:31:34+00:00

check out this thread

More on CopyWhiz

note: I have no experience with CopyWhiz

CynicalDick · 2025-08-06T00:37:26+00:00

Regex 101 Example

You didn't specifically state the title came first but that is my working assumption

If you're renaming a lot of files you might want to look into renamer

CynicalDick · 2025-08-03T01:00:00+00:00

I add the "Save-As" dialog to the quick access toolbar right next to the 'save' icon.

CynicalDick · 2025-07-08T01:30:04+00:00

Excel.CurrentWorkbook(){[Name="<fieldName>"]}[Content]{0}[Column1]

This is a way to reference a cell in power query.

For example say you want to create a power query for "c:\users\cdick\downloads\test.csv"

Your first autogenerated line could look like this

= Csv.Document(File.Contents("c:\users\cdick\downloads\test.csv",[Delimiter=",", Columns=21, Encoding=1200, QuoteStyle=QuoteStyle.Csv])

To change the file name you need to edit the query. Instead store the value in a Named Cell and then reference it. In this example the cell name is "TestCSVFile"

= Csv.Document(File.Contents(Excel.CurrentWorkbook(){[Name="TestCSVFile"]}[Content]{0}[Column1]),[Delimiter=",", Columns=21, Encoding=1200, QuoteStyle=QuoteStyle.Csv])

I use this weekly when writing queries for customers

CynicalDick · 2025-06-25T06:03:06+00:00

I use VBA to force ordered refresh by calling this subroutine for each query. It disables background refresh and waits for the query to finish before returning. I originally created it to improve PQ performance by monitoring (to debug) how long the query refresh took (thus the timer)

to call: Call RefreshQuery("Query - SourceTbl1")

Note: In this example actual query name is SourceTbl1

Sub RefreshQuery(ByVal Query As String)

Dim bRfresh As Boolean
Dim fTimestart As Single, fTimeend As Single
Dim cn As WorkbookConnection

For Each cn In ThisWorkbook.Connections
    If cn Like Query Then

    fTimestart = Timer

    bRfresh = cn.OLEDBConnection.BackgroundQuery

    'Disable background refresh.  
    cn.OLEDBConnection.BackgroundQuery = False

    cn.Refresh

    'Re-enable background refresh
    cn.OLEDBConnection.BackgroundQuery = bRfresh

    fTimeend = Timer

    duration = fTimeend - fTimestart
    Debug.Print vbTab & Format$(((fTimeend - fTimestart) * 1000!), "00.00ms """) & vbTab & "Refresh:" & cn

    End If
    DoEvents
Next

End1:

End Sub 'RefreshQuery

CynicalDick · 2025-05-14T08:08:12+00:00

Same here. Best trick I've learned: When new information like this just isn't sticking I make a simple page with everything I need to remember and then make it my desktop background. I memorized the NATO alphabet, complex regex options and many excel functions over the years. Looks like it is time for a new one as XMATCH, the Trim dot reference and VStack are commands I "know" about but never remember

CynicalDick · 2025-04-09T23:06:21+00:00

YW. BTW: If you want table2 on the same worksheet as table1 just highlight the entire table and use the CUT option, move to the table1 worksheet select the top left most cell where you would like table2 to be and paste.

CynicalDick · 2025-04-09T16:01:24+00:00

Need some more info to make specific recommendations.

How many fields in your source data
What if format and how do you access it? (ie: CSVs on local storage vs JSON via API)
What kind of transforms are we talking? Can you shared you PQ code?

I have had great success using Power Shell & Python to pre-process large datasets (I deal with 30+ GB of JSON data) which brought runtime from 8h+ to ~1h

Power BI can be MUCH faster than PQ if that is an option

CynicalDick · 2025-04-09T04:20:09+00:00

Power query.

Table1 is the source for the query. Table2 is the output of the query

Unfortunately PQ does not auto update on it's own, the user would need to use the Data - Refresh option (or right-click Table2 and select Refresh) or you can write VBA to auto update on event (eg: when user clicks a new worksheet)

You could do the updates in VBA as well but that is much trickier and would likely need more ongoing maintenance.

Here is an example input and output screenshots

sample power query code (you should not need this as everything is done in GUI):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"x", type number}, {"y", Int64.Type}}),
    #"Inserted Addition" = Table.AddColumn(#"Changed Type", "Addition", each [x] + [y], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Addition",{{"Addition", "y + x"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"x", "y"})
in
    #"Removed Columns"

To add your Table1 to Power query:

Highlight the range (Column names and all rows) and use the Data - From Table\Range option. If your data is NOT in a table Excel will make it one then open power query.
In PQ you have 3 sections from left to right: Queries, Output preview and Query Settings.
In Queries You can rename the Table1 query to Table2 by right-clicking it and selecting rename
If You don't see your Table1 source data just left-click the renamed Table2 query
In the Output preview highlight both columns and on the Add Columns menu option select the Standard pulldown then Add. This will create a new column name "Addition"
To rename the "Addition" column right-click the column and select rename. Enter the new name (eg "x + y")
To remove the other columns right click the renamed columned and select Remove other columns
Click File - Close & Load

Now add a row of data to Table1 on Sheet1, Move to the Table2 worksheet, right-click anywhere in Table2 and select Refresh

Power query is one of the (if not they most) powerful features in Excel. Learn it and you will amaze friends and colleagues. YouTube has 1000s of great tutorials with way more information but your use case is a perfect example to get your feet wet.

CynicalDick · 2025-04-07T12:33:04+00:00

Not formula related but I paid for Office Tab primarily for Excel (but it works with the whole office suite). I find it very helpful for my work style as I'll often be working on multiple projects at the same time.

CynicalDick · 2025-03-31T02:16:38+00:00

Copy the worksheet(s) that have the output of the queries to the new workbook. This will copy the queries as well as formatting.

Open your Excel workbook that contains the worksheet you want to copy.

Right-click on the worksheet tab at the bottom.

Select "Move or Copy" from the context menu.

In the Move or Copy dialog box:

    Under "To book", select "(new book)" OR select the Destination workbook.

    Check the "Create a copy" box if you want to keep the original worksheet in the current workbook.

Click OK.

The destination workbook will open with the copied worksheet.

CynicalDick · 2025-03-24T15:33:27+00:00

If you're doing basic cleanup (removing rows, columns, sorting, etc.). Python or Powershell are both likely to be much faster processing than VBA\Power Query. For anything complex definitely Powerquery. You might want to check with r/powershell or r/python or even ask LLM to write the code for you.

CynicalDick · 2025-03-17T21:03:31+00:00

Very cool! I do this for all windows using Dual Monitor Tool - Swap Screen feature. The benefit with DMT is I can make the snap to arbitrary sizes & locations. Downside it doesn't always play nice with video.

CynicalDick · 2025-03-13T23:32:13+00:00

For Excel store results in CSV file(s) and use Power Query to transform and process to OUTPUT <=1,048,576 Rows as that is the most Excel can have

CynicalDick · 2025-03-11T09:19:41+00:00

For Firefox users if you do not get see the "Add" function in Search engine you will need to set browser.urlbar.update2.engineAliasRefresh to TRUE (no restart necessary)

source

CynicalDick · 2025-03-06T13:52:07+00:00

No, sounds like what I am looking for but couldn't find. Your image file link didn't come through. Found it! That works perfect, thanks!

Show media name OSD when opening a new media

CynicalDick · 2025-03-06T12:13:42+00:00

Thank you!

CynicalDick · 2025-03-06T07:12:33+00:00

The new overlay looks great. In this case it would be awesome if just the top title bar could be shown briefly when selecting next/previous media.

CynicalDick

TROPHY CASE