Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.