CSV web query returns one huge row

[Anonymous]

Hi all

I'm sure this must be an easy one but I can't find a solution!

I have a web query that brings data in csv format into Power BI. The data all downloads but it only gives me one huge row with a bajillion columns.

Taking a closer look, I can see columns that contain something like this:

NaN;20170221

So this looks to me like the ';' should indicate a new row, and the first column is a date. This repeats over and over agian.

So how can I tell Power Bi to start a new row when it finds a ';'?

Thanks in advance!

[MarcelBeug]

If your query code uses the Csv.Document function, then you can try ";" as the third argument (which is the delimiter).

If no luck, then please share your query code.

[Anonymous]

Hi Marcel. Thanks for helping!

I'm a complete noob to this so I'd like to apologise if I'm getting obvious things wrong 

This is my query:

= Csv.Document(Web.Contents("http://pvoutput.org/service/r2/getstatus.jsp" & "?d=yyyymmdd" & "&h=1" & "&sid=49100" & "&key=<MyKeyGoesHere>"),[Delimiter=",", Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None])

You'll see that there is already a Delimiter argument. That splits the data into columns as you'd expect.

So if I was to add in ";" as the 3rd argument, then I'd put it between "Encoding=1252" and "QuoteStyle" right? But how to put it in? Just Encoding=1252, ";", QuoteStyle=QuoteStyle.None

[MarcelBeug]

Hi Ross,

Trying, but also learning from this one...

I see you don't have a third argument, but instead you have a record as 2nd argument (the part between and inluding the square brackets [...] ), which is perfectly fine.

So my suggestion then would be to try and adjust the part Delimiter = "," to Delimiter = ";"

= Csv.Document(Web.Contents("http://pvoutput.org/service/r2/getstatus.jsp" & "?d=yyyymmdd" & "&h=1" & "&sid=49100" & "&key=<MyKeyGoesHere>"),[Delimiter=";", Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None])

If that doesn't help, you can also provide a list of delimiters, like:  Delimiter={",",";"}

= Csv.Document(Web.Contents("http://pvoutput.org/service/r2/getstatus.jsp" & "?d=yyyymmdd" & "&h=1" & "&sid=49100" & "&key=<MyKeyGoesHere>"),[Delimiter={",",";"}, Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None])

Hope this helps, otherwise I would be running out of suggestions.

[Anonymous]

Ok so suggestion 1, changing the delimiter to ";" still gave me  1 row with a bajillion columns. But each column contained lots of data with commas in it. So it literally split it into columns separated at the ';'...

Suggestion 2 was this:

[Delimiter={",",";"}, Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None]

This produced an error 😞

[Anonymous]

This is how my query is set up in Power BI by default.

[Anonymous · Accepted solution]

I fixed it!

My steps were:

1) Change the source to just be a text file instead of a csv. This resulted in one column with one row. So one cell containing ALL my data.

2) Split the single cell based on the delimiter ";". This resulted in heaps of columns containing a bunch of information including commas

3) Transpose the data so that columns became rows. So a single column with a bunch of rows

4) Split the column again on the delimiter "," and hey presto! Done!

This forum post set me in the right direction.

https://community.powerbi.com/t5/Desktop/Split-column-not-into-seperate-columns-but-in-rows/td-p/111...

[v-huizhn-msft]

Hi @Anonymous,

Glad to hear you have resolved your issue, please mark right reply as answer, so other people can find solution easily.

Thanks,
Angelia

[MarcelBeug]

Glad to hear it works for you!

Sorry that I couldn't be of further assistance.