Combine CSV files without top N rows (~ IIS log files)

[stej]

Hi, I tried to combine IIS log files into one data set, but still with no luck. The problem are comments like this at the beginning

#Software: Microsoft Internet Information Services 8.5
#Version: 1.0
#Date: 2017-01-18 00:00:00
#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Cookie) cs(Referer) cs-host sc-status sc-substatus sc-win32-status sc-bytes cs-bytes time-taken

When I click on "Combine Binaries", a dialog is opened and the engine tries to guess structure of the CSV file. This is fine. But when selecting "space", it takes the first row "#Software: Microsoft Internet Information Services 8.5" and makes a guess there are only 6 columns.

There are much more columns in the IIS files of course. But I can't workaround this feature. 

In ideal world I would need to

1. skip first 3 rows
2. - take line "#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Cookie) cs(Referer) cs-host sc-status sc-substatus sc-win32-status sc-bytes cs-bytes time-taken"
   1.    - strip #Fields"
   2.    - use the remaining items to as column names
3.  - do it for every file

The combine itself is described here https://sharepointmike.wordpress.com/2016/03/19/loading-and-combining-multiple-csv-files-in-power-bi... but the rest. Any help is really appreciated.. as well as pointers to doc, forums or whatever. I tried to search how to analyze log files in Power BI but with no luck.

[mike_honey · Accepted solution]

The text file column detection doesnt help in this scenario.  I would set the delimiter to Tab get a single column of text.  Then you can use Remove Rows, Split Column, filters and Use First Row as Headers to get to what you want.  

You can make those changes in the generated "Transform Sample Binary from ..." Query.

[stej]

@mike_honey that would probably work (although I didn't try it). I'll mark as accepted, because there is probably currently no better solution.