Forum Discussion
Patternless Data Need to Append or Combine
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.):
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.