Saint Francois Hiding

A beautiful, cool, sunny day unfurled beneath mostly clear blue skies on this singular day of adventuring about the area surrounding Fredericktown in Missouri. While enjoying an excellent breakfast at local A&M in nearby Junction City, some debate led to choosing the farthest point out as the point of beginning for the day.

A bit more time on the road in the morning gave time to warm up a bit. A cold front and subsequent storms passing through the previous night had left a chill in the air. It faded along the route, though lingered comfortably brisk for the remainder of the day.

The intrusion exposure at Johnson Shut-Ins is as stunning on a red flag day like this one as it appeared in the last visit, under a green flag. None were adventurous enough to clamber out on the rocks and rapids today though. The paved path into the sight, along with the subsequent boardwalk and viewing platform placed this location as one of the easiest tasks, though most were a close second.

Standing guard, my comrade remained at the base of Elephant Rocks, protecting a good book. Summiting the formation found the peak lazily lounging about in the sun and not quite as formidable as witnessed from a distance. Threading the trail in and around the site hosted an array of curiosities, both biological and geological, to include the ruins of an old engine house, built to repair trains locomotives of the Iron Mountain and Southern Railroad more than 100 years ago.

Shared efforts attained the next summit overlook from the top of the state. Turning the corner and following the path a little further, the Mina Sauk Falls Trail tempted and almost nearly seduced this scouting expedition. The promise of a leisurely hour and a half downhill stroll to the falls seemed a good idea on this afternoon, but a more formal investigation is pending. There were other items on the agenda this day.

Random distractions require additional attention, as well. The Arcadia Academy appeared along the path to the next inspection. A long and sullied history emerged from this point of interest. Humble beginnings as the Methodist High School grew into involvement in the Civil War, before then being overrun by Ursulines, until forced to close in 1971. Now they serve ice cream, host a restaurant, event space, a bakery, and a bed and breakfast. Occasionally, they host a murder too.

Likely the most difficult destination of the day uncovered a scene every bit the molten perfection as that over at the Johnson’s place. The bright pink granite stood stark in this particular igneous environment. The smooth features guided waters from the Castor River safely through the intrusion and on to their appointment with the Mississippi River.

Accessing the final distraction of the day required a bit of backtracking through Fredericktown again. Any other destination would have required as much effort though. The Last Supper of the day lie even further north in Farmington.

Castle Rock proved less of a task than the route into the spot. Access was well marked, but the path hosted certain obstacles. Easily overlooking them arrived at a map that distinguished very little about the actual location on the estate. An educated guess down one trail closest to the map led to sight of the formation, standing naked against the blue sky, between trees still lacking appropriate foliage to offer much camouflage.

With the sun nearing the horizon, mid-section pangs demanded attention, left no more space for wandering about. Somewhat dirty and dusty from the trail, the party crashed the 12 West Bar & Grill to dine among some of the finest in Farmington. Known from previous travels, it offered the perfect source of sustenance to close a day of wandering along the road.

Storms in Pursuit

Racing across I-70 towards St. Louis, the thought of exiting, on one of many two-lane routes leading away from that travel chaos, crossed our mind more than once. Storms rode our tailgate throughout the day though, recommending otherwise. There would be time for the two-lane, when it was required.

Tornadic conditions were promised in the region in which my comrade and I planned for first base camp. The first warnings were dispatched just as the dying remnants of Route 66 faded into the rear-view mirror. Blue sky opened in spite of the warnings, with two-lane stretched out in front of our transport, finally offering a chance to pause at random amusements, here and there.

One of the spots enabled an opportunity to have our transport swallowed by the earth. An alignment too close to the edge of the exit portal caused the vehicle to be momentarily captured in the maw of a drainage serpent. Narrowly escaping with our lives, the remainder of the journey remained mostly uneventful.

Old Copper Mines Schoolhouse greeted us in no time, standing lonely under the blue sky that was quickly clouding over. Scant information is known about the place; actually, nothing beyond the name and location. A little further down and around the bend our first base camp offered safety from road hazards and the coming weather. It appeared as though it was still a few hours behind us though.

A desire for sustenance demanded the expedition risk one more crossing into the elements. A nearby, recently rehabilitated train depot enabled the activity to proceed quickly enough for return to base camp prior to clouds of doom unleashing their fury. Warnings continued to flash across the air waves. A bit further down the road, the little town of Wynne in Arkansas began to think about rebuilding. Check-in calls abounded with the news. We assured all that the incident 300 km away from our locale had left us mostly unscathed.

The storm finally rolled in much later than expected. We were beginning to wonder if it would come at all. Ample warning from the wall of rain, heard miles away, racing towards us from across the valley, enabled adequate time for retreat to our shelter. It poured copiously for about an hour, then ceased abruptly, leaving only occasional flashes of lightning and an ominous sky void of any starlight. That is as bad as circumstances got, or we slept through it. The weather stays up entirely too late sometimes.

Sequencing Months and Years in Power BI

Sequencing months within Power BI can be a bit awkward, if you need to constantly shift months to show a rolling 12 or however many, while continually keeping them in the order in which they fall across years.

Solutions of the complex variety abound. Simple answers are rare, likely because most of the folks writing them have been mired in code for entirely too long. That is not to say they are wrong, just that there appears a tendency towards over-programming.

A simple solution only requires a little bit of math to produce results perfectly adapted to ordering months and their associated years in visualizations.

It can be done with or without a calendar dataset; either way, a date field is required.

In Power Query, add a [Year] and [Month] columns, both of the numeric variety.

The next step is to simply add a Custom Column with the following, which is M language, indicating to take the date and convert it to text and concatenate the two.

