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

[gharbi-abdelkader]

@JarrettM

successfully completed the challenge

Hi every one !

my solution

[jbressan]

Hi @JarrettM

I just share the PBIX file and a link to online version.

[Matthias]

First post - my solution matches the target screenshots and no DAX is used. Thanks for the task!

Summary

The solution is relatively straightforward with a simple filter for the relevant invoice lines and then unpivoting the original aging groups to get all amounts in one column.
Note: The file to the csv would need to be updated.

#6 (PQ) - Addressing Irregular Data Formats.pbix (42.1 KB)

[BrianJ]

@Matthias,

Welcome to the forum – great to have you here! Nice way to debut, with good solution in Problem of the Week.

Hope to see you as a regular on these problems.

– Brian

[BrianJ]

Solution post by Nancy, a nonmember participant:

Enterprise DNA PoW#6 .pbix (3.8 MB)

[Yacob]

Summary

2021-02-18T00:00:00Z

[chrish]

What crazed mind would ever have thought that was a useful way for an accounting package to present data! Before Power Query I don’t know what you’d be expected to do with it - but PQ had it licked into shape without too much difficulty:

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

Have a good weekend,

Chris

[BrianJ]

@chrish

What crazed mind would ever have thought that was a useful way for an accounting package to present data!

What’s even worse is that somebody else looked at that mess and said “hey, nice approach – I’m going to use that too”, since apparently QuickBooks uses a very similar export format…

– Brian

[BrianJ]

All,

Here’s my solution.

Summary

Problem of the Week #6 BrianJ Solution.pbix (61.1 KB)

While I definitely could/should have been more efficient in the second half “cleanup” phase, the main thing I wanted to demonstrate was the “Feldmann Shift”, done to get the Names column to align properly with the Record # column. Once I accomplished that, the rest of the exercise was very straightford - just some basic UI cleanup commands.

Usually, the Feldmann Shift is used to gain the prior or subsquent value in Power Query, but it also can be used to shift specific columns up or down by a specified # of rows to align the data, as I did here. This is the second time in a month this technique has proven critical (the first was the COVID dashboard in DC #11, where we used it to convert the Hopkins data from daily cumulative to daily marginal, making the rest of the analysis MUCH easier). Definitely a technique worth putting in your tool belt.

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/

Thanks to @JarrettM for a very cool and practical problem.

• Brian

[logicalbi]

Please find attached my solution…

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

[tweinzapfel]

Hi Everyone,

Attached is my submission.

File: POTW 6 - Final.pbix (3.8 MB)

Approach I took:

First, I realized that this can be done entirely without using the Advanced Editor. So the steps included:

1. Removing blank rows
2. For the column with names, I replaced the blank fields with “null”. This then allowed me to use the “Fill Down” function to fill in the applicable name.
3. The name column was then split so I could remove the number. I then rejoined the columns to have the full name in one column
4. There were several rows where the “Record #” was blank – due to the totals in the spreadsheet. I filtered these out.
5. To get the overall Balance, I just summed up the various aging category columns – these columns were then removed.
6. A custom column was then added to get the aging balance. I used the Duration.Days function to get this.
7. I then added a Conditional Column to get the various aging categories.
8. Finally, I did add an additional conditional column to create a “aging category” sort column. This just assigned the value “1” to “1 to 30”, “2” to “31 to 60”, etc. This allowed me to sort the waterfall chart.

[JarrettM]

@tweinzapfel,

Thanks for your entry, but have another look at your Balance total. The Balance total should be $685,616.33. Don’t worry though, there have been numerous entries with the same mistake. I will definitely cover what is causing this in my wrap up video.

Thanks
Jarrett

[alexbadiu]

Here is my solution for Problem of the week #6

Spoiler alert

It was a fun challenge that needed the use of the main data cleansing steps. It was very useful practice.
The main problem I had was getting to the right totals. I could not understand why the total was not correct and had to double check all the steps.

Thank you @JarrettM for preparing this week’s POTW

Best regards,
Alex

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

[Craig]

Devastated… I blasted through the problem in under 10 minutes only to discover that the balance was 1.05 out.

“C:\Users\craig\OneDrive\10.03.2016\Power BI 2020\eDNA Problem of the Week\Problem #6\Craig Enterprise DNA Problem of the Week #6.pbix”

[BrianJ]

Solution provided via email by Elvir Mujkić.

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

[Craig]

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

[Craig]

@BrianJ

Thanks Brian… I’m not too devastated to be honest… I thought there would have to be some catch to the Problem, judging by the time it took to complete… I’m still laughing at myself for the approach I took concerning Customer Name & Key, though… I split the Customer Name and Key almost immediately and filled the Customer Key down then got annoyed that I couldn’t do the same with Customer Name so I copied the query, deleted all of the columns except for Name and Key and then merged the two queries… truly bizarre… Anyhow, I await Jarret’s solution with baited breathe…

[BrianJ]

Solution provided via email by Matteo Arellano.

In attachment, the PowerBI solution and the Excel file with the data table with all power query steps listed.

Jarrett ARData.xlsx (49.8 KB) Enterprise DNA Problem of the Week #6 (3).pbix (3.8 MB)

[BrianJ]

Solution provided via email by Drew Weaver.

Enterprise DNA Problem of the Week #6 - SOLVED.pbix (3.2 MB)

[BrianJ]

@Craig,

It definitely wasn’t a “gotcha” put in there intentionally, but will be a great learning opportunity, which I know @JarrettM will cover extensively in his solution post and video.

• Brian