Retrieving data from CSV, not showing the correct columns

[B-FHY]

So I am connecting a CSV in a SharePoint to PowerBI. I use, Get Data - SharePoint Folder and then link my Sharepoint. Because there are so many excel and CSV files in my Sharepoint, my CSV doesn't initially show up, I click transform data, filter to .CSV and then filter to the name of the correct CSV I want to use. To show the data I then click combine files, this will load the below which has a column that doesn't exist in my CSV (the last column in the below picture) and is missing two columns that exist (should be the last two columns).

I then refresh this and the incorrect column disappears and the correct columns show (see below image)

I click ok and get a table that needs refreshing but is missing the last two columns and has two columns that don't exist in the CSV ("Word Order Closed Status Modified Dater" and a blank column) (See below).

So i refresh the data and get the below error.

The only way I can get the actual table up I need is to change the code in this screen for the 'Changed Type' step and remove the code for the two non-existent columns, if I do this and refresh it will load my whole table with the correct columns and rows, however, once I apply I get the same error message as the above.

If anyone has had this issue before or can provide some help it would be appreciated. Thanks,

[Cristian_Angyal]

Hey @B-FHY ,

try using this code and just replace YOUR_SHAREPOINT_SITE and YOUR_FILENAME. 
As long as you only need data from a SINGLE CSV there is no point "combining" it!

// This query retrieves a CSV file named "Data-Products.csv" from a SharePoint site, 
// extracts its content, converts it into a table, and promotes the first row as headers.

let

  // Connect to SharePoint and retrieve all files
  Source = SharePoint.Files(
    "YOUR_SHAREPOINT_SITE",
    [ApiVersion = 15]
  ),
  // Filter to select only the "Data-Products.csv" file
  FILTER_DataProductsCSV = Table.SelectRows(Source, each [Name] = "YOUR_FILENAME.csv"),
  // Extract the content of the CSV file as a table
  AddCsvContentColumn = Table.AddColumn(
    FILTER_DataProductsCSV,
    "CsvContent",
    each Csv.Document([Content])
  ),
  // Retrieve the first row of the CsvContent column
  ExtractCsvTable = AddCsvContentColumn{0}[CsvContent],
  // Promote the first row to headers
  PromoteHeaders = Table.PromoteHeaders(ExtractCsvTable, [PromoteAllScalars = true])
in
  PromoteHeaders

If this answer was helpful, please consider accepting it as the solution to help the other members find it more quickly.

Kudos appreciated also 😉

Cheers,

Cristian Angyal
LinkedIn  |  X (Twitter) |   Romania Power BI User Group  |  YouTube

 

[v-karpurapud · Accepted solution]

Hi @B-FHY 

Thank you for reaching out to the Microsoft Fabric Community Forum. We understand you're experiencing an issue with retrieving data from a CSV when connecting to a SharePoint folder. Let's go through the troubleshooting steps:

When combining files, power query automatically selects a sample file (usually the first file in the SharePoint directory). If this sample file differs from your actual CSV, it may have different columns, causing discrepancies.

The "Changed Type" step applies column transformations based on the structure detected in the sample file. If the actual CSV has different columns, power query will throw a "Column Not Found" error because it tries to apply transformations to non-existent columns.

1. Check the Sample File

• In Power Query Editor, expand "Transform Sample File".
• Ensure Power Query is using the correct CSV file as the sample.

2. Fix the "Changed Type" Step

• In Query Settings, in the "Changed Type" step, remove references to missing columns (e.g., "Work Order Closed Status Modified Date").
• Re-add missing columns manually, if needed.

3. Ensure the CSV Updates Correctly

• If the CSV changes over time, replace "Changed Type" with promoted headers.
• Check if a "Removed Other Columns" step exists—edit it to keep all necessary columns.

4. Apply Changes & Refresh

• Apply changes and refresh the data.
• If the issue persists, delete and re-import the CSV using the correct sample file.

If my response has resolved your query, please mark it as the Accepted Solution to help others. Additionally, I would appreciate a 'Kudos' if you found my response helpful. 

Thank you!

[B-FHY]

When adding it I've set the sample file as the correct CSV, however, this is still not working. So far the only way I have managed it was by creating a blank report, with the correct sample file selected this still had the issue of not finding the correct columns. I then used the advanced editor to remove the two incorrect ones, add the two correct ones, and change 'Changed Type' to 'Promoted Headers', this then worked, however, I dont see why i need to do this for a brand new report? (I also tried these steps in the current report and still no luck).

[v-karpurapud]

Hello @B-FHY 

Power Query applies transformations based on the initially detected sample file, which can persist even when the correct one is selected later. This often leads to column mismatches that require manual adjustments.

• To avoid this in new reports, remove any automatically applied steps (such as Changed Type) before making modifications. Then, apply Promoted Headers first to ensure correct column mapping.
• This method ensures power query recognizes the correct columns without needing manual corrections each time.

If this resolves your issue, please mark it as the Accepted Solution to assist others with similar problems. Your kudos are appreciated!

Thank you!

[v-karpurapud]

Hello @B-FHY 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.