Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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...

 

View solution in original post

8 REPLIES 8
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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 Smiley Happy

 

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. Smiley Embarassed

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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
Not applicable

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

 

PowerBIQuery.PNG

Anonymous
Not applicable

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...

 

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

Glad to hear it works for you!

 

Sorry that I couldn't be of further assistance.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.