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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.