PROBLEM of the WEEK #6 (PQ) - Addressing Irregular Data Formats

[Matthias]

Thanks @BrianJ for the hint to the “Feldmann Shift”. Glad that I (re)visited it due to your recommendation!

[BrianJ]

Entry received via email from Vida Vaitkunaite

Summary

Hi Enterprise DNA Team,

Please find attached PBI file with my solution.

It was pleasure to practise my skills with this challenge.

All Power Query steps were done in User Interface. I only added data types manually to the formula in the last 2 steps to save extra step of changing data types.

Aging groups needed to be sorted in waterfall chart. For performance reasons data (Aging Group and Sort Key) was entered manually, instead of duplicating Original Data query and getting Aging groups from this query. Aging Groups is still Power Query table - not DAX. I then added relationship between Original Data and Sort Table and used the later one in the chart. However, I added 2 measures to sum Balance and Days Aged as I thought it is better to use measures instead of columns.

Kind Regards,

Vida Vaitkunaite

Enterprise DNA Problem of the Week #6 - Vida .pbix (3.8 MB)

[Sue]

@JarrettM on a separate note, I’ve been able to attach to Sage50 Accounts this week, been a bit of a challenge, but happy to share how if it’s of any use

[JarrettM]

@Sue,

Version all of my clients use is Contactor 100. Don’t know if that makes a difference or not. still interested in your thoughts.

Thanks
Jarrett

[Sue]

The version I’m working with Sage50 and you have to download a copy of the app on your desktop and can then connect using an ODBC. Did you want me to start another post or email you?

[JarrettM]

@Sue,

Start another thread please. I’m sure this may help others in the future, so want it here in the forum.

Thanks
Jarrett

[JarrettM]

Here is the the YouTube link to the video where I explain my entry:

Addressing Irregular Data Formats - Problem Of The Week #6 (Power Query Solution)

Any questions, please feel free to ask.

Here is my PBIX file
Enterprise DNA Problem of the Week #6 Jarrett Solution.pbix (3.8 MB)

Thanks
Jarrett

[Matthias]

@JarrettM that was a nice explanation of the small gap mystery for several entries. In my opininion the best option for the Type Detection is “Never detect column types and headers for unstructured souces”.

I liked the use of addition of the amounts and the substraction of the days through the UI, it is something I tend to neglect.

[BrianJ]

All,

Great job all around on this week’s problem – a record 22 separate entries. Thanks to everyone who participated. I hope we will see you all as regulars in future problems, and continue to grow the league of Enterprise DNA Problem Solvers.

If you solved this week’s problem (even if you were $1.05 off – hey, that could happen anyone… cough…) and would like a laptop sticker, please send me your physical mailing address (note: sending your email address won’t help me) at brian.julius@enterprisedna.co and I will be glad to drop one in the mail to you. If you sent in your mailing address with your entry, no need to resend – I’ve got you covered.

Thanks to @JarrettM for leading this past cycle, and formulating a really great, practical problem.

We will be back this Wednesday with @MudassirAli leading a doozy of a problem.

See you all then, and thanks again for your engagement in the Enterprise DNA community.

– Brian

[quantumudit]

Here goes my solution for Week-06 POTW.

Week06_Addressing_Irregular_Data_Formats_PowerQuery_Udit.pbix (3.8 MB)

I’ m quite late to submit the solution for this POTW but, its always better to be late than never.
Referenced the primary table to create the “Age Grouping” table for proper sorting.

Indeed a great exercise with some trimming and splitting

Will submit the Week-07 solution very soon as well…

Again, thanks a lot to @JarrettM & @BrianJ for the amazing puzzles.

[JarrettM]

@quantumudit,

Make sure to watch the YouTube video with the solution. The Balance total you come up with is incorrect. No worries, a bunch of others did the same thing.

Thanks
Jarrett

[quantumudit]

Yeah !! As the name says “Irregular data types”…

Thanks for the video @JarrettM, I corrected that mistake, however, the wrong total is still present in the “Task” tab of the Power BI report.

It would be great if you can replace that picture with the correct one (with the correct total) to avoid any confusion for future participants.

Thanks again for this amazing problem and definitely I learned a lot from this one.

But, I’m a little paranoid and now I am thinking about all those numerous reports that I’ve sent to the clients being unaware of the issue…

Luckily, not yet got any complaints as of now and hoping the same for the future…

[quantumudit]

Hey @JarrettM, I’m talking about the incorrect total that is still present in the “Task” tab of the Power BI file that I’ve downloaded. Here is the snapshot of the same:

Kindly replace this picture with the correct one to avoid any confusion in the future because most of the time we tally the numbers shown in the “Task” tab itself.

Thanks,
Udit

[JarrettM]

@quantumudit,

Thanks for pointing that out. I know we fixed the picture in the first post of this thread, but must have forgotten to fix the PBIX file.

Thanks
Jarrett

[JBocher]

Hi all,

Here is my solution to the problem :

Enterprise DNA Problem of the Week #6 - JBocher.pbix (1.6 MB)