Forum Discussion

bugsmakesmenuts's avatar
bugsmakesmenuts
Frequent Visitor
5 years ago
Solved

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

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 try...
Need Help
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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's avatar
bugsmakesmenuts
Frequent Visitor
5 years ago

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.