Forum Discussion
Issue when importing CSV file
Hello.
I am having an issue when I am importing a CSV-file through the Web query as it limits the amount of columns for some reason.
The first time I imported the CSV it had 16 columns, I have since then expanded the source to include 26 columns, but they will not appear when I refresh the query in PowerBI Desktop.
Currently the CSV-file contains 26 columns with barely 20 000 rows so it is by no means a large file.
If I manually change the column number from 16 to 26 and then refresh the data, it will show the data in the next 10 columns but they will still be registered as empty.
Before promoting headers:
With promoted headers:
The query is quite simple, basically open the Web link and then promote the headers, I have removed the URL for safety reasons 🙂
let
Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Columns=26, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
in
#"Promote Headers"
If I attempt the same import in Excel, it works without any problem and displays all 26 columns and shows no error messages.
10 Replies
DenHaa in this case remove the columns=26 from the code and also remove the steps which is changing the type of the columns.
refer to below articles for more help
Always import all columns from CSV file with Power Query (datacornering.com)
Thanks negi007 & themistoklis for your replies 🙂
I have tried this and then it will only load the first 16 columns.
My query now looks like this:
let Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"But the results is only 16 columns:
If I then manually enter 26 columns, it will fill them with data in the visual window but still class them as empty?
DenHaa your code is slighlty different. i have just imported the file and was able to get new columns using below code. Can you try using it.
let
Source = Csv.Document(Web.Contents("https://abc/xyz.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
I have tested this again today and the issue still remains.
When I try to load the CSV-file into PowerBI it shows the last 10 columns to be empty, and therefor they are not included by default.
If I try to load the CSV-file into Excel, it works without any issues and includes all 26 columns.
I have compared both of the queries and they seem identical to me:
PowerBI Query:
let Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"Excel Query:
let Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Columns=26, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"I have even tried copying the query from Excel into PowerBI, but it still fails in the same way, the last 10 columns are registered as empty and therefor not included.
Hi Anonymous ,
You just have to change the 26 or remove it entirely. It is an optional parameter.
Columns=26,
