Forum Discussion
How can I import just specific .CSV Column / Row data and not other portions, from a 3rd party file?
Hi There,
I'm wanting to utilise PowerBI to display some statistics from a 3rd party's hasten I add in the public domain and hence usable, data that is contained within their quarterly released .CSV file.
However, 90%+ of the data is containing other sectors of business that I do NOT want the statistics for, in my PowerBI charts or Dashboard that I would like to make.
If you look at the following CSV file: https://ico.org.uk/media/action-weve-taken/csvs/2172893/data-security-incident-trends-201718.csv and the following Column A with ALL Rows and Column C 'Charitable & Voluntary' ALL Rows are the only one that I need to be concerned with getting into my PowerBI?
I have spent sometime on it to date but can't seem to get it to pull in ONLY Column A, All Rows and Column C, All Rows - therefore, any kind guidance, signposting or assistance in getting this to a resolution whilst increasing my learning, would be very much appreciated.
Thanks in advance.
4 Replies
Hi There,
I'm wanting to utilise PowerBI to display some statistics from a 3rd party's hasten I add in the public domain and hence usable, data that is contained within their quarterly released .CSV file.
However, 90%+ of the data is containing other sectors of business that I do NOT want the statistics for, in my PowerBI charts or Dashboard that I would like to make.
If you look at the following CSV file: https://ico.org.uk/media/action-weve-taken/csvs/2172893/data-security-incident-trends-201718.csv and the following Column A with ALL Rows and Column C 'Charitable & Voluntary' ALL Rows are the only one that I need to be concerned with getting into my PowerBI?
I have spent sometime on it to date but can't seem to get it to pull in ONLY Column A, All Rows and Column C, All Rows - therefore, any kind guidance, signposting or assistance in getting this to a resolution whilst increasing my learning, would be very much appreciated.
Thanks in advance.
Carefully09,
Please check if you get expected columns in the following PBIX file. You can review the code in Advanced Editor of the imported query.
https://1drv.ms/u/s!AhsotbnGu1Nok39V5JfvWsIJFzX5
Regards,
LydiaHi Lydia,
Thanks for the reply, it's appreciated.
I do indeed see the correct two Columns and before I sent the above request help, I was getting exactly the same as you have with the getcsv file that you kindly uploaded for me to download and test.
What I don't understand, is that once in PowerBI Desktop it isn't showing ALL the rows, so i can't at this stage begin to build a great Dashboard that will over time, show each quarters, past, present and future stats from the ICO.
I am just trying something different, placing the 3 sets of data alongside each other in 6 columns of the .csv but that doesn't quite work, as it's choosing the headings in all columns and placing them for starters halfway down the row results in the 'Reports' view, which is skewing the data to begin with, it's certainly not straight forward it seems.
Whilst in the 'Data' view it is presenting correctly. Is there a way that I am not aware of at this point, in the 'Report' view to move the records around, so I can get the headings to the top as it is in the 'Data' view please?
Once I can get ALL row data to show, I can then begin to try and get it to show in visualisations, which is the ultimate goal but at present, it's a pain in the ...Attached is my version, in line with the above explanation. https://app.powerbi.com/groups/me/reports/3e6224ed-8232-4b06-a760-0fc1a5af782a?ctid=020fae13-65fc-4a3e-baed-0a1e97818109
Regards,
Carefully09,
I have no access to your report. You can share expected result based on the sample data via Excel.
Regards,
Lydia
