Cleaning Data in Power Query

[AzSunBird]

Account Description Memo Amount
Auto -1,206.02
Auto:AAA -191.00
1/23/2022 12:00:00 AM AAA AAA membership -191.00
Auto:Fuel -916.02
1/11/2022 12:00:00 AM Costco Santa Fe $3.35 -29.12
1/28/2022 12:00:00 AM Fry's Gas Santa Fe $3.33 -34.58
2/03/2022 12:00:00 AM Costco Santa Fe $3.44 -22.13
2/19/2022 12:00:00 AM Costco Santa Fe $3.54 -39.62
3/03/2022 12:00:00 AM Fry's Gas Santa Fe $3.99 -25.21
Bills -6,900.29
Bills:Cable TV-Internet -497.67
9/02/2022 12:00:00 AM T-Mobile Cell Phone/Internet -140.00
10/1/2022 12:00:00 AM T-Mobile Cell Phone/Internet -135.49

Power Query
===========
The above is as example of the Excel data I am trying to clean in Power Query.
I have also attached a PDF file which shows the example data much better.
This is what I am trying to do:
1. Delete rows where the Account column does not contain data with a colon ":"
2. In the Account column the Account name needs to be moved to a new column
so that the account name and the transaction date are not in the same column.
3. Copy down the Account names in the new column

This is all I am trying to do but I cannot figure out how.

There are 4 columns: Account Description Memo Amount
and 823 rows. Each row represents a different transaction for that account.
Each account will have varying number of entries and the number of accounts can
vary from month to month.

If I can get this working I am hoping I just do a Refresh for each succeeding month.
Hopefully you can give me some sense of direction on how I should proceed.

Thank you.

 

[AlanSidman]

Is your source document a PDF or an excel file. If excel, then upload that so we don't have to try and recreate your data.

 

[AzSunBird]

Is your source document a PDF or an excel file. If excel, then upload that so we don't have to try and recreate your data.

The source document is an Excel file. I will attach one. Thanks.

 

[AzSunBird]

I have uploaded an example file with a small subset of the original 800 plus rows. It is in the original post. Please let me know if it is sufficient. Thanks.

 

[AlanSidman]

If I am understanding correctly then,

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Accounts", each if Text.Contains([Date],":") then [Date] else null),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Accounts", null}}),
#"Filled Down" = Table.FillDown(#"Replaced Errors",{"Accounts"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Account] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Accounts", "Date", "Account", "Description", "Memo", "Amount"})
in
#"Reordered Columns"

 

[AzSunBird]

If I am understanding correctly then,

Thank you, thank you Alan! Your interpretation of my problem and your solution is perfect. Thank you for attaching the solution file. I will now study your solution as it will give me a greater understanding of the power of Power Query. I will learn how to approach, apply and modify this approach in future Power Query tasks.
You are a real professional and my hero.
Thank you!
AzSunBird

 

[AlanSidman]

Thanks for the feedback. If you wish to learn more about PQ, then I urge you to purchase Ken Puls and Miguel Escovar's book on PQ. It is listed on Amazon.

Amazon.com