Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fab
Helper I
Helper I

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.

1 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

View solution in original post

5 REPLIES 5

Hi,

 

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

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.

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

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

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.