Forum Discussion

imagautham's avatar
imagautham
Helper II
4 years ago
Solved

Importing dynamic named worksheets Using a condition

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?

 

 

Need Help
Tips and Tricks
  • PC2790's avatar
    PC2790
    4 years ago

    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: