Importing dynamic named worksheets Using a condition

[imagautham]

I have a situation where a data source (xlsx) file has a changing sheet name. 

The excel file has multiple sheets like Region,Location,ABC 1.1,ABC 2.2, ABC 2.1

I have a  requirement where I need to get a single sheet based on a condition.

The condition are

1. It should contain 'ABC'

2. Now there are three sheets with name ABC. It should pick only ABC 2.2 because 2.2 is greater than 2.1 and 1.1.

So next week if the data gets refreshed and if I find a sheet ABC 3.1, it should pick that sheet and load the data in that sheet.

How to write a M code for this condition.  Can someone please help?

[imagautham]

Thanks for the solution

[MFelix]

Hi @imagautham ,

Follow the steps below:

• Duplicate your query and just keep the source step
• Delete all columns except the name (that is the spreadsheet)
• Filter for the ABC
• Order by Descending order
• Keep only 1st row
• Drill down on the value
• Rename the query in order to know the name to use on the other query

On the other query

• After the source step do a filter by Name column (any value)
• Replace the value on the filter by the name of the previous query know it's dinamic

Just be sure that the pages have always the same names. Also be aware that since you are using a text field the sorting can be strange if the numbers have diferente formats you may need to add some additional steps on the first query to split the numbers.

Check PBIX file attach.

[PC2790 · Accepted solution]

Hey @imagautham ,

ANother way of doing it:

let
    Source = Excel.Workbook(File.Contents("C:\Users\pankhari.chawla\Documents\Book1.xlsx"), null, true),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Name.2] = List.Max(#"Split Column by Delimiter"[Name.2])))
in
    #"Filtered Rows"

Outcome:

[CNENFRNL]

let
    Source = Excel.Workbook(File.Contents("d:\Alldownloads\test.xlsx"), null, true),
    Max = let abc = List.Select(Source[Name], each Text.Contains(_, "abc", Comparer.OrdinalIgnoreCase)), max = List.Max(List.Transform(abc, each Number.From(Text.Select(_, {"1".."9","."})))) in List.Select(abc, each Text.Contains(_, Text.From(max))){0},
    #"Selected Worksheet" = Source{[Name=Max]}[Data]
in
    #"Selected Worksheet"