all columns from CSV file with Power Query

Always import all columns from CSV file with Power Query

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.

Power Query applied steps

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.

count of CSV columns parameter Power Query

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.

delete CSV column count parameter Power Query

 

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.

CSV import steps Power Query

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.

2 comments on “Always import all columns from CSV file with Power Query

  1. Doing this left Powerquery to only start with one column for me. That column had random data in it as well.

  2. cesar tozzi

    Thanks for the content, the number of columns on my csv file varies a lot and I was tired of fixing it in every refresh!

Leave a Reply

Your email address will not be published.