Forum Discussion

rahul_gupta's avatar
rahul_gupta
Regular Visitor
4 years ago

Error while combining files

Hell There,    I have excel file which has data form column A to T. For each file Column A, B, Q,R,S, T will have same header. But for C to P header will change. Eg for January header will be 1st J...
Need Help
rahul_gupta's avatar
rahul_gupta
Regular Visitor
4 years ago

Hello Anonymous 

 

Thank you for your quick response. The steps you have expalianed above is correct and using that i have created function. The issue i am facing is when i am trying to combine multiple files.

 

In my folder i have approx 59 files. And a new file is added every week. So this number will keep increasing.

When i am tryingto combine files then error pops up.

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

rahul_gupta - one approach I have tried in the past is to use SharePoint.Contents instead of SharePoint.Files.  This can offer more stability.  I also recommend a custom function to read the Binary file, this allows you to read the files one by one.  I normally use a Power BI Dataflow for these types of queries as well.

 

The function look like this:

(#"Excel Binary File" as binary) => 
let
   #"Buffer File" = Binary.Buffer( #"Excel Binary File" ),
   #"Open Excel" = Excel.Workbook( #"Buffer File" , True )
   #"Select Table or Sheet" = #"Open Excel"{0}[Data]
    blah blah
in
   Result

If you have lots of transformation, you have find it better to read the File into Dataflow table, then perform the transformation in second linked dataflow.   I.e. separate dataflow for Read and Transform, then Load the Dataset from the second dataflow.