How to combine tabs with differents names from many workbooks?

[roni1986]

Hello,

Today I have 2 workbooks files in the folder "C:\Users\User\Downloads\Teste Power Query" with this pattern names: "FY## Arquivo.xlsx", where ## is the last two digits form current year of the file. In theses files I have a tab with this pattern name "FY## aba", where ## is the last two digits form current year of the file.

In the same folder, I have others workbooks files that doesn't related and every new year a new workbook file is created with the name pattern and tab name pattern above.

I need combine the two tabs, and every year automatcily combine the tab from the new file, but I don't know how to combine with the differents names from the tabs.

I managed to filter the workbooks using start with and contais, but I don't find any way to do this with the tabs.

PS: In theses workbooks there more tabs, but I only need to combine that with pattern name.

[m_dekorte · Accepted solution]

Hi @roni1986,

See if this method is helpful. Although this illustrates how it works with excel tables, the same can be achieved with sheets. If you need further assistance, supply some dummy data.

Step by Step Guide to combine tables from files in a folder. 

Ps. If this helps solve your query please mark this post as Solution, thanks!

[Anonymous · Accepted solution]

Hi @roni1986 ,

Please try the below codesL

let
    Source = Folder.Files("C:\\Users\\User\\Downloads\\Teste Power Query"),
    FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "FY") and Text.EndsWith([Name], "Arquivo.xlsx")),
    TransformFiles = Table.AddColumn(FilteredFiles, "Transformed", each Excel.Workbook([Content])),
    ExpandTables = Table.ExpandTableColumn(TransformFiles, "Transformed", {"Name", "Data"}),
    FilteredTabs = Table.SelectRows(ExpandTables, each Text.StartsWith([Name], "FY") and Text.EndsWith([Name], "aba")),
    CombinedData = Table.Combine(FilteredTabs[Data])
in
    CombinedData

If the above doesn't work, please provide more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[roni1986]

Hello, stoped in this line:

ExpandTables = Table.ExpandTableColumn(TransformFiles, "Transformed", {"Name", "Data"}),

Expression.Error: O campo 'Name' já existe no registro.
Detalhes:
Name=Name
Value=

I Change the "Name" to "Item" (my Excel is in Portuguese), and that worked.

[m_dekorte · Accepted solution]

Hi @roni1986,

See if this method is helpful. Although this illustrates how it works with excel tables, the same can be achieved with sheets. If you need further assistance, supply some dummy data.

Step by Step Guide to combine tables from files in a folder. 

Ps. If this helps solve your query please mark this post as Solution, thanks!