Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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:
Thanks for the solution
Hi @imagautham ,
Follow the steps below:
On the other query
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @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:
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! |