Month: January 2022

Reporting on your Spotify listening history

On By Matthew RocheIn Power BI3 Comments

I’ve listened to music on Spotify for over 5,000 hours since I subscribed to their service. I listen the most in the early morning when I’m at the gym and before my morning meetings begin. I’ve listened to more Amon Amarth than I’ve listened to my next three top bands combined, but the album I’ve spent the most time listening to is “The Black Parade” by My Chemical Romance.[1] The song I’ve listened to the most is “O Father O Satan O Sun!” by Behemoth – Since March 2017 I’ve listened to it 620 times, for a total time of 69 hours, 15 minutes.

How do I know this? I know because for the past few years I’ve been reporting on my Spotify listening data.

Spotify doesn’t have a viable API for this type of analysis, but you can request a copy of your listening history and other account data by emailing privacy@spotify.com[2]. It takes about a month to get your data, so if you want to report on your own listening history, you should probably email them today.

If you’re interested in using my report as-is or as a starting point for your own analysis, I’ve shared a PBIT file here:

Here’s what you’ll probably need to do:

  1. Request a copy of your Spotify listening history by emailing privacy@spotify.com.
  2. Wait approximately 30 days for Spotify to prepare your data.
  3. Download the zip file prepared by Spotify.
  4. Extract the zip file to a folder on your PC.
  5. Locate the folder that contains JSON files. This report uses only the “endsong*.json” files that contain your listening history. The other files probably contain other interesting data – who knows?
  6. Open the PBIT file in Power BI Desktop.
  7. When prompted, enter the path to the folder containing your Spotify JSON data.
  8. Click “Load”.
  9. Cross your fingers, because I’ve never created a PBIT template before. Even though I’ve tested it, I don’t really know if this will work as hoped for you and I’m probably not going to provide any timely support if it doesn’t work.

Music is a huge part of my life, and I’ve had dozens of music-related pet projects over the past 30 years. When I was in college, my “learning a new programming language” routine involved re-implementing my record collection manager in whatever new language that semester’s classes called for. Being able to play with this familiar and very-meaningful-to-me data in Power BI has been a fun way to spend a few hours her and there over the past few years.

Sadly, the reason I’m using this report today is to figure out what albums I need to add to my music collection when I delete my Spotify account. I’ve discovered so much awesome new music because of Spotify[3], and I am now using this report to prioritize what CDs to purchase. This isn’t why I originally made this report, but this is where we are today.

As you’re probably aware, Spotify has chosen to spread disinformation and hateful propaganda and to prioritize their profits over public health. This is their right to do so, since they’re apparently not breaking any laws, but I won’t give them any more of my money to knowingly cause harm and poison the public discourse.

If you want to call me out for being a snowflake or SJW or whatever, please do so on Twitter, not here. I’ll delete any blog comments on this theme, and no one will get to see how witty you are – wouldn’t that be a shame?

Whether or not you’re planning to delete your own Spotify account, feel free to download this PBIT and check out your own listening history. I found insights that surprised me – maybe you will too. This report is something of a work in progress, so you might also find interesting ways to complete and improve it that I didn’t think of.

As for my post-Spotify music streaming world, I’ll probably end up getting a family subscription to Amazon Music. The price is the same, and it looks like all of the top albums I’ve been listening to on Spotify are there.

I’ll also definitely be spending more time with the CD/MP3 collection I spent decades building before I discovered Spotify. I own thousands of albums by hundreds of artists, and curating this collection was an activity that gave me joy for many years. Now I’m feeling motivated and inspired to rediscover that collection, and the personal satisfaction that comes from mindfully expanding it.

Specifically, I’m adding my personal music collection to Plex Media Server, and streaming it to all of my devices using their excellent PlexAmp app. I’ve been moving in this direction since it became obvious Spotify was going to keep funding and promoting hatred and ignorance, and it’s already reminded me of the wide world of music that’s not included in the big streaming catalogs[4].

Plex and PlexAmp make it incredibly easy to manage and stream your personal media, and they have both a REST API and a SQLite database for tracking and reporting on library contents and listening activity – and it doesn’t take a month to get your data. Maybe there’s a silver lining here after all…

