power query Shifting row of cells to the right

Question

I've imported a pdf file into power query. On a few of rows, the data has shifted 1 cell to the right and one those rows they are now not in the correct column. I can fix it in Excel, but is there a way to fix it in power query?

Answer 1

I ran into a similar challenge where the header row was displaced by one column when the exporting PDF content output to CSV.

I used the following strategy in Power Query:

1. Convert the affected row/record to a list.
2. Split it and reassemble it, i.e., sort it in the correct order.
3. Convert it back to a record and insert it back into the table.
4. Remove the old record

It looks something like this:

let
  Source = Excel.CurrentWorkbook(){[Name="tblShiftLeft"]}[Content], // Fetches the table/range containing the row data that needs to be shifted.
  #"Shifted Headers" = List.Combine({List.RemoveFirstN(Record.ToList(Source{0}),1), {""}}), // converts to list, splits, and reassembles
  #"New Headers" = Record.FromList(#"Shifted Headers",Table.ColumnNames(Source)), // Creates a record from the list using the column names
  #"New Headers Added" = Table.InsertRows(Source,1,{#"New Headers"}), // Insert the newly poisitioned header row.
  #"Headers Adjusted" = Table.RemoveRows(#"New Headers Added",0) // Remove the old row
in
  #"Headers Adjusted"

Hope this helps,