Here is how to manage that you always import all columns from the CSV file with Power Query. There is no difference in solution if you want to import data from the CSV file into Excel or Power BI by using Power Query. That might be very useful in situations when the CSV file is getting additional columns. By using a few little corrections in the Power Query steps, you can ensure that it always imports a maximum of available columns.
Here are typical steps that appear after importing CSV file content in Excel or Power BI.
Always import all columns from CSV file with Power Query
In the step “Imported CSV” is a definition of column count that is detected. If you have additional columns in your data source, it might look like Power Query is not loading all the columns from the CSV file.
If you want to ensure that Power Query always reads all columns from the CSV file, delete the column count parameter. The result will look like this.
Considerations
One of the automatic steps created by importing a CSV file with Power Query is changing column type. That is the selected one in the picture below.
When there are additional columns in the CSV file, that step are not causing problems that might crash all the reports. The only thing to keep in mind is that new columns are not in the step that is changing the data type. New columns will be with data-type text. That is the data type for all the columns before the step that changes data types.
If some of the columns are disappearing and mentioned in the step that changes data types, then there will be an error. If that kind of scenario is plausible, consider deleting this step and live without it.
Take a look at other useful posts in this blog about Power Query or Power BI.
Leave a Reply