Data Moshpit

You read that correctly. Data moshpit.

This September the greatest thing in the history of great things happening will happen. On Saturday September 16, in a metal club in Berlin, data platform MVPs Ben Kettner and Frank Geisler are JOINing[1] the two best things in the world: data and heavy metal.

Data Moshpit is a one-day community event with a heavy metal theme. The sessions will be metal. The speakers will be metal. The atmosphere will be metal. There will be beer and food and if my experience at metal shows in Germany have taught me anything, lots of brothers of metal and sisters of steal wearing leather and denim and the coolest jean jacket vests with giant Manowar back patches.

The Data Moshpit call for speakers is open now, and closes on July 7th. You can submit metal-themed sessions of your own, or just check out the exciting sessions already submitted.

If you’ve got a heart of steel and data for blood, this is the one event of 2023 that you cannot afford to miss. And if you’re not into metal[2], you should come anyway. It will be a great opportunity to connect with the community and learn something new. I hope to see you there!

[1] Yes, I made this joke. Don’t be CROSS.

[2] You can still be my friend, don’t worry.

Analyzing Plex Media Server Data using Power BI

Around a year ago I left Spotify and started using my locally hosted Plex Media Server to feed my daily music consumption. Since I spent much of the last 30+ years building an extensive CD and MP3 collection, the transition was surprisingly smooth, and the excellent Plexamp client app feels like an upgrade over Spotify in many ways.

Now it’s time to start digging into the data that my year of music listening has produced.

I blogged last year about how I report on my Spotify listening history. It’s a bit of a hack because Spotify doesn’t have a database or API you can use, so you need to make a GDPR request and wait a few weeks to get a JSON archive… but I made it work. Plex uses a SQLite database to keep track of its data and metadata, and it’s relatively easy to start using it as a data source for your own Power BI reports. Since this is a largely undocumented transactional database[1] I’m still figuring out some important details and locating important data points, but I wanted to share what I’ve learned.

Here’s how to get started.

Installing Plex, setting up libraries, listening

For this post to be useful to you, you need to run a Plex media server and use it to listen to your music collection[2]. Head over to, download the software, install it, set up your libraries, and start listening to the music you love.

This is all well-documented by the fine folks at Plex, so I won’t share any additional details here.

Download a database backup

As mentioned above, Plex uses SQLite as its database, and the Plex web UI provides an option for downloading a copy of the database file. Although it may be possible to report directly on the “live” database, I’m erring on the side of caution and using Power BI to connect to backup files.

Here’s how to download the database:

  1. Open the Plex web UI at and sign in to your server
  2. In the upper right, click on the “wrench” icon to open the settings
  3. In the lower right, select “Troubleshooting” in the “Manage” category of the settings options
  4. On the troubleshooting settings page, click the “Download Database” button

After a few seconds your browser will download a zip file containing a SQLite database file with a long system-generated file name.

Copy this file to folder and give it a more useful name for my project the database file is \Plex DB\plex.db.

SQLite driver and ODBC DSN

Power BI doesn’t have a native SQLite connector, but SQLite does have ODBC drivers. It looks like there are multiple options – I used this one mainly because it was the first one I found.

Once you’ve downloaded and installed the ODBC driver, create a DSN that points to the database file you downloaded earlier. The connection string is just the file path.

With the DSN created, you can start working in Power BI Desktop. Create a new PBIX, select ODBC from the Get Data dialog, and select the DSN.

Click OK and you’re ready to go.

Key Plex database objects

As mentioned earlier, the Plex database is largely undocumented. It has a somewhat normalized schema, which is slowing my exploration and discovery. When a table is mostly integer IDs and obscurely-named values, exploration can feel more like archaeology.

With that said, there are two tables that are probably going to be most useful: metadata_items and metadata_item_views.

The metadata_items table contains one row for each item (track, album, artist, episode, season, show, etc.) in your Plex media library. This table implements an implied three-tier hierarchy by including an id column and a parent_id column that define a relationship[3] between metadata items and the items that contain them.

The metadata_items table also includes vital data such as the title (track name, album name, artist name, etc.) of each item as well as index (track number on the album, episode number in the season, etc.) and such. Not all fields are used for all metadata item types, so you’ll see a lot more nulls than you might like.

The metadata_item_views table contains one row for each time a user has watched a video, listened to a track, and so on. This table includes the titles for the viewed item, as well as the parent and grandparent items, so you can easily get the track, album, and artist without needing to join other tables. You can join with  the metadata_items table if you need additional details like the album release year or the record label.

The metadata_item_views table includes a viewed_at column to track when a given item was viewed. This column is stored as a Unix timestamp, so you’ll need to convert it to datetime before you can start working with it. Plex tracks times using UTC, so if you want to work with data in a different time zone you’ll need to handle that in your PBIX.

Refreshing the data

Since your PBIX is connected to a database backup, it won’t automatically show your current library contents and listening history. If you want more current data, just download a new database backup and replace your plex.db (or whatever you named it) file with the new one. The next time you refresh your report, you’ll have new data.

I don’t have a use case for “real time” Plex reporting, so this approach works for me. It’s certainly easier and more timely than the hoops I had to jump through to “refresh” my Spotify data.

Next steps

I’m still just starting to get my hands dirty with this fun data source, so my next steps are mainly to keep exploring. Using my Spotify listening report as a target, I’m planning to duplicate that report and its insights with Plex data. Ideally I’ll have one report where I can see my legacy listening history in Spotify and my ongoing listening history in Plex… but that might be a few months away.

If you’re using Plex and have been thinking about exploring your data – or if you’re already doing this – I’d love to hear from you in the comments about your experiences.


[1] To the best of my knowledge after a year or more of occasionally googling to answer my questions, there is no official documentation for the database. The Plex community support forums have a few bits and pieces here and there, but most of what I’ve included in this post is the result of my own exploration. Honestly, it’s a real joy to have a side project with this type of work involved.

[2] Plex also has great features for managing and streaming TV shows, movies, and photos – it’s not just about music. This blog post is going to focus on the music side of things because that’s what’s most interesting to me today, but there’s a lot more to love in Plex.

[3] It’s worth noting that there are no “real” FK > PK relationships in the database, or if they are they’re not exposed through the client tools I’ve used. To figure out where the data relationships exist you need to explore the data.

Webcast: Unleashing Your Personal Superpower

Last week I delivered a presentation for the Data Platform Women In Tech‘s Mental Health and Wellness Day event.

The recording for my “Unleashing Your Personal Superpower” session is now online:

