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
    4 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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 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
4 years ago

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