[1] If you’re wondering where Manowar is on this list, they’re #8. Please consider that Spotify only has a limited selection of Manowar’s music, and that when I listen to my personal music collection it isn’t included in this data.

[2] For more information, see the Privacy page at https://www.spotify.com/us/account/privacy or whatever variation on this URL works for your part of the world.

[3] Four of my top 10 artists are bands Spotify recommended to me, and which I probably would not have heard of were it not for their “release radar” and similar playlists.

[4] For example, my personal collection includes every album, EP, single, and DVD Manowar has ever released. I may not literally have an order of magnitude more Manowar than Spotify does, but it’s pretty close.

You are not the customer!

On By Matthew RocheIn Career, Communication, Cooking and Baking, Diversity1 Comment

Unless you’re building a solution for your exclusive personal use, you are not the customer. If you are not the customer, you need to invest in understanding the customer’s motivations, problems, and goals. The right solution for you may not be the right solution for someone else – even someone you may think of as being “like you” – for reasons that may not be apparent until you look more closely.[1]

This is another post looking at problem domains and solution domains, and it’s inspired by a recent non-technical conversation over brunch[2] with some foodie friends. My wife and I had gotten together with friends who love good food – people who we’ve had over for dinner many times over the years, and from whom we’re always delighted to receive dinner invitations. These are people who know how to cook, and who have invested in their kitchens over the years, so they’re well-equipped to cook what they want.

Imagine my surprise when a friend who has a fabulous combi oven[4] in her kitchen kept talking about how much she loved her new toaster oven that was also an air fryer. Or when another friend who is more than capable of making wonderful meals from scratch waxed poetic about how Thermomix now allows you to use community recipes from any region so she can now make that dumpling recipe she’s been looking at… and about how easy it is to make toast in her June smart oven because it recognizes that she’s put a slice of bread into it and knows how dark she likes her toast…

My initial reaction was to point out how all of these toys were unnecessary, because they already had the real tools they needed to do all these things and more[5], but as the conversation went on I realized I was in a tastier version of a very familiar conversation. This was a conversion about pro and self-service tools, and  about persona-appropriate experiences.

Have you ever been in a conversation when someone suggests that “all you need to do” to solve a problem is to use some code-first tool or service, when all you really want is something lightweight and simple? You probably don’t have an environment set up already, and even if you did the proposed tool don’t solve the problem in the way you want to solve it… even though the tool is capable of solving the problem.

Have you ever built a report that gives its users all the information they need to answer their questions, only to find that your target audience doesn’t like or use the report, and instead keep building their own workarounds?[6]

Have you ever found yourself on the other side of that scenario, where someone has given you a report with all of the numbers, but none of the answers?

These are all variations on the same theme: the importance of persona-appropriate experiences. Delivering capabilities is never enough – you need to deliver capabilities via an experience that works for the people who will use it.

Building an experience people want to use requires a meaningful understanding of those people. What are their backgrounds, their skills, their motivations, their priorities, their preferences… as well as the problems they need to solve. There are lots of ways to build this understanding, and they all start with your acknowledging that you are not the customer, and your willingness to spend time understanding the actual customer’s problems before you start trying to solve them.

I’m confident that a commercial restaurant kitchen would have all of the capabilities you would need to prepare breakfast – and more. I’m similarly confident that you would prefer something smaller, less complex, and more familiar – like your own home kitchen – and that if you were only given a commercial kitchen you would soon start looking for alternatives or workarounds that work better for you.

The next time you’re working to solve someone else’s problems – whether you’re building a BI solution or a software product or service – pause to ask yourself if you’re giving a commercial kitchen to a home cook. While you’re paused, also take a minute to remind yourself that you’re not the customer.

[1] The admonition “you are not the customer” was included in some of the first training materials I read when I became a program manager at Microsoft. It took me a few years to understand why this is so important, but now it’s something I think about almost every day.

[2] This was December 2021, our first restaurant meal with friends since December 2019, right before the omicron wave arrived in the Seattle area. Who knows when we’ll be able to dine out again…

