Forum Discussion
Power Query - Csv.Document - Select specific columns from csv file
Typically, when loading a CSV, it looks like this before promoting headers:
Or like this after promoting headers:
If you use the Columns argument in Csv.Document, it interprets a list of column names not as which columns to select but what the column names are (useful if you don't have headers in the file) and assumes you are listing them in order from left to right.
Columns: Can be null, the number of columns, a list of column names, or a table type. If the number of columns is lower than the number found in the input, the additional columns will be ignored. If the number of columns is higher than the number found in the input, the additional columns will be null. When not specified, the number of columns will be determined by what is found in the input.I'd recommend not using this argument but rather selecting the columns you want after promoting headers. You can use the Choose Columns button for this and the query should end up looking like this:
let Source = Csv.Document(File.Contents("C:\Users\aolson\Downloads\Sample.csv")), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers", {"CO_NCM", "CO_UNID", "CO_PPI"}) in #"Removed Other Columns"Use whatever list of column names you prefer instead of the three I have in my example above.
I am sorry rohit_singh, I wasn't clear in my question. I have a dataset with 20+ columns and I want to import (let's say) 12 columns total . These specific columns that I want to import aren't the first 12 columns (e.g., I want to retrieve the first 10 columns and the last two columns). However, whenever I use the Columns argument in the Csv.Document function, I always get the first 12 columns, regardless if I use the specific names from the columns in the original csv.
Having said that, I believe I have misinterpreted the Columns' argument logic. I thought it could select the columns to import, but I think it only renames the columns based on the number of itens in the list of names (as in my example).
Below, there are the screenshots (I edited to avoid from exporting personal information). In the power query, I wanted to get the "CO_NCM" and "CO_SH6" columns (which are the first and third columns in the original csv file). However, the function exports the first two columns.
Hi mgartell ,
I think something is not right here. You have set the column parameters as "CO_NCM" and "CO_SH6".
Power Query correctly pulls in the columns with exactly those names.
Looking at I believe that there is a mismatch between the header names in excel and what's being pulled into power query. Could you check the delimiter you're using to import the file?
Kind regards,
Rohit
Hi, rohit_singh. I thank you for the fast and helpful answers. I believe I am using the correct delimiter (which is semicolon), as when I try another one (such as comma) it entirely messes up the data. I have tried this on another dataset and the result is the same: it always imports the first N columns, regardless of the columns names I've listed. You can see in the Excel Screenshot that the values of the column "CO_SH6" are not the ones that are imported in the Power Query. I don't know how to proceed with this.
Typically, when loading a CSV, it looks like this before promoting headers:
Or like this after promoting headers:
If you use the Columns argument in Csv.Document, it interprets a list of column names not as which columns to select but what the column names are (useful if you don't have headers in the file) and assumes you are listing them in order from left to right.
Columns: Can be null, the number of columns, a list of column names, or a table type. If the number of columns is lower than the number found in the input, the additional columns will be ignored. If the number of columns is higher than the number found in the input, the additional columns will be null. When not specified, the number of columns will be determined by what is found in the input.I'd recommend not using this argument but rather selecting the columns you want after promoting headers. You can use the Choose Columns button for this and the query should end up looking like this:
let Source = Csv.Document(File.Contents("C:\Users\aolson\Downloads\Sample.csv")), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers", {"CO_NCM", "CO_UNID", "CO_PPI"}) in #"Removed Other Columns"Use whatever list of column names you prefer instead of the three I have in my example above.