Hi everone!
I have stuck for a long time during my work in PQ. I'd like to keep the most recent row in my database, so I used:
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Merged", each [Est Number]&[ID]&[Place]&[Line]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Merged", type text}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type1",{{"Action Date", Order.Descending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Merged"})
and performing it is almost impossible. Refresh preview takes hours and also applying this query into PowerBI. I have to add other table transformation after removing duplicates, as adding conditional columns, maybe merging queries, but it can't be done with that time performance.
I also tried "Group by" method for that, but it was even worse, I couldn't even load column names after expanding data, because my laptop wanted to burn himself (Intel core i7).
I have one main file in Excel (260 MB, almost 1M rows) and each month I want to add new file (about 20 MB). Source for this query is Folder path. I tried to divide this main file into 10 seperatly files and try this with CSV format and clear cache. This all didn't help.
From my position at the job, I can't use SQL server or sth familiar, we receive this files from others, it is already filtered as we need. So far I only work in PBI using Excel/CSV/folder path as a source.
Could anyone write a hint for me?