[3] Kind of like me. I’ve been cooking and baking as a primary hobby for over 30 years, and have a wonderfully-equipped home kitchen. It doesn’t have every tool or toy I might want, but it has everything I need, and quite a few specialized tools I might not technically need but which make common-enough tasks easier and less time consuming.

[4] A combi oven is an oven that combines convection and steam, and which allows you to precisely control both the temperature and humidity while you’re baking.  This simplifies baking lots of things, from bread to cheesecake, but they tend to be very expensive. They’ve just recently moved from high-end professional kitchens into high-end home kitchens. I don’t own one, but I definitely want one.

[5] Thankfully I had my mask on as we were waiting for our food to arrive, which made it easier for me to keep my damned mouth shut for just one minute, which is not my forte.

[6] Yes, in Excel.

On joining the product team

On By Matthew RocheIn Career, Communication, Power BILeave a comment

Almost two years ago I published a blog post and video on Being a Program Manager at Microsoft, in which I shared some of my personal experience and advice based on my decade plus at Microsoft. I’ve received an incredible amount of feedback[1] on that 2020 post… and now it’s time for a follow-up.

My last few posts have focused on topics related to problem domains and solution domains, including how these concepts relate to Power BI and my work on the Power BI CAT team. What about for people who are currently Power BI or data professionals who might be thinking about changing careers to join the product team? These are pretty abstract concepts – what do they mean to you?

This conversation calls for an analogy, and since I don’t know anything about driving race cars or automotive mechanics, I’m going to use an analogy about race car driving and mechanics[2].

As a Power BI or data professional, you’re like a race car driver.

Your job is to win races. You need to respond to the rapidly-changing demands of the moment to deliver victory for your team[3]. You’re in the spotlight,

As a race car driver, there are two main factors that contribute to your wins and losses:

  1. Your skills as a driver
  2. The performance of the car you’re driving

To win a race, you need to know how to get the most from your car, and make it perform for the conditions on the track, and within the rules of the race.

As a program manager on the product team, you’re like a mechanical engineer responsible for designing, refining and tuning the race car engine.

Your job is to deliver an engine that performs the way your drivers need it to perform, as part of the larger system of the race car as a whole. The race car needs to have the capabilities and characteristics that enable drivers to win races, and the engine is a key part of that… as is the steering, suspension, and whatever other subsystems make up a race car.

Your job is not to drive the race car, and you can excel at your job without ever having won (or driven in) a race.

Re-read that last sentence and pause to let it sink in. If you’re thinking about joining the product team that makes your favorite software tool or platform, you’re also thinking about giving up the day to day job of using that software.

Even more than this, the skills that you’ve built as a race car driver – or as a data professional – are no longer directly relevant. Please don’t get me wrong – if you’ve spent years implementing data and BI solutions this will serve you very well as a program manager on a product team building data and BI tools. But that experience will be valuable in the way that having been a race car driver could be valuable in informing the design and optimization of race car engines.

Having an intimate understanding of – and deep personal experience with – the problem domain can help inform your work in building and improving the solution domain… but your job is no longer what it used to be. You need to learn and master new skills to be successful in the new role, and that means no longer being the expert on the problem domain like you used to be.[4]

This can be a difficult realization and wake-up call for people making a career change, because it means giving up – or at least moving away from – the work they know and love. It can also mean discovering new work and new challenges that they may love even more… but change is hard at the best of times, and it doesn’t always come at the best of times.

I hope this blog post and analogy[5] doesn’t scare anyone off. Working on a product team can be incredibly fun and rewarding – it’s just different from working with the product that the team builds and ships. It’s another situation where the problem domain and solution domain differ in ways that aren’t always obvious until you’ve had a chance to work in both worlds.

Joining the product team after working as a data professional changes your relationship with the product. What used to be your solution domain, where you were the expert responsible for understanding the product and delivering solutions based on its capabilities, now becomes your problem domain. You’re responsible for increasing its capabilities to make it more useful for others to build their solutions, but you’re no longer building those solutions yourself.

 

