Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

Patternless Data Need to Append or Combine

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.

Screen Shot 2022-10-23 at 4.48.23 PM.jpg


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.

Screen Shot 2022-10-23 at 4.59.21 PM.jpg


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. 

Screen Shot 2022-10-23 at 4.49.04 PM.jpg


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. 



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



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.

Super User
Super User

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})

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.):


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"}})

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.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors