Combining binaries - Same file structures, varying number of column headers RRS feed

  • Question

  • Hope there's a simple solution to this one, please note I'm using Excel 2016.

    So I have a 8 months of sales data, each month stored in a separate excel file, each file has a separate column header for each store branch. Every few months a new store opens, or an old store closes so the number and placement of column header names changes in the file series.

    When I combine binaries, it appears to simply use the first file in the series as a static template, if there are 8 columns, it will simply take the first 8 columns of each subsequent file and combine them, even if they no longer contain the same data. 

    Is there a solution to this other than manually adjusting each file?

    Friday, October 18, 2019 3:07 PM


  • Hi, 

    I guess, you use fixed value for Columns parameter of Csv.Document function, like this:

    = Csv.Document(File.Contents(path),[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None])

    In that case you should replace Columns = 8 for Columns = null or just remove this parameter.

    Otherwise, please provide your code and data sample.

    Friday, October 18, 2019 4:26 PM