[1] I continue to believe that this video may have been the best thing I did in 2020. I’ve had dozens of conversations with people around the world who want to be program managers or product manager – at Microsoft or other tech companies, and some of them have already achieved their goal. This feels like I’m actually making a difference in the world when the world is doing its best to make me feel powerless, and that’s like a gift that keeps on giving. I may not be the only one who feels this way. While this post was in draft form waiting to be published, my friend Davide created a website to showcase this video – he even transcribed it! You can check it out here: http://aka.ms/being-a-ms-pm.

[2] Because of course I am. This is an analogy that presented itself to me in one of those conversations mentioned just one footnote earlier, and which works as well as any I’ve found so far.

[3] There are teams in racing, right?

[4] This is where it’s useful to have a person or team responsible for bridging these two worlds.

[5] Another possibly less scary analogy could have involved someone who loves eating becoming a chef in a professional kitchen. Since I know enough about these worlds to be dangerous, I didn’t trust myself to use this analogy without ratholing on lots of details no one but me would care about. Maybe I’ll try that one again when I have more time to think about it…

Simplifying the solution domain

On By Matthew RocheIn Communication, Community, Data Culture, Metaphors, Power BILeave a comment

My recent posts have focused on the importance of understanding both the problem domain and solution domain in which you’re working, the value of operating in both the problem domain and the solution domain, and some specific resources and techniques for solution-oriented practitioners to increase their fluency in understanding the problem domain. A lot of the message I’ve been trying to get across in these posts can be summarized as “the problem domain is really important, and we need to get better at appreciating and understanding the problems we work to solve.”

This message is predicated on two basic assumptions:

  1. You, the reader, are a technical professional of some sort[1], and you need to deliver technical solutions to people with problems
  2. The people who have problems can’t solve those problems on their own

Let’s look more closely at that second assumption, because it’s becoming increasingly less valid, and less true. And let’s look at it through the lens of… furniture.

Close your eyes and imagine for a moment that you need a table. Let’s say you need a bedside table. What do you do?

As you read through this list you were probably evaluating each option against your own requirements and preferences. Odds are you’ve chosen one or more options at some point in your life. Odds are the criteria you used to decide which options are viable or preferable have changed as your circumstances, skills, and budget have changed.

I couldn’t find Ikea instructions for a bedside table, but honestly I didn’t try very hard

Of course, the decision doesn’t need to be about a bedside table. It could be about a dining room table, or a bed, or a full set of kitchen cabinets. Each option will have different but similar choices, and you’ll likely use different criteria to evaluate and select the choice that’s right for you.

This is also true if the choice is about business intelligence solutions, which may not technically be considered furniture[2]. Close your eyes once more and imagine for a moment that you need a report, which may or may not include a table.  What do you do?

  • You could use an existing report
  • You could start with an existing report and customize it
  • You could start with an existing dataset use it to build a new report
  • You could start with an existing dataset, customize it using a composite model, and use it to build a new report
  • You could work with a center of excellence or BI team to create a dataset, customize a dataset, create a report, or customize a report to meet your needs
  • You could locate or create data sources and build a custom BI solution end-to-end, maybe getting some help along the way if you don’t already have the tools and expertise required for the project

The parallels are easy to see, and they hold up surprisingly well to closer inspection. Just as ready-to-build options make it possible for someone with no woodworking or cabinetry skills[3] to self-serve their own furniture, DIY BI tools[4] like Power BI make it increasingly easy for someone with no data preparation or data modeling skills to build their own BI solutions.

To step away from the furniture analogy, tools like Power BI simplify the solution domain and make it easy for problem domain experts to solve their own problems without needing to become true experts in the solution domain.

Any finance or supply chain professional can use Power BI Desktop to build reports that do what they need, without needing to ask for help. Their reports may not be the most beautiful or best optimized[5],  but maybe they don’t need to be. Maybe “good enough” is actually good enough. Just as it might make more sense to buy a $40 Ikea bedside table instead of spending hundreds or thousands of dollars on something fancier, it might make sense to stack a few milk cartons on top of each other and call it good, at least until you can save up for something better[6]. There is no one size that fits all.