I hope you’ll watch the recording[1], but here’s a summary just in case:

  • Growth often results from challenge
  • Mental health issues like anxiety and depression present real challenges that can produce “superpowers” – skills that most people don’t have, and which can grow from the day-to-day experience of living with constant challenge
  • Recognizing and using these “superpowers” isn’t always easy – you need to be honest with yourself and the people around you, which in turn depends on being in a place of trust and safety to do so

In the presentation I mainly use an X-Men metaphor, and suggest that my personal superpowers are:

  1. Fear: Most social interactions[2] are deeply stressful for me, so to compensate I over-prepare and take effective notes for things I need to remember or actions I need to take
  2. Confusion: I don’t really understand how other people’s brains work, or the relationship between my actions and their reactions – to compensate I have developed techniques for effective written and verbal communication to eliminate ambiguity and drive clarity
  3. Chaos: My mind is made of chaos[3], which causes all sorts of challenges – to compensate I have developed a “process reflex” to understand complex problems and implement processes to address or mitigate them

I wrap up the session with a quick mention of the little-known years before Superman joined the Justice League, which he spent as a Kryptonite delivery guy, and absolutely hated his life. Once he found a team where he could use his strengths and not need to always fight to overcome his weaknesses, he was much happier and effective.

In related news, if I could only get these Swedes to return my calls, I’m thinking of forming a new superhero team…

[1] And the rest of the session recordings, because it was a great event.

[2] Think “work meetings” for starters and “work social events” for an absolute horror show.

[3] I have a draft blog post from two years ago that tries to express this, but I doubt I will ever actually finish and publish it…

Manowar – Munich, 2007

tl;dr: this post has nothing to do with data or business intelligence, and there’s no reason to read it unless you want to learn how 2007-era Past Matthew stumbled his way through Bavaria. But “heavy metal” is listed in the banner of the blog, so I’d say this fits. You’ve been warned.

Over the past 30 years I’ve seen the heavy metal band Manowar almost 75 times, across the US and Europe, in Japan, in Israel, and above the arctic circle in Svalbard. Manowar are not the most prolific musicians, but their music is inspiring and motivating, and they put on a live concert like no one else.

My first international show was in 2005 – that was also the first time I’d ever visited a non-English-speaking country, and only the second time I’d ever left the US. That 2005 festival was a life-changing experience – it opened my eyes to experiences and possibilities that I had never previously imagined, and in 2007 I was ready to do it again.

When Manowar announced their 2007 spring tour, I bought tickets and made plans to travel with my wife and two friends to see the show in Munich. It was going to be our first vacation after we had kids, and it would be the first time seeing Manowar in concert for any of them. Family health problems caused those plans to fall apart, but at the last minute – less than a week before the show – I realized that I had enough frequent flyer miles to make the trip for free. I also realized that if I flew in the day of the show and flew back the next morning I would only need to miss two days of work. (This sounded like a good idea at the time.)

The rest, as they say, is history. In any event, the rest of the post was written in March 2007, so… ancient history.

Before the concert

I can’t believe I’ve made it this far. As I type I’m sitting in a Boeing 767 on the tarmac in Atlanta, waiting for takeoff for the transatlantic flight to Munich, Germany. The show is tomorrow; I’ll be on the ground in Germany for just over 24 hours before flying back home.

This was supposed to be a different trip. I was supposed to have a nice, relaxing week with my wife and two friends. Fly into London, spend a few days there. Train to Dover. Ferry to Calais. Rent a car in Calais, and take a few days to drive across France and Germany, arriving in Munich on the day of the show. Sounds like a good idea, no? Of course, it was not meant to be. The best laid plans of mice and Manowar fans, I suppose. Plans change, and here I am, flying alone into the face of a whirlwind one-day invasion of Germany, hoping I’ll be able to sleep on the flight.

The day itself started off well. I was chatting with the woman at the airline counter (they’re always extra nice to us frequent flyers) and she asked me what I was doing in Munich on such a short trip. I told her I was going to see Manowar in concert, “They’re a heavy metal band,” I said. She broke into a huge smile – apparently Joey is her buddy. He’s always very nice when he flies (Manowar and I happen to share the same airline and airport, although I’ve never run into them on the road) and she helped the band when they were flying out to start the tour early last week. Sadly I could not turn my Manowar fandom into a free first class upgrade – when you’re flying for free in the first place, they tend to frown on free upgrades – but that was OK. I was going to see Manowar – what more could I need?

Things went downhill from there… My flight from New York was delayed half an hour. I had a 90-minute connection in Atlanta, so it would be tight, but I should make it. The half hour delay turned into 45 minutes, then an hour. No!!! Well, obviously I made it, or I would not be bothering to write up a tour report, right? We made up some time in the air, and although my flight to Munich was boarding by the time I made it to the gate, I did make the flight. The plane is pretty full, and no one is sitting next to me yet – do I have a chance at enough room to almost be comfortable?

Yes!! We’re in the air, flying north along the eastern seaboard, and I have room to spare. It’s not first class – as the flatulent young German woman sitting in front of me keeps reminding me as she tries to make re seat recline even further – but it will do. There are worse ways to cross the Atlantic, I’m sure.

Well, that wasn’t too bad. Waking up over The Netherlands, flying into morning, around an hour out of Munich has a certain appeal. The clouds are laid out below the plane like a giant layer of cotton, and the sun is rising with exaggerated speed ahead of us. And they’re bringing food and coffee. Well, something that they call coffee. It’s not great, but it will have to do until we get on the ground. I hope there’s decent coffee in the Munich airport…

…but if there is, I can’t locate it.

And pissing me off even more is my mobile phone. It’s a quad-band phone that should work everywhere in the world, and I went out of my way to make sure that it would work in Germany. Weeks before the trip I talked to my service provider: “Oh, yes sir, you won’t have any problems. You’ll have great service in Germany.” Right. Replace the word “problems” with “coverage” and the word “service” with “headaches” and you’ll be much closer to the truth. I’d made plans with people from six different countries to stay in touch via text messages while I was on the ground. Now it looks like I’ll just have to wing it, in a city I’ve never visited, in a country where I don’t speak the language, trying to meet with people who can’t get in touch with me, and most of whom I’ve met only once before, at the Earthshaker fest in 2005. Oh boy!

At the airport train station I met up with Alex, a German Manowar fan I met back in 2005. He was travelling to Munich for the show as well, with two other German friends. Alex had also caught the show in Berlin the night before, and was planning on catching two more shows before the German tour was over. I’m a little bit jealous, but I doubt I’ll be moving to Germany any time soon, so I should not complain. The four of us rode the train together from the airport to the central Munich train station, and had breakfast and coffee together in downtown Munich. We told stories about past Manowar shows and the trials and travails involved in getting to them sometimes, and paid way too much for coffee.

