Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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!
Solved! Go to 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.
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
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.
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
Glad to hear it works for you!
Sorry that I couldn't be of further assistance.
User | Count |
---|---|
89 | |
88 | |
84 | |
81 | |
49 |
User | Count |
---|---|
151 | |
143 | |
111 | |
74 | |
55 |