from pdf to excel without losing its format or data changing positions

Hi,

Follow these steps to clean and structure your data properly:

1. Import the PDF Correctly
   Open Excel 365 and go to Data → Get Data → From File → From PDF.
   Select the PDF file and let Power Query extract the tables.
   Choose the correct table (check the preview to ensure it captures the data properly).
   Click Load To... and select Power Query Editor instead of loading directly into Excel.
2. Fix Column Shifting Issue in Power Query
   Identify if the Date or Tour columns have missing values, which might be causing misalignment.
   Ensure that the first row is properly set as headers:
   Go to Transform → Use First Row as Headers.
   Manually rename columns if necessary (Home → Transform → Rename Columns).
   Reorder columns if needed by dragging them into place in Power Query.
3. Fill Down Dates and Tour Codes in Power Query
   Since Dates and Tour Codes only appear once per section in the original PDF, they need to be repeated for every row.
   Select the Date column → Click Transform → Fill Down.
   Select the Tour column → Click Transform → Fill Down.
4. Ensure Data is in Table Format
   Click "Close & Load" to bring the cleaned data back into Excel.
   If not automatically formatted as a table, select the data → Press Ctrl + T to convert it into a table.
5. Tips for Formatting
   If numbers appear as text:

Select the column → Transform → Detect Data Type → Set to "Whole Number" or "Decimal Number".
If blank rows appear:
Use Remove Blank Rows under the Home tab in Power Query.

This should maintain your data’s structure and allow easy analysis in Excel. Let me know if you need further clarification! 😊