Combine files button not combining the files the way it should be

[bugsmakesmenuts]

Hello, 

I have a list of files whose first 16 rows are file information and blank rows, and the column header starts at the 17th row. The structure looks like the picture below. 

I was trying to import these files to power BI desktop from sharepoint folder. However, after clicking the 'combine files' button, it always squeeze all the columns to a single column as the following. 

However, if I change the data source to 'folder' instead of 'sharepoint', it will work as the way I need. Then I can edit the table in Transform sameple file. 

Why is this happening and how to fix this issue? I really need to import the files from sharepoint. 

Thanks a lot!

[v-jingzhang · Accepted solution]

Hi @bugsmakesmenuts 

I cannot reproduce this. When I connect to csv files stored in Sharepoint folder (Power Query SharePoint folder connector), it displays a window just like when you use folder connector. In below window, the delimiter is comma so the columns are populated as expected. 

Do you see a window like above? If not, check the M codes in Advanced Editor directly. After combining files, the M codes in my queries are as below. Check if Csv.Document function is used in Transform Sample File query. If not, which function is used there? You can try replacing that with Csv.Document function. Modify the variables accordingly. 

Sample File

let
    Source = SharePoint.Files("https://xxxxxxxxx.sharepoint.com/sites/xxxxxxxSite", [ApiVersion = 15]),
    Navigation1 = Source{0}[Content]
in
    Navigation1

Transform Sample File

let
    Source = Csv.Document(Parameter1,[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[bugsmakesmenuts]

Hi @v-jingzhang,

Thank you very much for your answer. It direct me to the result I am looking for!

So when I import the data using sharepoint folder connector, It does not have the delimiter and data type detection as your picture. The default M codes for the correspondingTransform Sample File is

let

    Source = Table.FromColumns({Lines.FromBinary(Parameter7, null, null, 1252)})

in

    Source

I used your code for transform sample file and changed the parameter name and columns number. It is now working!

Very appreciate for your help!

Tina

[bugsmakesmenuts]

I somewhat find the reason. The query editor loads the files as Text file instead of csv file, which is strange. 

When I click the 'combine files' button, maybe because of the strange structure of the data source file, the query editor open the files as 'Text File' which can be noticed by going to Transform  Sample File and click the 'setting' at the source step. 

If I go to Sample File and at the Navigation step, double click the file on the left hand side's icon, it will add a new step 'Import CSV'. Then, go back to Transform Sample File, click the 'setting' icon at Source step, Change 'Open file as' automatic' (csv document not working here).

I can get a Transform Sample File loaded as the source file designed, but when I go back to the query, 

it gives an error "An error occurred in the ‘Transform File (9)’ query. Expression.Error: We cannot convert a value of type Binary to type Table.
Details:
Value=[Binary]
Type=[Type]"

This problem is super confusing, and if anyone can send me a link talking about how these works backend and solve the problem, I will be very appreciated.