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.
Hi Team,
I have requirement like, from a folder of Excel files i have to pick the data file of MAX datekey
As mentioned in the image file with max date key to be picked.
I believe it is possible by passing parameter to MQuery.
Thanks in advance..
Solved! Go to Solution.
Hi @vedansh,
You can use query below to find the Excel file with the maximum date in Power BI desktop:
let Source = Folder.Files("<folder path>"), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}), #"Changed Type1" = Table.TransformColumnTypes(Table.SplitColumn(#"Changed Type", "Name.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Name.2.1", "Name.2.2"}),{{"Name.2.1", Int64.Type}, {"Name.2.2", type text}}), MaxDte=List.Max(#"Changed Type1"[Name.2.1]), FilterRowWithMaxDate=Table.SelectRows(#"Changed Type1",each [Name.2.1]=MaxDte) in FilterRowWithMaxDate
Then you can click Binary to extract data from the Excel file.
Best Regards,
Qiuyun Yu
Hi @vedansh,
You can use query below to find the Excel file with the maximum date in Power BI desktop:
let Source = Folder.Files("<folder path>"), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}), #"Changed Type1" = Table.TransformColumnTypes(Table.SplitColumn(#"Changed Type", "Name.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Name.2.1", "Name.2.2"}),{{"Name.2.1", Int64.Type}, {"Name.2.2", type text}}), MaxDte=List.Max(#"Changed Type1"[Name.2.1]), FilterRowWithMaxDate=Table.SelectRows(#"Changed Type1",each [Name.2.1]=MaxDte) in FilterRowWithMaxDate
Then you can click Binary to extract data from the Excel file.
Best Regards,
Qiuyun Yu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |