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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi rahul_gupta , please consider using the following steps to import the Excel files:

 

If the data from Excel Workbook is in a "Sheet" (i.e. not in a named "Table") and the data starts on the first row, add the "true" to import the worksheet with Headers Promoted.

Source = Excel.Workbook(#"Excel File", true)

With the inital table which contains "Known" columns "A, B, Q etc", you can now used the "Unpivot Other Columns" function by selecting these known columns.

 

#"Unpivot" = Table.UnpivotOtherColumns(#"Get Sheet", {"A", "B", "Q", "S", "T" }, "Attribute", "Value")

From here, you can transform the Other Column heading to Dates and format the values to the correct data types.

 

The above can then be converted into a useable function that can accept different Excel files or multiple worksheets.

 

 

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.