Load only Excel tabs based on certain value

[fab]

Hello,

I have an Excel file with many tabs.
I would like to load (Power BI desktop) automatically tabs that contain a certain value in theirs names.

How can I do that ?


Thank for your help.

[Datatouille]

Hi,

The tabs which match your name's condition(s), do they all have the exact same format ?

[fab]

Yes,

For instance, I have the excel file with these tabs :

- 1_internal

- 1_customer

- 2_internal

- 2_customer

-....

Tabs withs "internal" have the same format (but not the others...) and I want to load only these one.

[Datatouille · Accepted solution]

Ok,

So try this: Open the Query Editor, copy/paste the following M code (advanced editor) and adapt the green part.

let
    Source = Excel.Workbook(File.Contents("YourExcelFileFullPath"), true, true),
    KeepCol = Table.SelectColumns(Source,{"Name", "Data"}),
    Test = Table.AddColumn(KeepCol, "Flag", each Text.Contains([Name],"YourTestWord")),
    KeepTrue = Table.SelectRows(Test, each ([Flag] = true)),
    Expand = Table.ExpandTableColumn(KeepTrue, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"})
in
    Expand

[fab]

I would like to complete this query:

Your solution is working fine for one file.

How can I to apply this code for all files (xls same structure) in a folder ?

(I want names of tabs and files as a column too).

[Datatouille]

Ok this one should work:

let
    Source = Folder.Files("YourFolderPath"),
    KeepCol = Table.SelectColumns(Source,{"Name"}),
    Sheet_Info = Table.AddColumn(KeepCol, "Personnalisé", each Excel.Workbook(File.Contents("YourFolderPath" & [Name])),
    ExpandSheet = Table.ExpandTableColumn(Sheet_Info, "Personnalisé", {"Name", "Data"}, {"Name.1", "Data"}),
    Test = Table.AddColumn(ExpandSheet, "Flag", each Text.Contains([Name.1],"YourTestWord")),
    KeepTrue = Table.SelectRows(Test, each ([Flag] = true)),
    Develop = Table.ExpandTableColumn(KeepTrue, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})
in
    Develop