Issue when importing CSV file

[DenHaa]

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.

[Anonymous]

Did you get a fix for this? im getting the same issue.

[danextian]

Hi  @Anonymous , 

You just have to change the 26 or remove it entirely. It is an optional parameter.

Columns=26,

[DenHaa]

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.

[negi007]

@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)

[DenHaa]

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?

[negi007]

@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"

[DenHaa]

The only change I could see was the Encoding, correct?

When I changed the Encoding to 1252, all of the 26 columns show up when I refresh but they still give an error:

And if I try to change the type of one of the fields, I get the following error:

Here is my current query:

let
    Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Assigned Date", type datetime}})
in
    #"Changed Type"

[negi007]

@DenHaa try to remove the changed type step. in this step some column names are passed for processing. 

[DenHaa]

I have tried removing that as well so my query is simply:

let
    Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

When I use this, the following error is shown:

As I mentioned in my original post, it works if I do the same process in Excel and the query looks like this:

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"

[themistoklis]

@DenHaa 

Try removing this statement  , Columns=26  and load it again