If you are a Power BI professional, it may be difficult to think of the “boards and cinderblocks bookshelf” approach as acceptable, but in most circumstances it’s not up to you to decide. The tools for DIY BI are widely and freely available, and people will use them if they present the most attractive and accessible option to solve their problems. You can’t stop them from building DIY solutions, but you can help them build better ones.

This is where having an effective and engaged center of excellence comes in. Whether you think in terms of problem and solution domains, or in terms of enabling DIY through readily available tools and guidance[7], you can help make “good enough” better by meeting your problem domain experts where they are. They have the tools they need to get started building their own solutions, but they probably need your expertise and assistance to achieve their full potential.

You should help them.

[1] Probably a Power BI practitioner, but who knows?

[2] I have repeatedly seen Power BI referred to as a “platform” which is basically the same thing as a table, so I’m going to withhold judgment on this one.

[3] Someone like me! I married a carpenter and have never found the motivation to develop these skills myself.

[4] DIY BI has a ring to it that SSBI doesn’t have. Why don’t we start using DIY BI instead. DBIY? D(B)IY? D(BI)Y? Hmmm…

[5] They may indeed be the Power BI versions of my DIY home improvement projects.

[6] Please raise your hand if you too had bookshelves made of random boards and cinderblocks when you were in college, and were also happy with the results.

[7] This is the second place in this post where I’ve shared a link to this Marketplace story. If you run a Power BI COE for your organization, do yourself a favor and listen to the story – it will inspire you to think about your COE in different ways.

Understanding the problem domain

On By Matthew RocheIn Communication, Data Culture, Diversity, Power BI4 Comments

If you’re reading a Power BI-focused blog[1] you’re probably someone who builds solutions. As a BI and data practitioner, you may build reports, datasets, dataflows, databases, or any number of technical artifacts that together represent a solution domain. You have a toolset at your disposal, and you invest your valuable time and energy into refining the skills needed to use those tools.

But what if you’re holding the right tools, while using them to implement the wrong solution? This could happen if you’re holding a hammer and building a chair when what’s really needed is a table, but it could also happen if you’re using Power BI to build a solution that deliver what its users need to make their jobs easier.

Action doesn’t equate with value, and time spent solving a problem you don’t meaningfully understand is often time wasted. So how do you, as a Power BI author, meaningfully understand problems in domains like finance, supply chain, HR, or patient care if you lack personal experience in those domains?

Asking good questions is a great start.

If you want to deliver a fix for a problem someone has reported, asking targeted closed-ended questions to narrow down the scope of the problem can be valuable. What is the error message, when did it start happening, what are the steps to reproduce the problem, does it happen all the time, does it happen on other computers, does it happen for other users… that sort of thing.

This type of question is often great if you need to deliver a fix, but what about when you need to deliver a product? What if you need to deliver a Power BI app that will help a group of people be more productive completing a complex set busines processes?[2]

Asking good questions is still a great start, but you need to ask different questions.

While focused, detailed, targeted questions can be very effective for scoping down a tactical problem to deliver a fix, they tend to not be great for understanding a broader problem domain. Ask closed-ended questions when you want to deliver a fix; ask open-ended questions when you want to understand the problem.

What does your current process look like, where are the places where you struggle, what are the triggers that would cause you to begin the process, why do you do it that way, who works with the output you produce, and what do they do with it… that sort of thing.

If you’re used to asking the first sort of question, the second sort may not be obviously useful. What would you do with these answers? How can you act on them?

The short answer is “it’s complicated” and the longer answer is “read The Customer-Driven Playbook by Travis Lowdermilk and Jessica Rich.”

This amazing book presents a “hypothesis progression framework” that includes tools for learning about customers and their problems before building a solution to those problems. This framework is broken down into four phases:

