Power BI Accelerator Week #8 is Live!

Power BI
1

Accelerator Fans,

They say that good things come to those who wait. So, if this is true, then this round’s Accelerator should be REALLY good. Sorry for the delay on this one - it was quite a tough one to put together, and in the midst of end-of-year content rollout, Data Challenge #17 and other things, it just took longer than usual. But we think we’ve got a really fun one for you…

In this week’s problem set, you are given an Excel file with four separate fact tables. All you need to do is import the data into Power Query and create four simple visuals that you all already know how to do. Sounds easy, right?

Well, of course there’s a catch to it. Each of the fact tables we provide are misshapen in a common way that will prevent you from creating the visual without properly reshaping them via Power Query transformation techniques such as pivot, unpivot, transpose and others into the “long and thin” structure that we discussed back in Accelerator Week #2.

Learning these common reshaping techniques is akin to learning end positions in chess or bridge. Once you know how to solve a number of common data shaping challenges, you can often reduce some very complex transformations to one of these common patterns, and then apply your solution in sort of a “cookbook” fashion.

Everything you need is contained in the two attached files. The PBIX includes an Extended Date Table, all the instructions you’ll need plus some key learning resources.

Given the holiday season and the fact that @sam.mckay and I are both taking a bit of a break for the next couple of weeks, you’ve got plenty of time on this one since the live solution session won’t be until Wednesday, Jan 12 at 5pm ET.

Hope you all have a wonderful holiday, and a happy and healthy New Year. Thanks so much for your participation in this initiative and for being part of this great community.

See you in 2022!

  • Brian

Accelerator Week 8 Raw Data Final.xlsx (29.3 KB)
Accelerator Week 8 - Final Problem Set.pbix (20.7 MB)

P.S. As always, huge thanks to my co-pilots on this project, @KimC and @eric_m , who provided invaluable input and guidance on this week’s problem set.

5 Likes
2

Happy Holidays to all the EDNA Members, may you all have a safe and healthy holiday season :slight_smile:

Thanks Brian :slight_smile:

Learning Resources links?

1 Like
3

Hello,

My submission is attached. This is my first post so hopefully I’m attaching it correctly.

I recently worked my way through the first seven Accelerators, and I plan to stay caught up from now on. I’ve learned a ton by doing these and I hope this series continues past #10.

Thanks, and Happy Holidays!
Steve

Accelerator Week 8 - Solution - SteveH.pbix (20.7 MB)

3 Likes
4

Have a good holiday and enjoy these moments with your families

1 Like
5

@Keith ,

Sorry – my bad. I posted the #8 problem set right before I bolted out the door for Christmas travel, and forgot to post the Suggested Learning Resources in clickable form. Here you go…

Suggested Learning Resources

eDNA Transformations and Data Modeling Course

(Focus on the Data Transformation Examples and Advanced Transformations and Query Techniques sections)

Power BI Buried Treasure: Modulo and Integer-Divide