= Text.Combine({Date.ToText([#”Date”], “yyyy”), “ “, Date.ToText([#”Date”], “MMM”)})

The output in the new column is “2023 Mar” for March 2023.

Lastly, create one more Custom Column with the following code (M again).

=[Year]*100+[Month]

The output in the new column is “202303” for March 2023.

Mission accomplished, almost.

Back in Power BI, sort one column by the other and all months from here into eternity are sorted correctly by year and month.

Compare that with a DAX solution captured from somewhere a few months ago, hailed in some parts of the interweb as the greatest thing since sliced bread. It works just as well, relying on the same columns, but for non-programmer types is not quite as easy to wrap your head around, much less remember it over and over again.

Seq Month =

VAR MaxMos = SUMMARIZE(ALL(‘Calendar’),’A Calendar’[Year],”MaxMonth”,MAX(‘Calendar’[Month]))

VAR MyYear = [Year]

VAR MyStart = SUMX(FILTER(MaxMos,[Year]<MyYear),[MaxMonth])

VAR firstYear = CALCULATE(FIRSTNONBLANK(‘Calendar’[Year],1),ALL(‘Calendar’))

VAR myNum = IF(MyYear=firstYear,[Month],MyStart+[Month])

RETURN myNum

There are likely limitations with the simple approach, though none have been uncovered as of this writing. Power BI can slice and dice in a hundred different ways and seems to care very little whether the code is complex or simple math.

I like simple.

Get the latest file from Power BI

Daily or weekly reports constantly need data appended to a source folder in non-database situations. As the files pile up folder it can be a little difficult to differentiate between them in Power BI, when one really only needs the latest for a particular visualization or report page.

Another story on Medium, linked below, does a great job of detailing how to get only the latest file data. The solution points to creating a separate, identical query on the same pile of data and then, singling out the most recent report in the new query.

A few standard conventions are required to do this. Naming files to include a YYYYMMDD date prefix makes it a little easier to perform the sort needed but inserts an extra “hands on” step in the process. [Date Modified] or [Date Created] from the file attributes usually prove more useful.

While all of this is effective enough, it can make things a little clunky; especially true if seeking to have the queries always mirror one another. There are quite a few elegant solutions to do just that too, if one digs around enough.

There is a simpler solution. It still requires sorting the data in Power Query. It does not require another query.

In Power BI, a slicer leveraging whatever field used to do that sort pulls off the same stunt. Place a “Top N” filter on that slicer to show only the “Top 1” based on whatever seems most appropriate. In my use case, it is a date field and works perfect every time, with every “Scheduled Refresh,” and appears a little more flexible too.

There are a few limitations on editing the text that appears in the slicer. A few modifications may be needed to get the right look. However, it enables the report to host as many visualizations as needed, based on one data file, without an extra query and the need to maintain that query. I suspect it probably performs better too.

. . .

further reading

Connect to datasets in the Power BI service from Power BI Desktop

Microsoft Learn

Getting data from the latest file in a folder using Power Query

Medium

Social Determinants de Coronado

A couple days wandering about downtown San Diego and over to Coronado.

While the Marriott offered an excellent view of the city and the mountains, there was no time to linger about the room. An early afternoon landing in San Diego, after departing Kansas City around lunch time left a somewhat desperate desire for sustenance.   It took very little time to locate Taqueria Los Chuchys on Fourth Avenue and enjoy a little, though somewhat greasy, street taco happiness.  Mostly clear and 10-12°C finished off afternoon into evening strolling about the bayside quadrant.

Little seemed to have changed.  Many of the same places under construction on the prior incursion 10 years ago appeared as though they were still under construction.  The afternoon melted away under a slower pace.  Passing through 5 kilometers of the urban bayside, base camp for the next couple of nights arrived as the sun began to get down on the horizon. 

Going back in time brought about an early than usual morning, offering another chance to drift through the immediate area around the hotel.  A conference held in the same location would be the test of endurance for the day.  

Our crew ended the long afternoon of experts exhibiting statistical growth visuals they seemed to misunderstand, at this infamous location.  The Whiskey House in San Diego stands in the Guiness Book of World Records for hosting the most whiskey in the world; indeed, whiskey from all over the world!  The multi-volume menu required more time than available but sufficed to produce "Midnight Moon" from North Carolina. A bit of charcuterie, followed by a filet of Salmon closed the affair out nicely.

The team split up after that, parting ways for a variety of destinations, primarily those only seen from behind closed eyelids.  A brief wander about later that evening revealed a new site noted earlier in the day. A massive shell in Jacobs Park glowed a beacon at edge of the marina.  A production of any sort at this venue is likely a fun and fantastic experience.

The conference did not run quite as long on the following day, nor was it any more particularly enlightening. Afternoon quickly unfolded across 24 kilometers by ferry and foot.  The latter of which dominated the transportation type much of the length of Coronado Beach. Barefoot operations slowed the pace on this clear and sunny 15°C afternoon.  Colleagues found their own space but departed much earlier.  Slightly inland to the Burger Lounge, which offered a delicious opportunity to reflect on how much time before sunset.  Beach Time enables everything and watching the sun melt into the horizon of the Pacific Ocean, the return journey followed in short order.

Crossing back on the ferry later, after walking the distance back to the landing, found conversation with a local couple out for a walk.  They had both once lived and gone to school in Missouri.  It is a small world, indeed.  Walking continued into the morning, up and along the bayfront, thinking it might be an enjoyable travel this method through to the aerodrome.  Broadway was the furthest attained.  Traveling cargo proved an unplanned detriment to the task and the 992 supported the remainder of the expedition.

The window remained elusive on the flight out, but the lottery turned favorably on this return trip. Earth and sky quickly spread across a horizon from 3 kilometers above and this in-flight film offered up clear skies across the southwestern United States.  Most of Kansas lay covered in snow, with the eastern portion obscured by clouds through to landing.  A few landmarks noted in the previous journey along this route went into hiding, others boasted a new look, and a few new formations displayed their best contours. 

. . .

Further Reading

Marriott Marquis

San Diego, CA

The Whiskey House

San Diego, CA

Coronado

Coronado, CA

The Burger House

Coronado, CA