Power Query Importing all files in folder and unpivot

[joe321]

I would like to replace existing functioning VBA code with Power Query to do the following:

Process all Excel files in a folder and import into pivot table format.

The Excel data has row labels in column A and values in columns B to M. The row headers in B to M are years 2000,2001,2002 etc. I currently unpivot this data via VBA and import it into pivot table format. In a hidden column to the right I have a unique identifier for the row so if future versions of the excel model have lines added or deleted, I can match them up in PowerPivot.

In PowerPivot, I compare the results of many different scenarios creaed in Excel.

I need to import the following:
1) Scenario Name (it is listed in a cell in the excel file)
2) Year,RowId and value for each row columns B to M

Also, the years need to be "unpivoted"

I am a relative novice in Power Query, but am familar with the unpivot function the import all files in a folder menu item.

Any ideas to get me started are appreciated.

 

[anvg]

Hi
Maybe a part of my work code helps you to solve your task

let
    //get a work folder from a named cell in a report workbook
    fileFolder = Excel.CurrentWorkbook(){[Name="Путь"]}[Content]{0}[Column1],
    //get a table of information about files in the folder
    source = Folder.Files(fileFolder),
    //filter the table by .xlsx extension and get a workbook content list
    folderData = Table.SelectRows(source,each Text.Lower([Extension])=".xlsx")[Content],
    //get information about workbooks structure
    baseTables = Table.Combine(List.Transform(List.Positions(folderData),each Excel.Workbook(folderData{_},true))),
    //get data list of worksheets where each item is a table (each workbook has one sheet only)
    neededTables = Table.SelectRows(baseTables, each [Kind]="Sheet")[Data],
    //unpivot each table in the data list
    unpivoted = List.Transform(List.Positions(neededTables), each GetUnpivotedTable(neededTables{_},{"Дата посещения"},type number,"DateVal","Value")),
    //create a combined table from one
    combined = Table.Combine(unpivoted)
in
    combined

GetUnpivotedTable code is

let
    Creator = (this as table, FixedColNames as list, OtherType as type, AttributeColName as text, ValueColName as text) as table =>

let
    unpivotNames = List.Difference(Table.ColumnNames(this), FixedColNames),
    hasUnpivot = if List.Count(unpivotNames) > 0 then true else false,
    unpivotTypes = if hasUnpivot then List.Transform(List.Positions(unpivotNames), each {unpivotNames{_}, OtherType}) else null,
    typedTable = if hasUnpivot then Table.TransformColumnTypes(this, unpivotTypes) else this,
    unpivotedTable = if hasUnpivot then Table.UnpivotOtherColumns(typedTable, FixedColNames, AttributeColName, ValueColName) else this
in
    unpivotedTable
in
    Creator

Regards,

 

[hosseing]

Thanks anvg!
I'm a novice to M language too but your solution works very well for me! Can you also tell me how you'd keep the file names? thanks in advance.