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

Hi,

 

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

  • fab's avatar
    fab
    Helper I
    9 years ago

    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's avatar
      Datatouille
      Solution Sage
      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).