PQ not loading final column of xlsx

[Anonymous]

Hi,

We have an xlsx extract from Business Objects hosted on a sharepoint site and upon trying to load the data the final column is missing.

Connecting via a web connection, the advanced editor the m is as follows:

let
Source = Excel.Workbook(Web.Contents("~~~Data.xlsx"), null, true),
#"Employee List_Sheet" = Source{[Item="Employee List",Kind="Sheet"]}[Data]
in
#"Employee List_Sheet"

The only way to resolve it seems to be to go in, add and then remove a new column before saving. After this process the column appears within Power Query. Any ideas as to what could be going wrong here?

[Anonymous]

Hi @Anonymous 

Before importing your xlsx file into Power Query, make sure that your data is formatted as a table in Excel. This helps Power Query to correctly identify and import all columns. Here's how you can do it:

• Open your workbook in Excel.
• Select the range of cells that contain your data. Ensure the first row contains your column headers.
• Press Ctrl + T to create a table.
• In the dialog that appears, make sure the checkbox for "My table has headers" is selected, then click OK.
• Save your workbook.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sergii24]

Hi @Anonymous, it would be much easier to help you if you can share a sample of Excel file for testing purspose 🙂

[Anonymous]

Hi Sergii24, Unfortunately I am unable to share a this file due to the sensitive data within.

[Sergii24]

You can prepare a sample file with dummy data 😉
Prepare Excel, create new pbix, test that you have the issue, share Excel and pbix. I'm just saying that I'd be happy to help but I'd need your collaboraiton as well.

Let's hope that someone from the community could give you a valuable answer without these details that I'm asking about!