Table.Group for AllRows without hardcoding the table columns

[Anonymous]

Hi All

I've been looking for a solution to not having to hardcode the table columns in the Table.Group statement of my query when doing a Group for All Rows.

Scenario, every day a file will be exported from the source system as a .TXT file. The issue here is, the Users can pick and choose which columns they want included in the export file, sample as such:

Day1

PROPERTY,SYSTEMDATE,PROPERTY_DESC,CURRENCY,BOOKID,LATITUDE,LONGITUDE,IMPORTKEY
"INDH05",2016-03-18 17:17:31.18,"Application System 05","EUR",null,null,null,"INDH05"
"INDH06",2016-03-18 17:17:49.977,"Application System 06","EUR",null,null,null,"INDH06"
"INDH08",2016-03-18 17:18:24.283,"Application System 08","EUR",null,null,null,"INDH08"
"INDH07",2019-02-07 15:21:37.11,"Application System 07","EUR",null,null,null,"INDH07"

Day 2
PROPERTY,SYSTEMDATE,PROPERTY_DESC,CURRENCY,BOOKID,LATITUDE,LONGITUDE,IMPORTKEY
"INDH05",2016-03-19 18:17:31.18,"Application System 05","EUR",null,null,null,"INDH05"
"INDH06",2016-03-19 18:17:49.977,"Application System 06","EUR",null,null,null,"INDH06"
"INDH08",2016-03-19 18:18:24.283,"Application System 08","EUR",null,null,null,"INDH08"
"INDH07",2019-02-09 16:21:37.11,"Application System 07","EUR",null,null,null,"INDH07"

I can import the files with no issues

The underlying problem is, there are now 2 records for each "PROPERTY", with the difference being the "SYSTEMDATE" has changed. Ultimately, we only want to keep the most recent record.

So in the above example, INDH05 has 2 records, SystemDate = 2016-03-18 and SystemDate = 2016-03-19, we need to remove 2016-03-18 because it is out of date.

All the suggestions I can find is to Group by PROPERTY, apply an Index to the Group and the use the Index to remove any Records where the Index <> 1. 

This also works PERFECTLY.

However, in doing so, the Table.Group has hardcoded the columns in the aggregate columns:

let
Source = Csv.Document(File.Contents("C:\PowerBI Test\VW_BIDATA_RECORDS.TXT"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([IMPORTKEY] <> "" and [IMPORTKEY] <> "IMPORTKEY")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"IMPORTKEY", Order.Ascending}, {"SYSTEMDATE", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"IMPORTKEY", "SYSTEMDATE"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"PROPERTY"}, {{"Record Details", each _, type table [PROPERTY=nullable text, SYSTEMDATE=nullable text, PROPERTY_DESC=nullable text, CURRENCY=nullable text, BOOKID=nullable text, LATITUDE=nullable text, LONGITUDE=nullable text, IMPORTKEY=nullable text, #""=nullable text]}})
in
#"Grouped Rows"

As mentioned, the user can pick and choose which columns they want, so for one User, the columns in the export file will be differnet to the next.

Additionally, I have another 50 types of export files that this has to apply to and I don't want to have to have 50 queries all doing the exact same thing but with different columns, which would then also 

There must be a way for Power Query to dynamically determine what the aggregatecolumns are based on what columns are in the table being Grouped?

I've spent horus trying to find solutions and trial and error and just can't find something that will work.

The alternative is if there is another way to de-dupe records, keeping the most recent record based on a specifed datetime column.

[Marco57]

I need this as well but provided solution is not from my end answering the need

[wdx223_Daniel · Accepted solution]

NewStep=Table.FromRecords(Table.Group(#"Filtered Rows1","PROPERTY",{"n",each Table.Sort(_,{{"IMPORTKEY", Order.Ascending}, {"SYSTEMDATE", Order.Descending}}){0})[n])

[Marco57]

@wdx223_Daniel 

Not working on my end - There is a syntax error

[Anonymous]

THANK YOU! 

This works perfectly and as I suspected, relatively simple.