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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
imagautham
Helper II
Helper II

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?

 

excel_sc.png

 

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

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:

PC2790_0-1653381011902.png

 

 

View solution in original post

4 REPLIES 4
imagautham
Helper II
Helper II

Thanks for the solution

MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



PC2790
Community Champion
Community Champion

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:

PC2790_0-1653381011902.png

 

 

CNENFRNL
Community Champion
Community Champion

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors