Merging Files with Fixed Set of Columns and Varied Date Columns (for unpivot)

[oxologic]

Hey everyone, we have a scenario where we are trying to achieve a forecast of the products from different suppliers (for ease of explanation but would fit in most scenarios too). Therefore, we have Column A and B that is fixed (Supplier and Product), while Column C - Z (dates) are used to unpivot the qty in each cell to give us time intelligence info based on Supplier and Product.

We will have various files inside the same SharePoint folder, so whenever a new file was added, it should automatically refresh and give us the latest information. Right now, we can do this provided that Column C-Z are the same dates across all the files. Therefore, the main steps involved are: -> Merge all files from folder, Unpviot -> Results

In our case, where we want this to be usable in all scenarios, i.e. I have a file from 2019, where Column C starts from 1 Oct 2019, and another file where Column C only starts from 10 Feb 2020. However, is there any automatic way for us to do this instead: Unpivot each individual file from Column C onwards -> Merge All Files with Unpivoted Columns -> Results. I know we could do this, by appending each file separately, but this would be a manual method where we are required to know that a new file is added to the folder and then append the new file to the query to get the results.

[trebgatte]

In testing, your scenario should work. Basically, merge all of the files as shown in this post https://marqueeinsights.com/how-to-merge-multiple-excel-files-with-power-bi/, then unpivot the columns. 

I hope this helps.

--Treb, Power BI MVP

[Mariusz]

Hi @oxologic 

Can you Provide samples for these files?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[oxologic]

@Mariusz , hi i've attached the sample data files through wetransfer. I thought there was a way to attach the files previously, but couldn't find it. 

https://we.tl/t-wqa1Av1Hdn

[Mariusz · Accepted solution]

Hi @oxologic 

You can use Get data from SharePoint folder

Then filter the Folder Path to select only the folder path with all the files in question.

 

Create a Blank Query and paste the below script into Advanced Editor to create Function and rename it to "Function".

( content as binary ) => let 
    binaryToTable = Excel.Workbook( content ),
    getData  = binaryToTable{ [Item="Table1", Kind="Table"] }[Data],
    unpivot = Table.UnpivotOtherColumns( getData, {"Supplier", "Product"}, "Date", "Value" )
in
    unpivot

 When in the table with a list of files, go to Add Column ribbon > Invoke Kustom Function and follow the below.

Now you can Expand all the columns from all files.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.