Forum Discussion

fab's avatar
fab
Helper I
9 years ago
Solved

Load only Excel tabs based on certain value

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 hel...
Need Help
  • Datatouille's avatar
    Datatouille
    9 years ago

    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's avatar
fab
Helper I
9 years ago

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's avatar
Datatouille
Solution Sage
9 years ago

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