Removing similar but different rows to create functioning power query

[mackenzian64]

In my data I have monthly totals and grand totals. I am trying to make a generic power query that will eliminate all the unwanted data, regardless of the company name, months used etc, so I only get the working data.

In the attached sheet, I only want to keep the data for the rows beginning with the date, or the row below it, that has the job number (i.e. Q000535), but not the general report data (dates, company, page numbers, or totals. I can filter them out in the power query, but the dates change, the company name changes, only the general layout remains the same, so I need a more generic method of eliminating the selected rows.

Note, I have severely edited the file to work within the file size limits, so the numbers don't add, but the general layout remains available.

Any suggestions?

 

[AliGW]

You've provided a text file - please attach an Excel workbook.

 

[mackenzian64]

But that's my starting point, a text file. I can through it into Excel, but that seems silly as I can use Power Query to take a text file and convert it into a table for analysis.

 

[AliGW]

It's up to you. I don't see why it is so difficult for you to provide a workbook with the data already pulled into PQ for those willing to help you tweak your query in their free time and for no recompense, but if you can't be bothered, then neither can I. Sorry. Good luck!

 

[UK_GER]

Hi Ian,

i had a look onto your textfile.
There are lot of "spaces" in it. Think that's the reason why Ali asked ;-)
An export file with tabs would be easier, also for getting the different columns splitted.

If you get it always in exact this format, you should be able to split all by number of "digits".
I've used the digit counting for checking for the month.

This could off course become difficult, if sometimes the format changes and e.g.

General Ledger - Inquiry (Apr 01/17 to Mar 31/18) -> this Apr could become part of your data.
(Just that you know it)

I checked the month by the "short typ" e.g. Apr.
I'm not native speaker, so pls. check if all short typs are correct.

Code is:

let
Quelle = Table.FromColumns({Lines.FromBinary(File.Contents("C:\TESTDATA\GL with Job.txt"), null, null, 1252)}),
#"step_1: text_range" = Table.AddColumn(Quelle, "Text (22/3)", each Text.Range ([Column1],22,3)),
#"step_2: convert errors" = Table.ReplaceErrorValues(#"step_1: text_range", {{"Text (22/3)", "text to short"}}),
#"step_3: change type" = Table.TransformColumnTypes(#"step_2: convert errors",{{"Text (22/3)", type text}}),
#"step_4: check for month" = Table.AddColumn(#"step_3: change type", "check for Month", each if [#"Text (22/3)"] = "Jan" then "ok" else if [#"Text (22/3)"] = "Feb" then "ok" else if [#"Text (22/3)"] = "Mrz" then "ok" else if [#"Text (22/3)"] = "Apr" then "ok" else if [#"Text (22/3)"] = "May" then "" else if [#"Text (22/3)"] = "Jun" then "ok" else if [#"Text (22/3)"] = "Jul" then "ok" else if [#"Text (22/3)"] = "Aug" then "ok" else if [#"Text (22/3)"] = "Sep" then "ok" else if [#"Text (22/3)"] = "Oct" then "ok" else if [#"Text (22/3)"] = "Nov" then "ok" else if [#"Text (22/3)"] = "Dec" then "ok" else "no no" ),
#"step_5: filter it" = Table.SelectRows(#"step_4: check for month", each ([check for Month] = "ok"))
in
#"step_5: filter it"


Code is hard related to your format - just to say it again.

Have fun (all of us)

THX
Uwe

 

[mackenzian64]

Thanks. I had an epiphany and took a different approach to the file and was able to eliminate all the extraneous data and then extract only the information needed, starting with the text file. By eliminating data in the first column, then finding data that had Total in it in the right spot, I could eliminate the extra stuff and then move forward.

:typing: Thanks again for responding and helping.

 

[UK_GER]

Thanks for feedback.
Uwe