Forum Discussion
CSV web query returns one huge row
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.
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:
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 :(
This is how my query is set up in Power BI by default.
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.
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
