Forum Discussion
OneDrive CSV without Headers loaded into PowerBI Service?
I am loading some CSV files from OneDrive for Business into the Power BI service (as Desktop doesn't directly talk to it, and you get the automatic refresh bonus).
However my files don't have headers, and as theres no Query Editor in the Service I can't change that, so my first row is becoming the headers. Its causing some issues with a few files where the first row has the same value in a couple of columns, which then fails to load due to non unique headers.
Is there a way to add headers when loading OneDrive files? If not, does anyone know of a way to add a header row to a CSV in Flow (Im downloading the files in a zip from an external site and extracting them to a OneDrive folder using Flow)? Or, am I going to have to put the files somewhere other than OneDrive and go the long way round using Desktop (and maybe a Gateway) to be able to use PowerQuery on the files (and thus losing the handy 1 hour auto refesh)?
Thanks!
Indeed, but Power Query isn't available when connecting directly to OneDrive files in the Service.
I've found a workaround by using the SharePoint link from a OneDrive file when opened in Excel, used that in Power BI Desktop and utilised Power Query there, then published to the Service and setup a scheduled refresh. It feels like a bit of a hack but it works.
3 Replies
Anonymous ,
Is there a way to add headers when loading OneDrive files? If not, does anyone know of a way to add a header row to a CSV in Flow (Im downloading the files in a zip from an external site and extracting them to a OneDrive folder using Flow)? Or, am I going to have to put the files somewhere other than OneDrive and go the long way round using Desktop (and maybe a Gateway) to be able to use PowerQuery on the files (and thus losing the handy 1 hour auto refesh)?
Currently power bi doesn't have this feature. However, in power query, you can click "Transform", then click "Use First Row as Headers" so that the first row become the new headers. Then publish the report to power bi service.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Indeed, but Power Query isn't available when connecting directly to OneDrive files in the Service.
I've found a workaround by using the SharePoint link from a OneDrive file when opened in Excel, used that in Power BI Desktop and utilised Power Query there, then published to the Service and setup a scheduled refresh. It feels like a bit of a hack but it works.
I solved this case in my own way:
1. I have created a file data_h.csv which contains column headers for the actual data file without headers: e.g. (id, header_1, header_2 etc.)
2. In Power BI - Power Query, add a query: from the txt/csv file (data_h.csv) (if present, remove the step: promote 1st row as headers)
3. add the 2nd query: from the txt/csv file - data file without header - (data.csv)
3. Add a query as a new one - combine rows from 2 tables as new ones: (1st: data_h.csv + 2nd: data.csv)
4. Use the 1st line as headings