After breakfast, Alex and his friends went on to the show venue, and I went out into Munich to kill a few hours. My Dutch friend Joyce was arriving on a 2:00 train, and she and I were planning to get our tickets and head over to the hall together. Munich was beautiful, but the main square was pretty much overrun with tourists, which was a bit annoying. Still, I didn’t have any trouble keeping busy until 2:00. I found still water (hurrah!!) and some decent food, and watched people.

One thing that really surprised me was how many people were smoking, both indoors and out. I’d forgotten that Germany was so permissive about indoor smoking – it’s been so many years since smoking indoors has been legal in the States that I just never think about it. But here, in the airports, train stations, stores and restaurants, everyone seems to be puffing on a cigarette. The upside to the wait, however, is that I found still water. I picked up two 1.5 liter bottles at a grocery store near the train station and have them ready for the show. A small victory, but a victory nonetheless.

But where the fuck is Joyce? 2:00 has come and gone. I’ve been waiting in the train station since 11:00 or so. For the last hour I’ve been standing where all of the escalators come up from the lower levels where the trains arrive. And there is no sign of her. Since she is going to be picking up the tickets at the box office, she and I need to get together at some point or this whole trip has been for naught. And where the fuck is David, for that matter? He was supposed to arrive on a 3:00 train, and that time has also passed. Of course, I’ve never met David in person, so it may well be that we passed without even knowing. I should just send them text messages to find out where they are, right? Right.

So by a little after 3:00 it’s become apparent that there’s nothing to gain by waiting in the train station, other than sore feet. I take another train to the Olympiapark. The show is going to take place is at the Olympiahalle, a big concert and sporting venue in the Olympiapark, and my hotel is nearby. Although I won’t be sleeping there, it seemed like money well spent to have a secure place to leave my things and maybe take a shower after the show. I would feel very bad for anyone having to sit down for ten hours next to someone who had spent four hours or more in the crowd at a Manowar show. So I stop off at the hotel, check in, drop off my bag, pack a small sack with my water and some snacks, and take a taxi back to the Olympiahalle.

When I arrive at the hall, it’s still over two hours before the doors are scheduled to open, and there are only a few dozen people there. The first group I meet includes my friend Alex. He and the friends with whom I had coffee this morning were standing at the head of a line with a bunch of their other German Manowar friends. It’s amazing how many faces were familiar from Earthshaker, and how many people remembered me as well. Everyone seemed amazed that I would travel from New York to see Manowar play in Germany, and not once, but twice. I’ll bet that there are people in Germany who paid more (in time, money and pain – however you want to look at it) to be there than I did, but I guess the miles you travel adds to momentary celebrity. In any event, it’s always nice to have people be glad to see you.

On the other side of the hall, waiting in another line (yes, to hear the battle cry) was a group of Italian Manowar friends, including my friend Nicola. This guy is amazing. He takes tons of photographs under the name “Thundersteel” and has a great attitude about everything. To give you an idea of how great this guy is, when Gods of War was released, he bought me a copy of the special edition CD and mailed it to me – from Italy. All this because he knew that it would not be available for sale in the US until early April. What an amazing guy. He introduced me to a bunch of his friends as well – again, many of them were familiar from Earthshaker, but there were a few “Manowar virgins” as well, although I’ve met a few of them online over the years. Everyone was excited about the impending show.

In addition to being excited, I was also getting a bit worried. I had friends to meet, tickets to pick up and no success to date in doing either. But there was one place remaining where I had not yet looked: The Manowar show truck. It was parked on the far side of the hall, so I made my way over there. There were a bunch of fans hanging out near it, but basically it was just a big tractor trailer truck painted with the King of Kings artwork. Very cool, but also very closed. There were some familiar people unloading boxes of t-shirts and other merchandise from the truck, but it wasn’t being used for anything other than transportation and storage that I could tell. And of course, there was no Joyce and no David. And the clock was ticking…

Having seen all that there was to see at the Manowar truck, I walked back around to the front of the Olympiahalle to hang out with Alex and his group of friends. But no sooner had I arrived than whom did I see walking toward the hall? Joyce, her friend Hans and our Norwegian friend Erman who was such a great guy at Earthshaker. He wasn’t wearing his signature wool, leather and metal outfit, but he was still ready to kick ass. After we said hi and exchanged stories (Joyce and Hans had apparently also waited in the train station until 3:00, looking for me – what was up with that?) we all headed over to the Manowar truck together. By the time we were done looking at the truck once more, the beer garden and food stand on that side of the hall had opened, and everyone (well, everyone but me – I hate beer even when I’m in Bavaria) had to settle down for a beer or three. And who did we find waiting near the beer? Our English friend David, who came up and greeted us. Finally! Everyone who needed to be together WAS together and everyone had his own ticket. What a relief. Although I did not have any beer, I did have a giant pretzel – thanks, Hans! – and couldn’t finish the whole thing. It was bigger than my head.

After shooting the shit for a while – talking about Manowar and concerts and kids and more – we all headed together back to the front of the hall. We got in line behind Alex and his group, and none to soon. Shortly after we got situated, large groups of fans began arriving. At this point it was around an hour before the doors were scheduled to open, and people were arriving in serious numbers. And among those numbers was my Austrian friend Mark. Mark and I had talked earlier about the possibility of his making it to the show in Munich, but I’d thought that he was not going to be able to make it. I was delighted to see him – and rightfully so, as later events were to show.

As we waited, the sun was setting. And with the setting of the sun, the temperature started to drop. It was snowing when I landed in Munich in the early morning, and it has been chilly all day. As the sun went down, it was getting damned cold. I’d left my jacket and over-shirt at the hotel, knowing how hot it was going to be once the show started. At least the cold weather would keep my hard-earned water nice and cool. I had only drunk a little from one of the bottles. I knew how much I would need water once I got down into the crowd – dehydration was what undid me at Earthshaker, and I was determined not to make the same mistakes again. I had water, I had snacks, I was all set.

Or so I’d thought. When the doors opened (nearly half an hour late) the first thing the security people did was confiscate my water. Bastards! I suppose it makes sense, but if you’re not going to sell it in the hall (and no, of course there was no still water for sale anywhere in the hall) then why keep people from bringing in their own? God damn it.

And this disappointment was quickly followed by another. The security goons were not letting anyone into any areas other than the ones printed on their tickets. And although I had a good seat (and for the price I paid for the damned ticket, it had better be good) it was still only that: a seat. I had no desire to sit down and watch a Manowar concert from the comfort of a chair. But what to do?

I decided to go spend some money. I went up to the top floor of the Olympiahalle and found the merchandise stand. I’m not normally one to buy crap at concerts – especially Manowar concerts, where the t-shirts tend to have naked women and guitar-wielding demons on them – but this time I made an exception. I got a few “Sign of the Hammer” shirts, some patches, some Manowar ear plugs (how could I resist?) and two toy Manowar show trucks for the kids. What fun!

I also remembered that Manowar were giving away “International Road Warrior” t-shirts to fans who came to a show from a different country. These were being distributed v e r y s l o w l y at another table separate from the main merchandise stand. I went over there and resigned myself to waiting in line for a while. (Not that I had to worry about anyone stealing my assigned seat, right?) The system that the one person at the desk was using to distribute these free shirts went a little bit like this.

  • Equipment: One black sharpie marker, one ball point pen, one stack of white paper.
  • Organization: One piece of paper per country. The name of the country is written at the top of the paper, in bold black sharpie. The names of fans from said country are written below, in pen.
  • Process: When a fan arrives and shows his passport, the t-shirt guy locates the appropriate page, or allocates a new one if no fan from that country has yet gotten a shirt. If there are existing fans from that country listed, he cross-references the current fan’s ID to ensure that he has not already gotten a shirt. If the fan is not a return customer, then the t-shirt guy writes down his name and ID number, asks what size t-shirt is desired, and hands over the shirt. Next customer, please!

As you can probably imagine, this was taking FOREVER!! I was fortunate in that I arrived relatively early, but it still took me 10 or 15 minutes in line. By the time I had my shirt (which is wonderful, by the way!) the line was five times as large as when I’d arrived. The database and software architectural consultant in me was screaming “there’s a better way to do this” and thinking of simple applications that would dramatically speed things up at future shows. I’m a geek, I know…

But waiting in line was interesting too. There were lots of people from Austria and Italy, but also people from Slovenia, Great Britain and right in front of me was a guy from Australia! People thought I’d come a long way to see Manowar play, but this Aussie had come at least twice as far as I. And as I was walking away with my t-shirt, I saw my Austrian friend Mark. If there was a Nobel prize for Metal, this guy would win it. Why, you ask? Because he said to me “Wait here – I think I have a way to get you down to the show floor.” Even now, 4,300 miles and 20 hours later, I get chills as I type this. Although I’m sure that I would have enjoyed the show from a distance, I was bitterly disappointed that I could not get up front for the show. And here was my redemption, my salvation.

And five minutes later, there I was, four or five rows back from the front, watching HolyHell start the show. I was the happiest man in Germany that night.

HolyHell put on a great show. They played everything from their new single, plus a song called “Eclipse” and a cover of Yngwie Malmsteen’s “Rising Force.” It was the same basic set they’d played back in 2005 – with the exception of one or two new original songs and the omission of a Rhapsody cover – but they were in much better form. Maria sounded (and looked) great, Rhino pounded his drums, Tom and Joe shredded the hell out of their guitars, Jay mastered his bass and Francisco was a wizard with his keyboards. My one complaint about HolyHell’s act back in 2005 was that they didn’t seem to be a single unit yet. Everyone did their own thing well, but they didn’t have the chemistry that you want to see on stage. They got better each time I saw them in 2005, but even at Earthshaker they weren’t quite there yet. But tonight they were on. Not only did each individual musician kick ass, the band as a whole really seemed to be a single cohesive unit, and that made a big difference. I sang along to every song, and at the end of the night, back at my hotel, I found that it was HolyHell going through my head, not Manowar. Interesting…

And of course, Eric’s duet with Maria on “Phantom of the Opera” was the highlight of the show. I don’t think anyone was surprised like they were back in 2005, but it worked wonderfully nonetheless. Their voices were so clear and so strong and it was so obvious that they were having a great time on stage together. If you’ve ever seen Manowar live you know the look that Eric gets on his face when he’s having a lot of fun, and it was there during this entire song. I swear, Eric should record a side project of Andrew Lloyd Weber cover songs. Weber wrote so many great musicals, and many of his songs are written to really push the singer’s capabilities, to light up only when being sung by the best. And who could be better than Eric Adams? Following Eric’s duet with Sarah Brightman (Andrew Lloyd Weber’s ex-wife, and the female singer for whom “Phantom of the Opera” was originally written) would it really be that big of a stretch? I’d buy it!

Sadly, the HolyHell set was quite short. I realize that they’re the first act of three, and don’t even have a full album out yet, but I’d much rather watch (and listen to) them than Rhapsody. Of Fire. Oh boy.

I suppose that Rhapsody put on a good show, but I just could not get into it. I own most of their albums, and really like a few of their songs. I even think that they’re much better live than in the studio. But I just could not get into their show in Munich. It was pretty much the same show they’d done in 2005, and to me all of the songs sounded basically the same to me. I mainly used their set as an opportunity to rest for Manowar, conserve my strength, and gradually move closer and closer to the stage. By the end of Rhapsody’s act there were only two people between me and the security barrier.

And my Italian brother Nicola was beside me! He and a few friends had gotten down on the floor through some subterfuge (probably similar to my own) and were ready to experience Manowar up close and personal.

The intermission between Rhapsody and Manowar was incredibly short, by Manowar standards anyway. They unveiled more amps, Scott’s Drums of Doom, a giant video screen behind the stage and a giant Viking longboat below the video screen. The longboat is obviously a set piece and not a real boat, but it was pretty well done regardless. The press of the crowd became intense as everyone tried to get closer to the stage.

And then the lights went down. And the music swelled. And the announcement came, as Orson Welles’s voice filled the hall. “Ladies and gentlemen, from the United states of America – All hail Manowar!”

And all hell broke loose.

Which, of course, was very much to be expected.

The setlist for the show was pretty much the same as the Pardubice show earlier in the tour. I’m really grateful to Manowar for posting the a photo of the setlist on their blog, because I would never have remembered what songs were played or in what order. Instead of listing each song, I’ll instead focus on my impressions from the pit.

Overall: The show was amazing. Although there were a few places in a few songs where Joey’s bass seemed distorted or obscured, it only happened two or three times, and never lasted for more than a second or two. The sound was just great. The band seemed motivated, inspired, energized and simply delighted to be on stage in Munich. In short, they were simply ON for the show.

The Setlist: It was the best of times; It was the worst of times. Or something like that. Everything on the setlist was great. There were some songs that I really would have liked to hear (“Hail and Kill” and “Gates of Valhalla” leap to mind) but by the end of the show I was practically begging for it to end. I don’t know if I could have taken another song or two without dying or getting carried away.

