Debtors Query

[kboakye129]

Hello
I have been trying to use excel power query to split the attached notepad into columns where I can see total account balance, account number and amount in a separate columns. I am not interested in any other data except these columns.The problem I having is , total account balance description and accounts numbers are in the middle of data and I having difficulty spliting them

 

[Razmicka]

Hi
Is this what you want ? its about 15 minutes of shifting and sorting data manually

 

[kboakye129]

Hi Razmicka
thank you for getting the job done. could you send the steps you used to clean this notepad? If you say sorting data manually, does it mean you did not use data query?

 

[Razmicka]

Hi kboakye

No I didnt use data query.
The easiest way is to copy notepad content to a clean sheet by copy and paste
then sort column A, and manually search for all rows that say "Total for account xx", which should all be bunched up together
Copy the bunch to another sheet, apply text to column (Fixed width) by manually adjusting where the columns should be and Finish,
All Done

 

[Pecoflyer]

Something like this? There might be a slight problem as I am using European rationale. I'm just a PQ beginner so this can surely be improved on

let
    Source = Csv.Document(File.Contents("C:\Users\ADP\kDrive\DownloadW10\aged query\Aged Debtors Analysisv1.txt"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.Contains([Column1], "Total")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Column1], "Customer")),
    #"Cleaned Text" = Table.TransformColumns(#"Filtered Rows1",{{"Column1", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1", Text.Trim, type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Trimmed Text", "Column1", Splitter.SplitTextByPositions({0, 50}, false), {"Column1.1", "Column1.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Position",{"Column1.2"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Removed Columns1", "Column1.1", Splitter.SplitTextByPositions({0, 30}, false), {"Column1.1.1", "Column1.1.2"}),
    #"Trimmed Text1" = Table.TransformColumns(#"Split Column by Position1",{{"Column1.1.1", Text.Trim, type text}, {"Column1.1.2", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text1",{{"Column1.1.1", "Account numbers"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",".",",",Replacer.ReplaceText,{"Column1.1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1.1.2", type number}})
in
    #"Changed Type"