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 t...
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:

     

     

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago
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"