At the risk of oversimplifying 250 pages of expert guidance, the framework looks something like this:

  • Who is my customer, what does she do, what motivates her, and what problems does she have?
  • What is the scope of my customer’s problem, how does it affect her, and what is its impact?
  • How well the concept you’ve come up with to solve to solve the problem actually fit with your customer’s needs, does its value proposition motivate her to learn more, and how will the limitations of the concept reduce its value?
  • How well does the product or feature you’ve built to implement the validated concept actually solve the customer’s problem it was built to solve?

Each of these phases is presented with best practices for asking the right types of questions of the right people in the right way, with guidance for formulating the right questions, asking them in structured experiments so you can better trust the answers you get, and then making sense of what you’ve heard. That sensemaking process is where your deep expertise in the solution domain will help you the most, as you look closely and mindfully at what you you’ve heard and decide how to act… but most of the process is about learning the details and nuances of the problem, not about solving it.

Albert Einstein once said “If I had an hour to solve a problem I’d spend 55 minutes thinking about the problem and 5 minutes thinking about solutions.” Think about that.

In my experience most technical professionals don’t value and appreciate the importance of the problem domain, and instead focus their energies on solutions to problems they don’t fully understand. We can do better, as individuals and as teams and as an industry, and The Customer-Driven Playbook is an excellent place to start. I hope you’ll read it, and I hope you’ll let me know how it changes the way you approach problems and solutions.

[1] Does BI Polar count as a Power BI-focused blog? These days it feels like I’m posting more about ancillary topics and less about Power BI itself.

[2] For that matter, what if you need to deliver a complex commercial software product like Power BI?

Into the Date Dimension!

On By Matthew RocheIn Dataflows, Power BI, Power Query1 Comment

Late last year I was spending some vacation days playing with one of my favorite personal data sets[1], and I realized I needed a better date dimension. So I asked for one.

A bunch of folks from the Power BI community shared their favorite Power Query date dimension queries[2] – you can look at the Twitter thread for links if you’re interested. With their inspiration (and code!) I picked what I liked, discarded what I didn’t, and came up with this:

let
// To be turned into parameter
startDate = #date(2017,01,01) as date, 
// To be turned into parameter
endDate = #date(2021,12,31) as date, 
// To be turned into parameter
culture = "en-US" as text,

// Beginning of actual query 👇 
Source = List.Dates(startDate, Duration.Days(Duration.From(endDate - startDate)) + 1, #duration(1, 0, 0, 0)),

TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns , "Year", each Date.Year([Date]),type text),
InsertQuarterNumber = Table.AddColumn(InsertYear, "Quarter Number", each Date.QuarterOfYear([Date])),
InsertQuarterName = Table.AddColumn(InsertQuarterNumber, "Quarter Name", each "Q" & Number.ToText([Quarter Number])),
InsertMonth = Table.AddColumn(InsertQuarterName, "Month Number", each Date.Month([Date]), type text),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "Start Of Month", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "End Of Month", each Date.EndOfMonth([Date]), type date),
InsertDayOfMonth = Table.AddColumn(InsertEndOfMonth, "Day Of Month", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDayOfMonth, "DateInt", each [Year]*10000 + [Month Number]*100 + [Day Of Month]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM", culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM", culture), type text),
InsertShortMonthNameYear = Table.AddColumn(InsertShortMonthName, "Month Short and Year", each [Month Name Short] & " " & Number.ToText([Year]), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthNameYear, "Month and Year", each [Month Name]& " " & Number.ToText([Year]),type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter and Year", each "Q" & Number.ToText([Quarter Number]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Day of Week", each Date.DayOfWeek([Date]) + 1),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd", culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, "Day Name Short", each Date.ToText([Date], "ddd", culture), type text),
InsertEndOfWeek = Table.AddColumn(InsertShortDayName , "End Of Week", each Date.EndOfWeek([Date]), type date),
InsertedStartOfWeek = Table.AddColumn(InsertEndOfWeek, "Start of Week", each Date.StartOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertedStartOfWeek, "Week of Year Number", each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "Week of Month Number", each Date.WeekOfMonth([Date])),
InsertYearMonthNumber = Table.AddColumn(InsertMonthWeekNumber,"Year and Month", each [Year] * 100 + [Month Number]),
InsertYearAndQuarterNumber = Table.AddColumn(InsertYearMonthNumber, "Year and Quarter", each [Year] * 100 + [Quarter Number]),
InsertYearAndWeekNumber = Table.AddColumn(InsertYearAndQuarterNumber, "Year and Week", each [Year] * 100 + [Week of Year Number]),
InsertDecadeName = Table.AddColumn(InsertYearAndWeekNumber, "Decade Name", each Text.Range(Text.From([Year]), 0, 3) & "0s"),
InsertDecadeNumber = Table.AddColumn(InsertDecadeName, "Decade Number", each Text.Range(Text.From([Year]), 0, 3) & "0"),
InsertStartOfQuarter = Table.AddColumn(InsertDecadeNumber, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
InsertEndOfQuarter = Table.AddColumn(InsertStartOfQuarter, "End of Quarter", each Date.EndOfQuarter([Date]), type date),
InsertDayOfYear = Table.AddColumn(InsertEndOfQuarter, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertIsWeekday = Table.AddColumn(InsertDayOfYear, "Is Weekday", each if [Day of Week] = 1 or [Day of Week] = 7 then 0 else 1),
InsertIsWeekend = Table.AddColumn(InsertIsWeekday, "Is Weekend", each if [Day of Week] = 1 or [Day of Week] = 7 then 1 else 0),
#"Reordered Columns" = Table.ReorderColumns(InsertIsWeekend,{"DateInt", "Date", "Year", "Year and Quarter", "Year and Month", "Year and Week", "Quarter Name", "Quarter Number", "Quarter and Year", "Start of Quarter", "End of Quarter", "Month Name", "Month Name Short", "Month Number", "Month and Year", "Month Short and Year", "Start Of Month", "End Of Month", "Week of Year Number", "Week of Month Number", "Start of Week", "End Of Week", "Day of Year", "Day Of Month", "Day of Week", "Day Name", "Day Name Short", "Decade Name", "Decade Number", "Is Weekday", "Is Weekend"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"DateInt", Int64.Type}, {"Year", Int64.Type}, {"Year and Quarter", Int64.Type}, {"Year and Month", Int64.Type}, {"Year and Week", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Number", Int64.Type}, {"Week of Year Number", Int64.Type}, {"Week of Month Number", Int64.Type}, {"Day of Year", Int64.Type}, {"Day Of Month", Int64.Type}, {"Day of Week", Int64.Type}, {"Decade Number", Int64.Type}, {"Is Weekday", Int64.Type}, {"Is Weekend", Int64.Type}, {"Quarter Name", type text}, {"Decade Name", type text}})
in
#"Changed Type"

In case you can’t visually parse that unfortunate wall of text, here’s what it looks like in my data model:

As I keep playing with my pet project I’ll probably keep tweaking the date dimension query to add, remove, or refine it as needed. When I’m done I may turn it into a dataflow, or I may make it into a function that takes my project’s min and max dates as input parameters. Who knows?

What I know for sure is that the next time I’m looking for a date dimension query, I’ll look here.

Update: I’ve published the pet project where this date dimension ended up – you can find the details and download the PBIT file if you’re interested.

This PBIT also includes a tweak to the query shared above to dynamically set the start and end dates included in the data source. If you’re interested in this pattern and don’t want to bother downloading the PBIT, here’s what’s different.

// Update as necessary to reference the appropriate date column in your data
startDate = Date.StartOfYear(Date.From(List.Min(#"Spotify Extended History"[ts]))) as date,
// Update as necessary to reference the appropriate date column in your data
endDate = Date.EndOfYear(Date.From(List.Max(#"Spotify Extended History"[ts]))) as date,
// To be turned into parameter as needed
culture = "en-US" as text,

 

[1] I made a GDPR request of Spotify, asking for all of the data they had related to my account. It took them about a month to prepare that data and make it available for me to download, but it was worth the wait. You can request your own data too: https://www.spotify.com/us/account/privacy/

[2] And a bunch of other folks shared DAX and SQL solutions.