Hello . I am trying to create a cross tabulated report from the attached file. It s AP aging report that its in a very bad structure and i have tried many times to transform it with no luck. I need this report in an excel format so that i can analyze it in a pivot table. . Appreciate your help from this community. Thanks in advance ap340.txt (354.1 KB)
1 Like
Hi @jazzista1967,
Wow this wasnāt easyā¦ (or maybe Iām overthinking it 
)
Donāt know if itās 100% correct to be honest because Iām unfamiliar with this data and you didnāt provide a mockup with the desired result. But this should get you there Iām sure.
Fase 1.
Only retaining rows of intrest. InitialClean query
Fase 2.
Split InitialClean in Vendors (Result) and Details (RowClean)
Fase 3.
Clean, Split and Merge all detail rows.
Here Iāve used a pattern recognition logic to find the Date values in the strings multiple times
Fase 4.
Merged the RowClean Data back to the Result Query
If you change the FileLocation parameter all queries will be restored.
Hereās your sample file. eDNA - Accounts Payable cleanup.pbix (92.6 KB)
I hope this is helpful
P.S. you can copy all queries over to the Query Editor in Excel.
1 Like
Hi Melissa! How are you? Oh my godā¦ You are almost there. I am attaching a mockup of one vendor so you can see the pattern and how the report needs to be populated. . The report in total is off by USD 79,233.50 but it may be some lines that may have been filtered . How did you figure out even the first step? I have been sooo frustrated for not being able to clean anything. As you can see its an old accounting software that spits out this ugly report. I am trying to automate this report for my accounting AP closing. Thank you so much for your help so far.
mockup Ap 340 with one vendor.xlsx (45.9 KB)
Itās great to know that you are making progress with your query @jazzista1967. Please donāt forget if your question has been answered within the forum it is important to mark your thread as āsolvedā. Also, weāve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Luis, I have no idea what @Melissa did as I canāt open up to date pbix files.
Is 79,233.50 a lot?
At the top the data looked interesting so I made the mistake to try my luck. Yeah, it is indeed bad.
How on earth do you have 2 lines for ADA100? I thought in the original text file there is only one. Still I have 8.318.382,37 which sounds too much.
How much do you know that it is and how many lines do you have?
1 Like
You can see each of the transformations I did - when you step through the steps in the Applied Steps pane. However if you have any specific questions please let me know.
.
Okay I identified 3 remaining issues and solved 2 of them.
What Iāve solved were missing pieces to get the the sum for all individual ātotal rowsā to match the sum for all Result query rows.
What I havenāt solved is that the āInvoice Numberā column can include a Description but Iāve spend far too much time on this already to try and fix that. Maybe you can sort that yourself.
.
If you change the FileLocation parameter all queries will be restored.
Hereās the updated sample file. eDNA - Accounts Payable cleanup v2.pbix (115.0 KB)
I hope this is helpful
HI Melissa! I did a quick check on the aggregation column and now the report foots correctly. I also changed the location parameter and everything worked correctly. I will try to mess around with the description column to see if I can separate the invoice number from the description in column labeled Data.1. My godā¦ you should do a tutorial with this example . Very elegant solution. Thanks for your help. I will be studying your M code even thought this was very advance and its a bit over my head.
Hi Matthias. Thanks for your solution also. As you can see, the accounting software that we used is very old and the data is just awful. What i was trying to create is a cross tabulated report so that i can use a pivot table in order to analyze the due dates and the aging . Here is the TXT file. Melissa in her last post posted the complete file. Thanks for your help. Could you share your PBX file to see what you did? Thanks
@jazzista1967 Thanks for the feedback Louis! I did not find the txt file, but if you have txt or xlsx it could be helpful. Here is the main question: How much do you know that it is and how many lines do you have?
I am sure that the solution from @Melissa is great and elegant, but I canāt read current pbix. My solution was quite simple splitting the column at the first left occurance of several space characters. Originall a lot, then less and it seems probably still too many. Then combine the right side one down. And split and trim again and again. So I was quite frustrated and didnāt bother about any checks in the morning. 
But the right side looks good to me, that is why I am curious about the correct amount.
The left tells me that I had too many spaces. If I reduce the number of spaces I get to 8.442.851,22. Is that getting close?
Hi @jazzista1967,
Had a brain wave earlier today on how to also solve for combined description and invoice number, so gave it another go. Hereās an updated overview of the general process - step through the Applied Steps for details.
-
InitialClean, only retains rows of intrest both Vendor and LineItems
-
RowCleanSingleLine, clean up single line items
-
RowCleanMultLine, clean up multi line items
-
LineItems, combine (2) and (3)
-
Result, keep Vendor rows (1), get Vendor name and merge LineItems (4)
Added a validation query so you can see if sum line amounts are equal to the company total row
.
Hereās the sample file. eDNA - Accounts Payable cleanup v2.pbix (116.2 KB)
I hope this is helpful.
1 Like
Melissa: Good evening. Sorry for this late reply. Appreciate your help in this again. I am going to take a look at the PBIX file and follow the steps. Quick question: Is it possible to have ALL steps into one file inside the query editor? Thanks again in advance. Regards
Hi @jazzista1967,
The short answer: is possible yes, should you no.
Also it doesnāt matter how many queies you create, you can choose what to load to the worksheet.
1 Like
@jazzista1967 - Luis, when I started out it looked to me as a split by position task combined with an offset. I failed and tried by delimiter which normally is a save bet, but that did not work even though I reduced the number of spaces used as delimiter.
I looked at the download data in excel and understood that the offset assumption was wrong. And there was no chance for split by delimiter, because of these 3 lines:
2 lines from Hospital Service District No1 which do not have a minimum of a double space
1 line from Gator Valve, Inc. with byDate input
So back to split by position but taking the invoice block from the end.
I would not have expected the page headers disrupting invoice lines belonging together, but it does. That filtering step is manually defined, so I am not happy with it, but is seems to work and it should be stable.
I found the positions by a query counting the dashes below the field names, but the structure is stable so there is no need to define this dynamically. I just copied the positions over into the split step.
The result is a single query solution with not too many steps - definitely not optimal, because I started off, failed and adapted until all cases were covered. But Luis, I am content with it. I assume @Melissa has utilized more advanced techniques, but unfortunately I canāt open her pbix file on my machine.
6 of the steps are for getting the data down one row. That can be done shorter, but through the UI it is just convenient.
ap340.pbix (99.6 KB)
1 Like
Luis, if you like the solution reduce the offset to one step:
= Table.FromColumns(
Table.ToColumns(#"Inserted First Characters") & {{null} & List.RemoveLastN(#"Inserted First Characters"[Invoice],1)},
Table.ColumnNames(#"Inserted First Characters") & {"Invoice.1"} )
Nicely done @Matthias 
Like your approach! Itās totally different than the road I travelledā¦
Although I used split by position in my solution as well
1 Like