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

[JarrettM]

**OK, here we go! Problem of the Week #6 is now live! The YouTube video introducing the problem can be found here.

For background on this initiative, check out this post 9.

Background

This is real receivable data from a client of mine from back on 1/21/2021. The client uses Sage Accounting, and I’m unable to connect to the data through Sage, so the client sends me a Detailed AR Aging Report that comes back with a crazy format. I’ll include the PBIX file and the CSV file at the bottom of this post. The dataset was larger than the 3K rows that are allowed to just copy and paste the data, that is why there will also be a CSV file along with the PBIX. Full instructions are on the YouTube video Brian & I did for this Problem of the Week.

The Model

The model only contains one table.

Your Task

Your task is to take the data from the CSV file and create a Table & Waterfall Chart like the picture above. I’m not to worried about the naming of the columns, but make sure that each row has a “Customer Name” included with each “Invoice#”. The “Age” & “Aging Category” are custom columns that will need to be added after you have the data sorted out. For the “Age” column calculation, please ensure that you are going off the following Date to do this calculation 1/21/2021.

Important
We ask that if you post anything directly related to a solution to please use the “Hide Details” or “Blur Spoiler” options within your post. This will prevent people who have not yet completed their solution from seeing things they might not want to see yet and could influence their solution.

To use these options, just select the text or image you want to hide, click on the gear icon on the top right at the top of your post and select either Hide Details or Blur Spoiler.

Note that this is a Power Query only challenge, so no DAX even if that is what you would choose to use outside of this challenge.

Eligibility for the Drawing
To encourage participation, we will be doing a drawing of five random participants each round to receive one of these cool Enterprise DNA vinyl laptop stickers:

To be eligible for the drawing, just send an email to problemoftheweek@enterprisedna.co before 11:59pm ET, Wednesday, February 24, 2021 indicating you’ve successfully completed the challenge, and please provide your mailing address for us to send the sticker if you’re selected.

We hope you find this initiative fun, challenging and helpful in continuing to build your Power BI skills. All of the information you will need is contained in the files within this post. If you have any questions or comments, just message @BrianJ or @JarrettM in this forum thread.

Good luck, and enjoy!!!

P.S. Your feedback is always welcome, and if you have a real-world problem that you think might make a good future Problem of the Week, please email it to us at problemoftheweek@enterprisedna.co.
Thanks!

Here are the files you will need:

Enterprise DNA Problem of the Week #6 .pbix (1.6 MB)
Jarrett ARData.csv (28.6 KB)

[Harsh]

Hello @JarrettM,

Here’s the solution -

Ooopppss…Forgot to add the chart in the solution. Attaching the revised screenshot as well as PBIX file.

Made the adjustment of $ 1.05/-

Thanks and Warm Regards,
Harsh

EDNA POTW No. 6 - Solution.pbix (78.7 KB)

[BrianJ]

@Harsh,

Great to have you aboard for Problem of the Week. Creative solution – quite different than the approach I took. I suspect we will see a lot of very different strategies on this one, which makes for a very interesting problem and discussion.

• Brian

[uriah1977]

Hi @JarrettM,

Here is my solution. Thanks for setting this up.

Daniel

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

[JarrettM]

@uriah1977,

Have a look again at your solution. You have the wrong “Balance” totals. That Number should be $685,616.33. First # I had entered was wrong.

Thanks
Jarrett

[uriah1977]

Hi @JarrettM,

Thanks! I will check it.

Daniel

[Melissa]

Hi @JarrettM,

Thanks for hosting this POTW!
You might want to check that figure again…

My Solution

I thought I was going slightly mad cause my numbers didn’t add up to the expected result, but I now know what the problem is… These values passed as text have decimals, now look them up if you’re 1.05 shy of $ 685,616.33

, Record# , Invoice# , Due Date , Current , 1 - 30 , 31 - 60 , 61 - 90 , 91-120+	",	Decimals
,24876, 200391001-5 , 01/14/2021 ,“2,358.18”,	TRUE	0,18
,24964, 200692001-2 , 01/17/2021 ,“20,818.92”,	TRUE	0,92
,24922, 200878001-1 , 01/16/2021 ,“17,085.82”,	TRUE	0,82
,24923, 200878001-2 , 01/16/2021 ,“3,375.82”,	TRUE	0,82
,24873, 200914001-2 , 01/14/2021 ,“5,192.06”,	TRUE	0,06
,24943,200976004, 01/17/2021 ,“7,130.20”,	TRUE	0,2
,24878,201042001, 01/14/2021 ,“2,000.00”,	TRUE	0
,24966, 201081001-2 , 01/17/2021 ,“1,383.49”,	TRUE	0,49
,24875, 201154001-2 , 01/14/2021 ,“4,827.00”,	TRUE	0
,24963,201169015, 01/17/2021 ,“2,247.11”,	TRUE	0,11
,24933,201180004, 01/17/2021 ,“5,825.46”,	TRUE	0,46
,24948,201202006, 01/17/2021 ,“4,741.01”,	TRUE	0,01
,24884, 201208001-1 , 01/15/2021 ,“1,985.94”,	TRUE	0,94
,24921,201238001, 01/16/2021 ,“1,692.34”,	TRUE	0,34
,24969,201256002, 01/17/2021 ,“1,571.34”,	TRUE	0,34
,24942,201292014, 01/17/2021 ,“2,539.27”,	TRUE	0,27
,24937,201318002, 01/17/2021 ,“3,654.12”,	TRUE	0,12
,24936,201336006, 01/17/2021 ,“19,077.58”,	TRUE	0,58
,24935,201339005, 01/17/2021 ,“6,438.96”,	TRUE	0,96
,24934,201339006, 01/17/2021 ,“4,096.58”,	TRUE	0,58
,24938,201353002, 01/17/2021 ,“1,214.64”,	TRUE	0,64

.

Here’s my query result. Nothing special just nested some M functions - that’s it basically.

I want to state for the record that I NEVER create implicit measures (by dragging a numeric field into a visual) but since we’re not allowed to use DAX this POTW there’s no way around it

Here’s my result.

eDNA POTW 6 Melissa.pbix (3.8 MB)

[adam]

Hi @JarrettM

Running through the Data Clean at the moment I noticed the Data has Duplicate Values. Are we to take the values that are Currently Assigned to an invoice number and filter out the one that are not?

[JarrettM]

@adam,

You are on the right track. Only numbers that are associated with an Invoice# are supposed to be kept.

Thanks
Jarrett

[adam]

Cheers Jarrett

[JarrettM]

@Melissa,

Your # was correct. I entered the number wrong in the previous post!

Thanks
Jarrett

[Rajesh]

Hi @JarrettM

Here is my solution.

Summary

POW #6 - Rajesh.pbix (3.8 MB)

[BrianJ]

While Jarrett is taking the bullet for me, the incorrect screenshot earlier was mine. Numbers are all corrected now.

My bad - sorry for the confusion. Carry on…

[JarrettM]

@BrianJ,

My current status!

[Melissa]

I like your moves…

[BrianJ]

@JarrettM,

Thanks - you know I’d do the same for you, buddy.

• Brian

[Harsh]

Hello @BrianJ and @JarrettM,

Made the adjustment in the screesnot and solution file and upload again.

Thanks and Warm Regards,
Harsh

[adam]

G’Day @JarrettM,

My first Problem Of the Week Complete!

[spoiler]

Cheers Adam

[jbressan]

Hi @JarrettM this is my solution. Half is Power Query and another half is DAX. The transformation of the flat file was made with Power Query and the Pareto Analysis I made it with DAX (sorry)

Online Version

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

[JarrettM]

@jbressan,

Please include your PBIX file when you get a chance.

Thanks
Jarrett