Forum Discussion
CSV web query returns one huge row
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!
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.
8 Replies
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.
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 :smileyhappy:
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
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. :smileyembarrassed:
