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:

     

     

4 Replies

  • 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"
  • PC2790's avatar
    PC2790
    Community Champion
    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:

     

     

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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.