David's Daily Bread
News and Scripture each morning. Practical tools each evening. Loved by God.
Start here tonight
worth an hour tonight Trending workflowMakeUseOf front page, Sep 13

Point Excel's Power Query at a folder once, and next month's report rebuilds itself on one Refresh All

The monthly report you rebuild by hand is the one Excel was always willing to rebuild for you.

Writing at MakeUseOf on September 12, Adaeze Uche describes three years of rebuilding one monthly report by hand: opening 114 files from 19 locations, copying the data out, fixing the formatting, stripping unwanted rows, and combining the result. Power Query replaces that with a setup you do once. Under the Data tab, Get Data then From Folder points Excel at the storage location, Combine and Transform Data reads every file in it, and from there you drop columns, correct data types, use Unpivot Other Columns to turn a wide layout into database-friendly vertical rows, and click Close and Load. Next month you drop the new files into the same folder and click Refresh All, which the author reports finishing in seconds.

✎
The tool shelf new tonight

Every shelf item is available today; no waitlists, no vaporware.

✎
The workflows follow along at home
Mary of Nazareth

Keep and Ponder

The useful work is finished. Keep what is true, ponder what matters, and entrust the rest to God.

Keep

I will both lay me down in peace, and sleep: for thou, LORD, only makest me dwell in safety.
Psalm 4:8, KJV

Ponder

Name one thing that can remain unfinished until morning.

Entrust

God of peace, hold what I cannot finish, and let me receive this night as a gift. Amen.

A practice shaped by Luke 2:19Guided by the biblical witness of Mary of Nazareth.
Page notes
✎