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 help.

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

5 Replies

  • 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