Forum Discussion
Change from xlsx to csv
JGG ,
I believe this is correct as the difference in connection strings would be found within the 'Transform Sample File from [your folder name]' query, as well as a different sample file being used for the 'Sample File' query, each in the new function group (automatically generated folder of queries).
Additionally, Windows tends to actually store .csv files as Excel csv, so may appear to Power BI as an Excel file when importing. You can see this from the icons that your .csv files display when viewing them in File Explorer - they will either show as an Excel icon or a text file icon.
Further to the above, if your .csv files are Excel csv (maybe even if they're not), you may need to select your 'Filtered hidden files' step in your output query and manually filter this table to only include files with [Extension] .csv to prevent all of your .xlsx and .csv files being bundled together during the transform function operation.
Your 'Filtered hidden files' step table should look something like this:
This is where you may need to filter rows to only include specific files in the folder for processing.
Pete
Hi JGG ,
I'm assuming you're comfortable using the Advanced Editor in Power Query here.
I think the quickest/easiest way to do this would be to connect to the .csv folder and perform the Load/Transform steps you want as you did previously for the .xlsx folder. This will create a new transform function group for the .csv folder in Power Query.
Create a duplicate of you original .xlsx query as a safety copy here as we will mess with the code next.
Then take the code generated in the Advanced Editor for your new .csv query and use it to replace the same code lines in your existing .xlsx query without replacing the subsequent transformations you've done after the initial data load/transform. This should work fine as all your fields are the same in the .csv files as they are in the .xlsx files.
Once you are happy that your full query is running as expected, you can delete the original .xlsx function group from Power Query.
Hope this makes sense.
Pete
Thank you, this make sense. However when I compare the advanced editor for xslx and csv they seems to be identical except for the reference to 21 (the number of the new query). See attached. So may be something I misunderstand here.
JG
JGG ,
I believe this is correct as the difference in connection strings would be found within the 'Transform Sample File from [your folder name]' query, as well as a different sample file being used for the 'Sample File' query, each in the new function group (automatically generated folder of queries).
Additionally, Windows tends to actually store .csv files as Excel csv, so may appear to Power BI as an Excel file when importing. You can see this from the icons that your .csv files display when viewing them in File Explorer - they will either show as an Excel icon or a text file icon.
Further to the above, if your .csv files are Excel csv (maybe even if they're not), you may need to select your 'Filtered hidden files' step in your output query and manually filter this table to only include files with [Extension] .csv to prevent all of your .xlsx and .csv files being bundled together during the transform function operation.
Your 'Filtered hidden files' step table should look something like this:
This is where you may need to filter rows to only include specific files in the folder for processing.
Pete