Forum Discussion
Retrieving data from CSV, not showing the correct columns
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!
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