(Hint – this one will come in very handy for problems #3 and #4)

How and Why to Unpivot Data in Power Query

Transpose, Pivot or Unpivot in Power Query?

2 Likes
6

Hi @BrianJ,
Its all good sir. I figure it out earlier today where to find it.

Thanks for posting for others for their reference.

I hope you had a good Christmas and advance Happy New Year.

Thanks
Keith

2 Likes
7

My Accelerator Week 8 solution is attached. Thank you for an interesting and challenging problem set. I’m halfway through the Power Query book by Ken Puls and Miguel Escobar, so the timing was perfect.

My main takeaway is that if there is any pattern to the data file whatsoever, Power Query can transform it into a proper fact table. The right combination of features may require experimentation, but that is no problem in Power Query. I must have tried and deleted steps a dozen times on a couple of the exercises.

Happy holidays, everyone!

Mark
Accelerator Week #8 - Solution MH.pbix (23.7 MB)

3 Likes
8

Happy New Year everyone.

Attached please find results. Some great learnings once again. Not sure how elegant my results really are but they seem to have worked. As its the holidays no formatting has been done to the visuals. (Concatenate on holiday? Not likely! )

Accelerator Week 8 - Final Problem Set.pbix (20.7 MB)

2 Likes
9

Welcome and Congratulations, Steve.

I agree this is an instructive series.

Regards,

John Giles

2 Likes
10

Accelerator Week 8 - Final Problem Set - tb.pbix (20.7 MB)
Here is my submission. Good practice - seems I spent a lot of time playing around with this before finding my way to the end. Thanks,

Tim

2 Likes
11

Hi There,

This round was very interesting. I cannot say it was hard for me as I already worked on similar transformations. I just had to find a time to do it and It was more like a welcome and helpful revise.

I took the time to apply what I have learned in previous rounds, and from the different resources I got on eDNA, like setting a parameter for the location of the source file, organize the queries in folders or loading to the model only the necessary queries. The dates table was already there I just had to adjust the starting date

The easiest problem to solve was the #4.

For problem #3, it was like when you have the word on the tip of your tongue, but it doesn’t want to come out until the moment I remembered that I had made a video in 2018 to solve the same problem. If you are interested, here is the link (the video is in French) Lignes décalées, cas 02. Répartir les Produits, Quantités et Prix de Vente (Méthode 01) - YouTube

Thank you for the time you are taking to set up all these resources.

Please find below my solution for the Week 8

Accelerator Week 8 - MehdiH - Solution.pbix (20.7 MB)

Have a nice week

2 Likes
12

Welcome @stevens and don’t worry I always feel like that

Not sure that my results are elegant but they seem to work

:smiley:

2 Likes
13

Brian,

Happy New Year! This Accelerator was great. The stack data problem was spot on. I can see myself using modulo and integer divide often.

Accelerator Week 8 - Boehnke Final.pbix (20.7 MB)

Thanks,
Brad B

1 Like
14

Hi @BrianJ

Many thanks for another learning opportunity. There were some cool take aways for me; like

  1. Using Modulo and Integer divide
  2. Fill Down
  3. How to fix the “Too many elements in the enumeration to complete…” pivot error in Power Query

Accelerator Week 8 - Final Problem Set - Eze.pbix (20.7 MB)

1 Like
15

Accelerator Participants,

Happy belated New Year! I’m back after a much-needed holiday break. Great to see so many people enjoying this round’s problem set, and lots of different solution approaches.

Registration in for the live solution event with @sam.mckay and me at 5 PM ET this Wednesday 1/12 is available here:

Hope to see you there!

  • Brian
1 Like
17

Hello Brian:

Here is my submission for Accelerator 8, as it stands now.

Until a couple of hours ago, I was feeling great about this exercise with one exception, which was problem 1. I knew I’d have an issue assigning the highest payer to each month along with the average salary. I didn’t want to lift anyone else’s code. I thought I could work on it as time permitted. I was fine with that. I’ll explain the rest in the section devoted to each problem.

Problem 1 - (Time to complete - 45 minutes, with the exceptions noted below)

Aside from linking average salary and highest payer, I thought everything was fine. I created the matrix and checked several of the dollar values at random. It looked great until I tried to sort the Education Level by the Education Sort. There was an error stating that there wasn’t a one to one match between columns. Further research indicates that I must’ve butchered a step in the data shaping, so I’ll try to fix the issue and resubmit tomorrow.

Problem 2 - Double Headers (Time to complete - 15 minutes)

I used the tutorials you suggested and found another tutorial from “Data Training IO”

Problem 3 - Stacked Data (Time to complete - 20 minutes)

I used the tutorials you suggested.

Problem 4 - Horizontal Pairs (Time to complete - 2 hours)

I stumbled around on this problem, but Melissa de Korte’s “Unpivot Multiple Column Pairs In Power Query” (using M Code List.Zip). About 15 minutes later, I’d finished the problem.

I did additional research and analysis on Melissa’s M Code after completing the problem.

So, here’s my submission. I’ll try to correct the errors in Problem 1 prior to tomorrow’s review session.

Regards,

John Giles

Accelerator Week 8 - JohnG.pbix (20.7 MB)

1 Like
18

Folks,

My apologies for the last-minute change, but we’re going to have to bump the Accelerator Live Solution Event to the same time next Wednesday 1/19. Looks like I picked up a case of Omicron at a family wedding this weekend. Hopefully, not a huge deal but I’m not going to be in shape to teach the course tonight.

Thanks to all who have submitted solutions thus far – definitely an interesting range of approaches taken. Looking forward to discussing this next week.

– Brian

19

Hoping you feel better, soon.

1 Like
20

Hoping it is not grave I wish you a speedy and good recovery

21

Hope you make a speedy recovery and feel better soon @BrianJ