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
Im struggling using the table.max as its returning; Expression.Error: The specified sort criteria is invalid.
Details:
let
Source = Folder.Files("XX"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"id", Int64.Type}, {"naam", type text}, {"achternaam", type text}, {"status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"MaxSource", each List.Max([Source.Name]), type nullable text}, {"Detail", each _, type table [Source.Name=nullable text, id=nullable number, naam=nullable text, achternaam=nullable text, status=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([Detail],"MaxSource"))
in
#"Added Custom"
Ive added a screenshot
Column Custom would return rows where MaxSource=Source.Name.
Thanks in advance
Solved! Go to Solution.
each Table.Max([Detail],"Source.Name")
Hi thanks for the reply. It worked!
I am running into a somewhat different problem. I have tried this with a small batch of data. Ive just tried the same steps above but with 5 CSV files (+- 1GB) and it seems Power Query is not responding anymore.
Is there a more efficient way of doing this?
The objective is to retrieve the max value of Source.Name for each ID and their row information.
Thanks in advance
Hi @PQ-Noob675137, you should mark Ibendlin's post as a solution and create new topic.
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.