Volume: For the “main” set, the volume was perfect. It was loud enough to feel every pound of the drums both through the air and through the floor, but not loud enough to hurt. When the crowd was really into a song you could hear them clearly around you, but you could always hear Eric singing as well. It was a great balance and whoever was responsible for it (Jeff Hair?) should be commended. When the “Gods of War” setlist began, however, it seemed like the volume jumped a good 5 decibels. It went from being just right to my being “too old,” because for me it was just too loud. I had earplugs in my pocket – I didn’t need them for the main set – but there was no way for me to get my hands into the pocket to get them out, so I just had to suffer. My ears stopped ringing by the time I got to the airport, so it wasn’t as bad as I’d feared, but I don’t think I’ll be cranking up any music for weeks to come.

The Crowd: This was the first time I attended a regular concert in Germany, so I don’t know if the crowd was typical or not, but the people around me up front were totally and overwhelmingly into the show. They sang along to the standard songs that Manowar plays on every tour. They sang along to the classic songs that Manowar has not played live in many years. They sang along to the brand new songs that have never been played live before this tour. Fists truly filled the air. It was hot, sweaty, smelly, smoky and packed as tight as I’ve seen any pit since Manowar played B.B. King’s in NYC in 2005. There was a lot of attrition – I saw at least 10 people get helped/pulled out of the crowd by security, one of whom was carried off in a stretcher. There were just so many people packed into too little a space, and people were getting crushed. There was also fierce competition for space up front. I reached the security barricade during “Each Dawn I Die” and managed to stay there for the rest of the show, but it was a constant battle to stay there.

Highlights: Seeing “Holy War” performed live was worth the trip to Germany in and of itself. I’ve never seen it live before, although (as I’ve mentioned in several forums online over the years) I spent the first half of my first ever Manowar show (at the start of the Triumph of Steel tour) screaming “Holy War!” at the top of my lungs until Eric actually paused and told me they weren’t going to play it that night. And the most amazing thing was that when Joey started playing it, I didn’t  know what song it was. The bass-heavy opening sounded so different from the album version – lacking as it was the segue from “Drums of Doom” – and I turned to Nicola beside me to ask what it was before it struck me. Even though I knew they were going to play it, it still took me completely by surprise. And it was so far beyond my hopes and expectations it’s hard for me to believe how great it was. The song performed live had such a different sound and feel to it from the studio version, but it was still completely true to the heart of the original. It just had more power, more strength, and in essence more holy war.

The other older songs were great too. I’ve never been much of a fan of “Gloves of Metal,” but the crowd loved it, and it was hard to not enjoy the journey back in time. The other older songs, especially “Mountains,” “The Oath” and “Secrets of Steel” were much closer to my heart. I’d never dreamed of hearing “Mountains” live, but it had all of the majesty, clarity, serenity (if I can use this adjective to describe a Manowar song) and power of the original, and then some.

The Vikings: With the thunderous conclusion of “Black Wind, Fire and Steel” the lights came back up, and the stage crew got to work getting ready for the second set, uncovering pyrotechnics and flame pots on the stage. They also hung shields on the side of the longboat, and the shields were decorated with the four band members’ symbols. I thought that was a nice touch. After just a few minutes (the merest blink of the eye if you’re accustomed to waiting for hours for Manowar stages to be set up) the lights went down again and the tape began to roll.

That’s right – playback. The second set was the final “real” tracks from the “Gods of War” CD – everything from “The Blood of Odin” through “Hymn of the Immortal Warriors.” For “The Blood of Odin” they played back the studio narration, instead of having Joey recite it live on stage. They didn’t “pull a Rhapsody” however – instead of simply rolling the tape while lights flash and smoke machines pump out smoke, Manowar brought out the Jomsvikings warriors both in video and in person. They had filmed short video segments to go along with “The Blood of Odin” and “Glory, Majesty, Unity” and more, acting out Joey’s interpretations of the songs. The films were very well done, so I suspect Neil Johnson was involved in their production. For some parts of some songs as well, the Vikings were also live on stage – either up above near the longboat or down below fighting next to the band members – with sword and shield in hand.

Now let me add a disclaimer: I fenced competitively when I was in college, and fought some of the best fencers in the world. (Yes, they kicked my ass, but that’s neither here nor there.) I’ve also done enough other forms of sword fighting, both for theater and for competition, that I have a very critical eye when I see things like this performance. I didn’t really care for their stage show during the Manowar Fanconvention in Geiselwind 2005, but they did a decent job on stage in Munich. It looked like a lot of work and planning went into their performance, and unlike in Geiselwind the “fighting” was only a small portion of what was going on. So the Vikings, to me, were good but not great, although they were probably the least of the available evils.

Why do I say this? First and foremost, I was delighted by the inclusion of a significant “Gods of War” set at the concert. I love the album, and the parts of it that they played are some of the best parts. And since it is a narrative slice of a concept album, it really needs to be played end to end. (Can you imagine Queensryche not playing “Suite Sister Mary” during Operation: Livecrime because it was too long?) And that means including the narrative interludes. And I can’t think of any way to perform songs like “The Blood of Odin” and “Glory, Majesty, Unity” live that would be better than what they did. The video told/enhanced the story, the Vikings made it real, and the band played along whenever it made sense.

To be completely honest, if I were given the choice between a “classic” Manowar live set and a “Gods of War” theatrical set, I’d choose the former. But Manowar didn’t make me choose – they gave us the best of both worlds, and it totally worked.

The show ended with an amazing rendition of “Hymn of the Immortal Warriors” that was a perfect ending to the night. I can’t describe the emotions I felt as the song (and the show) ended. Not only was it a perfectly delivered version of one of my favorite new songs, but it also marked the end of the battle I’d been fighting for the last three hours or more – the battle to reach the front row and to stay there. It seemed to me that Eric met my eye time and time again during this song, as I screamed along with him to its very end. But, of course, I was awfully punchy by that point.

Then the lights came up, “The Crown and the Ring” started playing, and the show was nothing more than a sweet memory. As I stood there, completely drained and exhausted from my ordeal, I noticed one of the cameramen (the small guy with the shaved head – I always call him “Handycam” but I really should learn his real name) gesturing wildly to one of the photographers to move out of his way – apparently Nicola and I made a good shot, because he kept the camera on us for the rest of the song. If they end up making a DVD of this show, I should be all over it. I saw the cameras on me time and again. I guess that’s just one more example of why being in the front row is so great: to the victor go the spoils.

After the concert

And then it was time for me to go. Although there was some water shared with the crowd during the show (a drop here and there, not nearly enough) I was seriously dehydrated and needed fluids immediately. I said goodbye to my Italian friends, stumbled up the stairs and staggered to the nearest refreshment stand. “Do you have any still water?” “Uh… No.” I desperately look around for something neither alcoholic nor carbonated. “I’d like an orange juice please.” The guy pours me a pint glass of orange juice. By the time he’s turned to the cash register, I’ve drained the glass. “Could you make that two, please?” He pours me another. It, too, disappears. Sadly, he only had enough juice on hand for three glasses. But it was enough to keep me from dying, and that is probably what matters.

