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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power Query Not Recognizing Header Row in CSV when combining multiple files from SharePoint folder

Hi, 

 

I'm trying to combine a set of files saved in SharePoint folder. Files are CSV file type and are same column headers in each file. A new CSV will be saved in the folder each day for the previous day. The issue I'm having is that while Power Query is pulling in all the data it's not recognizing the header row in the CSV files so when combining multiple files it's pulling in header rows multiple times which I don't want obviously. When doing the initial step to combine files the below appears and there is no GUI option to promote headers at this step and I'm newer to Power Query so not sure where to manually insert M code or what that code should even be. 

 

Anyone see this before or have a workaround solution or am missing something?

 

JohnAMay_1-1661706954374.png

 

 

JohnAMay_0-1661706701933.png

 

 

Appreicate any assistance in resolving!

 

Thanks!
John

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

I assume you are using the Combine & Edit functionality to combine your csv files, where a function and query from a selected example file is auto generated. If so, you can just add a Promote Headers step in the example file query, which will auto update the function and apply it in your main query.

 

If not, you can create a custom column on your table of Binary files and use Table.PromoteHeaders(Csv.Document(... to extract the table of data from each file with the headers already promoted.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
BrianMegilligan
New Member

@Anonymous I was encountering a similar issue and I could not figure out why none of my previews and imports were not promoting headers. Turns out I had an option checked from power query to disable this function. I don't know how that happened. From Power Query, click File-->Options and Settings-->Options-->Data Load --> Type Detection--> Always detect column types and headers for unstructured sources

mahoneypat
Employee
Employee

I assume you are using the Combine & Edit functionality to combine your csv files, where a function and query from a selected example file is auto generated. If so, you can just add a Promote Headers step in the example file query, which will auto update the function and apply it in your main query.

 

If not, you can create a custom column on your table of Binary files and use Table.PromoteHeaders(Csv.Document(... to extract the table of data from each file with the headers already promoted.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Thank you!

Anonymous
Not applicable

So I did come up with one workaround solution to just promote headers and to then filter out the duplicate header rows. Would still prefer to learn how to get it to work the "right" way though either using the GUI correctly to acheive straigth away or M code modifications that might be needed. Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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