Patternless Data Need to Append or Combine

[Syndicate_Admin]

I am working on PDF bank statements. I've been importing each statement into Excel via Data ->Import PDF. From there, I transform the data. I'm working with tables only (not pages). At times, I've successfully gotten the close-to-final table down to three columns (Date, Description, and Amount). I've learned how to clean the data, which has helped remove spaces. Did I mention that I'm new to Power Query and Power BI? 🙂

My problem: Some of the Description column's data splits into multiple rows. These "additional" rows have "null" in their respective columns for Date and Amount. I believe this is great! I don't know how to append each row (in order) to its main row.

This picture shows what it looks like before I copy and paste the split cells into their appropriate row. It's as good as I can get it without manually doing a cut-and-paste to append the description.

This picture is an example of some transactions that are playing nicely. They are only taking one row. They play nicely but also aid in making the data (in my opinion) patternless.

This picture shows how I need it to look inside Excel. I manually move the data in Excel via cut and paste to get here. However, there has to be a faster way. 

What have I tried? 

Group By 

Grouping is occurring for no amount. The unique values I have are the Amounts. However, because some rows don't have Amounts, it seems the query thinks the blank cells (in the Amount column) are a value. 

Additionally, I can't group by Date because some transactions share the same Date. 

Indexing

I'm unsure how to index when some transactions only have one row of information. The Description column is inconsistent. 

Text.Combine

The table has inconsistencies, so this has proven unsuccessful. 

Copy and Paste Values

If I Merge cells (getting out of Power BI), Excel only merges the top cell. I lose the remaining data.

I've done more, but it's too much to list here. A solution is greatly appreciated. Thank you in advance.

[Syndicate_Admin]

NewStep= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName)&{[Date=""]},{},(x,y)=>if x={} then {{},y} else if y[Date]=null then {x{0},x{1}&[DESCRIPTION=Text.Combine({x{1}[DESCRIPTION],y[DESCRIPTION]}," ")]} else {x{0}&{x{1}},y}){0})

[Syndicate_Admin]

Thanks. Unfortunately, it's not working for me.

Modified to enter the last step "RenamedColumns"

Table.FromRecords(List.Accumulate(Table.ToRecords(RenamedColumns)&{[Date=""]},{},(x,y)=>if x={} then {{},y} else if y[Date]=null then {x{0},x{1}&[DESCRIPTION=Text.Combine({x{1}[DESCRIPTION],y[DESCRIPTION]}," ")]} else {x{0}&{x{1}},y}){0})

What I've Done and Didn't Work for Me (Not an any order)

• Advanced Editor: Error, "An error occurred in the ‘’ query. Expression.Error: The name 'RenamedColumns' wasn't recognized. Make sure it's spelled correctly."
• New Column
• Created a New Table

What is currently in Advanced Editor for this table (By the time you reply, I will have moved on to another statement so these steps may be in a different order for the next statement.):

let
Source = Pdf.Tables(File.Contents("X:\FileLocation\Statement_052020_1234.pdf"), [Implementation="1.3"]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
#"Removed Top Rows" = Table.Skip(#"Filtered Rows",12),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1),
#"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Id", "Name", "Kind"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
#"Removed Top Rows1" = Table.Skip(#"Expanded Data",2),
#"Removed Bottom Rows1" = Table.RemoveLastN(#"Removed Top Rows1",13),
#"Cleaned Text" = Table.TransformColumns(#"Removed Bottom Rows1",{{"Data.Column2", Text.Clean, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"Data.Column1", type date}}),
RenamedColumns = Table.RenameColumns(#"Changed Type",{{"Data.Column1", "DATE"}, {"Data.Column2", "DESCRIPTION"}, {"Data.Column3", "AMOUNT"}})
in
RenamedColumns

I'm new to Power Query and Power BI it is probable I'm applying what you've provided in the wrong location or incorrectly. Are you willing to advise where I'm messing up and how to apply what you've provided please?

Thank you again for your help.