At this point I had no idea what time it was. My only timekeeping devices were my phone and my iPod, and both were back at my hotel. And that is exactly where I needed to go. There was a change of clean clothes waiting for me there, and even more importantly, there was a giant bathtub just waiting for me to fill it with scalding water and soak my bruised, battered and sore legs before heading back to the airport. My hotel was right around the corner from the Olympiahalle, right next to the train station, so how hard could it be to find it, right?


I was about to find out. After the sweltering heat of the crowd, the outdoor air was bitterly cold. It was probably around 35 degrees Fahrenheit, give or take. It wasn’t snowing yet, but it wasn’t too far from it. I left the Olympiahalle through a main entrance (which I did not realize at the time was not the same main entrance as the one I used to enter) and noticed a few things in rapid succession. First, there was a large crowd of people all walking off in the same direction. Second, there was a big sign pointing in the direction they were going, with both “Taxi” and “U” (as in “U Bahn” or “subway”) on it. Finally, I realized I had no idea where I was or where I was going, so stunned and dazed was I following the journey to Valhalla I’d just endured. So I figured that the wisest course would be to follow the crowd and the signs. In the best case I‘d end up at the train station and I could walk to my hotel from there in a minute or two. In the worst case I’d hail a taxi and pay a few Euro for the trip to the hotel. It sounds good, right? Well, it did at the time…

Around five minutes later, the crowd had almost completely thinned out. There had been no signs after that first one, and I was on a long path winding off into the distance. I was covered with frozen sweat; the cool still felt good, but I had a feeling that it wasn’t going to feel good for too much longer. Then, up ahead of me, I heard two people talking in English, and they were both wearing “International Road Warrior” t-shirts. And they mentioned something about the U Bahn. I took this as a good sign. “Hey, are you guys going to the train station too?” “Yeah.” “Do you have any idea where it is?” “Um…” “Mind if I follow you, and we can be lost together?” “Sure.” The two guys were in town for the show. They’d flown down from Dublin and were trying to catch a train back to central Munich to the hostel where they were staying for the show. They were both 21 years old, it was the first Manowar show either one had attended. They both seemed amazed that I’d flown to Munich just for the day, just to see Manowar play, and that it wasn’t the first time. But now that they’d seen the spectacle of might that is Manowar on stage, I think they understood.

But that didn’t get us any closer to the train station or to my hotel. We found more “U” signs, but they had no relationship to the actual location of any U Bahn station in this reality. We followed them for what seemed like a long time, to no avail. We tried navigating by the giant tower (nicknamed “Joey’s Dick” by my Dutch friend Joyce earlier in the day) near the Olympiahalle, as one of the Irish boys had marked it as a landmark on their walk to the show earlier in the day. All of this was to no avail – we just seemed to be getting more and more lost. Finally I flagged a taxi, and we rode together back to the train station, and I rode back to the hotel from there. I learned later on that we’d walked for a little over an hour, and had gone over two miles from the hall – but in the wrong direction! Still, it all ended out well, and it was a great experience.

One high point of The Lost Walk was realizing how Manowar is a force that crosses cultural boundaries. I’d met and bonded with people from over 10 countries over the course of one day, and these two young men were another example of the type of person that follows Manowar: strong, self-confident, friendly, and empowered with a great sense of humor. The other high point was that they both thought I was in my 20s. When I told them I was pushing 40 (we got on the subject when I was telling about my visit to Dublin with my wife back in 2000 before we had kids) they wouldn’t believe it. I guess Metal not only makes you strong, it also keeps you young.

So around 1:30 I finally made it back to my hotel. To coin a phrase from “Blow Your Speakers” I only had one thing on my mind: taking a hot bath. My legs were KILLING ME after a day spent standing up waiting on hard tile floors in the train station and then fighting for position in the crowd during the show. And the two-mile hike after the show hadn’t helped either. When I’d checked in earlier in the day I’d noted how the huge size of the tub. It was long enough for me to stretch out and deep enough for me to completely submerge in – I’m not a small guy, so that’s a rare thing in a hotel bathroom. So I take a shower, get clean and start to get warm, and then plug the drain and start filling up the tub. I sit down and wait for the scalding water to rise and cover my aching legs. And I wait, and wait, and wait… And after a little investigation learn that the drain to the tub doesn’t actually seal. I’d been looking forward to this bath for hours and hours. I felt deeply and profoundly betrayed…

Twenty minutes later I was clothed, packed, checked out of the hotel and waiting for my taxi. I took the taxi to the central Munich train station, from there to take the S8 train back to the airport. The ride through Munich in the early morning hours was beautiful; snow was falling and everything was quiet and peaceful and slightly alien to my American eyes. We drove past a handful of giant billboards advertising the Manowar show; I felt filled with deep sense of accomplishment at the day’s events. Despite all the world had thrown in my way – both today and over the past few months – I had persevered.

When the taxi driver dropped me off at the train station, I went inside and had a series of revelations. Of course, I was very tired at this point, having been awake for the last 24 hours, so this series of revelations came very slowly. More like rumbles of thunder than flashes of lightning. It went a little like this:

Revelation 1: This looks like a completely different building than the train station where I arrived. Am I at the wrong place? No, the sign says “Hauptbahnhof” so that’s right…

Revelation 2: This place is huge! Those trains sitting there (it was around 3:00 AM at this point, and no trains were running) are much bigger than the trains I’ve been riding.

Revelation 3: Oh, THAT’S why I never saw Joyce and Hans yesterday!

That’s right. I’d come into Munich on a local train from the airport, and was waiting around in that part of the train station, underground. Joyce and Hans were coming in on a regional train from The Netherlands, and were coming in to a completely different part of the station that I didn’t even realize existed. Sad, but true.

So 20 minutes later and I’m riding the train out to the airport, around a 45 minute ride. There were quite a few Manowar fans back at the train station, but none that I saw going back to the airport to catch a flight home. Perhaps this meant that none were flying, but perhaps it meant that the rest of the long-distance warriors were a lot smarter than I, and decided to sleep before heading home. I was planning to sleep on the plane back to America, of course. By the time that I was in the air I would have been awake for 28 hour straight, with lots of walking and a Manowar concert thrown in for good measure. What could keep me awake?

Coach. Coach class could keep anyone awake, at least when combined with my neighbor. I was sitting next to a middle-aged German woman wearing too much perfume, who kept getting up to go talk to her husband who was sitting 10 rows behind us. Why she couldn’t bother the woman sitting on the other side of her – the one who WAS NOT SLEEPING, I might add – is a mystery to me. And just to make matters worse, this woman smelled like she had eaten a bushel of rotten fruit, and it wasn’t agreeing with her digestion. She had the most horrendously pungent gas that I have ever smelled; I never thought that a human being could make such a smell. So I did my best to smile and ignore her for the 10 hour flight from Munich to Atlanta. I caught a few hours sleep in bits and pieces on the way, but no real rest to speak of.

And now, after an interminable layover in Atlanta, I’m on the two-hour flight back home. The kids will be asleep by the time I arrive, but I suppose that’s for the best – this way I can give them their presents and tell them about the trip and the concert when they’re awake in the new day, instead of when they’re tired and whiny at bedtime. There’s always a silver lining, and as we all know, for every day that stings, two better days it brings.

So if you’ve made it all the way to the bottom of this novella (I just looked at the page count and even surprised myself) you’re probably wondering what just happened. So please let me summarize. There are two things that I’m taking away from this wonderful ordeal. First and foremost, if you are a Manowar fan you should do anything possible to see Manowar play in Germany on this tour. They are in the finest form possible, and the show they’re putting on is second to none. Second, and perhaps even more important, is that Manowar fans are the best people in the world. I met friends – people I hardly know, but whom I would be proud to call Brother and Sister – from eight countries, and each one did everything in their power to make my trip the best it could be. Mark, Joyce, Nicola, Alex, Erman, David – if you are ever in my part of New York, you’d better look me up.

Viral adoption: Self-service BI and COVID-19

I live 2.6 miles (4.2 km) from the epicenter of the coronavirus outbreak in Washington state. You know, the nursing home that’s been in the news, where over 10 people have died, and dozens more are infected.[1]

As you can imagine, this has started me thinking about self-service BI.

Where can I find information I can trust?[2]
When the news started to come out covering the US outbreak, there was something I immediately noticed: authoritative information was very difficult to find. Here’s a quote from that last link.

This escalation “raises our level of concern about the immediate threat of COVID-19 for certain communities,” Dr. Nancy Messonnier, director of the CDC’s National Center for Immunization and Respiratory Diseases, said in the briefing. Still, the risk to the general public not in these areas is considered to be low, she said.

That’s great, but what about the general public in these areas?

What about me and my family?

When most of what I saw on Twitter was people making jokes about Jira tickets[3], I was trying to figure out what was going on, and what I needed to do. What actions should I take to stay safe? What actions were unnecessary or unhelpful?

Before I could answer these questions, I needed to find sources of information. This was surprisingly difficult.

Specifically, I needed to find sources of information that I could trust. There was already a surge in misinformation, some of it presumably well-intentioned, and some from deliberately malicious actors. I needed to explore, validate, confirm, cross-check, act, and repeat. And I was doing this while everyone around me seemed to be treating the emerging pandemic as a joke or a curiosity.

I did this work and made my decisions because I was a highly-motivated stakeholder, while others in otherwise similar positions were farther away from the problem, and were naturally less motivated at the time.[4]

And this is what got me thinking about self-service BI.

In many organizations, self-service BI tools like Power BI will spread virally. A highly-motivated business user will find a tool, find some data, explore, iterate, refine, and repeat. They will work with untrusted – and sometimes untrustworthy – data sources to find the information they need to use, and to make the decisions they need to make. And they do it before people in similar positions are motivated enough to act.

But before long, scraping together whatever data is available isn’t enough anymore. As the number of users relying on the insights being produced increases – even if the insights are being produced by a self-service BI solution – the need for trusted data increases as well.

Where an individual might successfully use disparate unmanaged sources successfully, a population needs a trusted source of truth.

At some point a central authority needs to step up, to make available the data that can serve as that single source of truth. This is easier said than done[5], but it must be done. And this isn’t even the hard part.

The hard part is getting everyone to stop using the unofficial and untrusted sources that they’ve been using to make decisions, and to use the trusted source instead. This is difficult because these users are invested in their current sources, and believe that they are good enough. They may not be ideal, but they work, right? They got me this far, so why should I have to stop using them just because someone says so?

This brings me back to those malicious actors mentioned earlier. Why would someone deliberately share false information about public health issues when lies could potentially cost people their lives? They would do it when the lies would help forward an agenda they value more than they value other people’s lives.

In most business situations, lives aren’t at stake, but people still have their own agendas. I’ve often seen situations where the lack of a single source of truth allows stakeholders to present their own numbers, skewed to make their efforts look more successful than they actually are. Some people don’t want to have to rebuild their reports – but some people want to use falsified numbers so they can get a promotion, or a bonus, or a raise.

Regardless of the reason for using untrusted sources, their use is damaging and should be reduced and eliminated. This is true of business data and analytics, and it is true of the current global health crisis. In both arenas, let’s all be part of the solution, not part of the problem.

Let us be a part of the cure, never part of the plague – we’ll only be remembered for what we create.[6]

[1] Before you ask, yes, my family and I are healthy and well. I’ve been working from home for over a week now, which is a nice silver lining; I have a small but comfortable home office, and can avoid the obnoxious Seattle-area commute.

[2] This article is the best single source I know of. It’s not authoritative source for the subject, but it is aggregating and citing authoritative sources and presenting their information in a form closer to the solution domain than to the problem domain.

[3] This is why I’ve been practicing social media distancing.

[4] This is the where the “personal pandemic parable” part of the blog post ends. From here on it’s all about SSBI. If you’re actually curious, I erred on the side of caution and started working from home and avoiding crowds before it was recommended or mandated. I still don’t know if all of the actions I’ve taken were necessary, but I’m glad I took them and I hope you all stay safe as well.

[5] As anyone who has ever implemented a single source of truth for any non-trivial data domain can attest.

[6] You can enjoy the lyrics even if Kreator’s awesome music isn’t to your taste.

Grandfather, tell me a story!

Are you delivering a technical presentation? Don’t tell me about capabilities.

Don’t tell me about products, or features, or tools.

Tell me a story that I can relate to[1].

I promise this photo is very relevant to every technical presentation

Tell me a story about someone who struggled, and tell the story in a way that I can relate to the struggling character. When you tell me a story, you immediately have my attention, because my human brain is optimized for learning from stories[2].

Tell me a story about a problem, and the pain that problem has caused. The pain makes it real.

Now tell me about the solution. You already have my attention, and my emotional engagement. It’s time to take advantage of this fact.

Tell me about how your products, features, tools, and capabilities were used to solve the problem, and to eliminate the pain.

Why did the person choose a specific tool or feature? How did she use that capability, and what impact did it make? How did the person’s life change and improve because of the actions she took?

When you tell me a story instead of telling me about features, you probably won’t get to mention every feature you want. That’s OK.

If you tell me a story you might only be able to mention 5 or 10 features instead of the 50 or 100 features you wanted to mention. That’s OK, because I’ll remember the impact those few features made, and I’ll know how they can help me.

If you’d talked about all of the features, I probably would not have remembered any of them – unless I already had my own story and could connect the dots myself.

It’s the story that makes the message stick. I wish every technical presenter remembered this. Maybe I should tell them a story…

[1] No, no, not one of those – a real story!

[2] A quick search will yield many results that highlight the value and importance of stories for learning. This blog post presents an excellent summary.


Web Scraping with Html.Table in Power Query

The older I get, the more consistently I encounter things that make me feel old. And it’s not just the usual things, like those damned kids on my lawn. It’s things like technology. And sometimes it’s things like new features in Power BI.

Let’s take, for example, the new “from web” experience that was introduced in Power BI Desktop over the summer, and which is generally available (out of preview) this month. This is a cool feature that at first glance looks pretty straightforward, but it caused me no end of problems.

I started off by reading the available information:

When it became clear that I wasn’t going to be able to do what I needed with only the UI, I started looking into the Power Query functions being used in the query created by the UI. The code in my PBIX file didn’t make any sense to me, so I look in more places, like:

And still, I couldn’t quite figure out how to solve my problem. I was frustrated and annoyed , not just because I couldn’t do what I needed, but because I was convinced that there was some simple, easy thing that I was missing.

And I was right.

This brings me back to feeling old.

When I was your age[1], we only had one HTML tag, and it was <marquee> and we were glad to have it. We didn’t need any of those fancy stylesheets that kids these days have cascading all over the place, because we had H1 and H2 and that was more than enough for any page we could think of.

All pretense aside, I didn’t know that CSS selectors existed before I started trying to use the Html.Table function in Power Query, and that was a real problem. The inline help mentioned CSS selectors in the function description, but without more prior knowledge and context, this wasn’t helpful to me. At all. So I asked for help.

One of the developers on the Power BI team took the time to listen to what I was trying to accomplish, and gave me the code that I’d been so consistently failing to write on my own[2]. Once I had something simple that worked, I was able – with a little more research and some reformatting – to build the queries that I needed, but I don’t think I could have done it without that help.

So, working under the assumption that I’m not the only old guy out there who knows data pretty well, but hasn’t kept up to date on web technologies like CSS, I wanted to share the story and the code.

Here’s the pattern I was trying to implement. This is a relatively common pattern in my experience, although the details are different each time:

  1. Extract a set of links and related data from a parent web page
  2. For each of the links extracted from the parent, extract a set of data from the child web page, where each of the child pages has a consistent format
  3. Use the data from the parent and the child pages to build a data set for reporting and analysis

The specific solution I needed to implement was this:

  1. Extract album name, release date, song names, and links to the individual song lyric pages, from this page:
  2. Extract the lyrics from each individual song page, like this one:
  3. Perform mission-critical analysis on the lyrics.[3]

Let’s take a look at my learning process step by step.

I started off by using the UI, stepping through the new “From web” experience in Power BI Desktop. I knew that this wouldn’t give me everything I needed – you can’t get the URLs behind a link, you can only get the text – but I hoped it would give me a starting point.

from web

It gave me this:


I’m sharing this query as an image and not as text because I want to highlight one of the challenges I faced: the formatting. Because the Html.Table function accepts a complex parameter, I needed to “pretty format” it before I could visually parse it and compare the specific syntax to the available help, and to the source of the web page I was trying to use as a data source.

Even then, I was still stuck. There were not enough points of reference between the HTML and the query for me to relate them and to start modifying the query script. After fighting with this for longer than I care to admit, I asked for help. In return, I got this query:

    Source = Web.BrowserContents(""),
    Custom1 = Html.Table(Source, {{"Song", "a"}, {"Song Link", "a", each [Attributes][href]}}, [RowSelector="div#listAlbum > a[href^='../lyrics/']"])

Before I could use it, I had to reformat it like this:

    Source = Web.BrowserContents(""),
    Custom1 = Html.Table
                {"Song", "a"},
                {"Song Link", "a", each [Attributes][href]}
            [RowSelector="div#listAlbum > a[href^='../lyrics/']"]

That was much better! With this code, formatted so it was clear how the various parts of the columnNameSelectorPairs parameter related to each other, I could finally make some progress on my own. In short order I had this query:

    Source = Web.BrowserContents(""),
    Custom1 = Html.Table
                {"Song", "a"}
               ,{"Song Link", "a", each [Attributes][href]}
               ,{"Album", "div.album"}
            [RowSelector="div#listAlbum > a[href^='../lyrics/'], .album , b"]
    #"Filled Down" = Table.FillDown(Custom1,{"Album"}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Filled Down", "Release Year", each Text.BetweenDelimiters([Album], "(", ")"), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Album Name", each Text.BetweenDelimiters([Album], """", """"), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Between Delimiters1", each [Song] <> null),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","../","",Replacer.ReplaceText,{"Song Link"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Album"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Album Name", "Release Year", "Song", "Song Link"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Reordered Columns", "Lyrics", each fn_GetLyrics([Song Link])),
    #"Expanded Lyrics" = Table.ExpandTableColumn(#"Invoked Custom Function", "Lyrics", {"Lyrics"}, {"Lyrics.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Lyrics",{{"Lyrics.1", "Lyrics"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Song Link"})
    #"Removed Columns1"

The function fn_GetLyrics uses the same technique to pull the actual lyrics from the child pages:

(lyricsPageURL) =>

   Source = Web.BrowserContents(lyricsPageURL),
   Custom1 = Html.Table
         {"Lyrics", "div"}

      ,[RowSelector="br+ div"]
   #"Kept First Rows" = Table.FirstN(Custom1,1)
   #"Kept First Rows"

And now I have the data I need – I just need to analyze it.

There were two additional tools that were invaluable to my success.

The first tool is the CSS selectors reference at,  which provides the syntax for each CSS selector, an example of the syntax, and a description of each example. Just what I needed!

The second tool is the SelectorGadget Chrome extension, which  lets you click on elements on the page, and see the CSS selector that would select them. This may not have been an absolute requirement, but it definitely saved me time and swearing.

Hopefully this has been useful. I may be the only old dog out there trying to learn new tricks, but then again, I may not be. Ether way, get off my lawn!


[1] Please go back and read that in your best “crotchety old man” voice. Thank you.

[2] Thanks again, Ehren!

[3] Or maybe build a demo for the BI Power Session at the PASS Summit next month. It’s hard to tell the difference sometimes.