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!
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!
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).
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!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.