Combining row data for matching ID - help!

[Chelt75]

HI there, I am new to Power Query and have been checking the forums but don't seem to be able to find the exact answer I am looking for, nor one close enough that I have the expertise to adapt.

I have a query from the text file, I have want to be able to find rows with the same ID and be able to put all the data for each ID that has more than one record onto a the same row.

At the moment, my PQ looks like the table on the left, I am trying to get the table on the right

ID "AL1 1AJ14" has two records and they are in date order already (not all ID sets are)

ID "AL1 1AJ18" has three records, I just want the oldest and the most recent dates and their respective prices but I need the oldest to be Date 1 and Price 1 and the most recent Date 2 and Price 2

ID "AL1 1AJ22" has two records but they are not in the correct date order

Any help is appreciated. Thank you.

[jgordon11 · Accepted solution]

let
Source = Csv.Document(File.Contents("D:\Dropbox\Oscar Properties\Land Registry Price Data\Downloads\pp-complete.txt"),[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type datetime}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "Price"}, {"Column3", "Date"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Price", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Column5", "Type"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each [Column4] <> null and [Column4] <> ""),
#"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Column4", "Column8", "Column9"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"ID"),
#"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"ID", "Date", "Price", "Type", "Column6", "Column7", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Column6", "Column7", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"ID", Order.Ascending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each [Date] >= #datetime(2010, 1, 1, 0, 0, 0)),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each true),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each Text.StartsWith([ID], "AL1 ")),
Group = Table.Group(#"Filtered Rows3", {"ID"}, {{"All", each Table.Sort(_, {"Date", 0})}}),
Filter = Table.SelectRows(Group,each Table.RowCount([All])>1),
dp = {"Date","Price"},
dpt = dp & {"Type"},
RcdCol = Table.AddColumn(Filter, "Record", each Record.SelectFields(Table.First([All]),dp)),
RcdCol1 = Table.AddColumn(RcdCol, "Record1", each Record.SelectFields(Table.Last([All]),dpt)),
RemoveColumn = Table.RemoveColumns(RcdCol1,{"All"}),
ExpandCol = Table.ExpandRecordColumn(RemoveColumn, "Record", dp, {"Date1", "Price1"}),
Result = Table.ExpandRecordColumn(ExpandCol, "Record1", dpt, {"Date2", "Price2", "Type"})
in
Result

[Chelt75]

Wow! Thank you so much for your help - works great - really appreciate the time you took to help me!

Best wishes....

[jgordon11]

Change the source step to the name of your existing query.  Create a blank query and paste the revised code in place of the blank query code.

[Chelt75]

Thank you - will that work as I have already done quite a few steps to get the Power Query stuff to look like the stuff on the left table? i.e. the ID column is actually the merge of three columns after I got the data from the source TXT file.

[Chelt75]

i.e. this

let
Source = Csv.Document(File.Contents("D:\Dropbox\Oscar Properties\Land Registry Price Data\Downloads\pp-complete.txt"),[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type datetime}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "Price"}, {"Column3", "Date"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Price", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Column5", "Type"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each [Column4] <> null and [Column4] <> ""),
#"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Column4", "Column8", "Column9"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"ID"),
#"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"ID", "Date", "Price", "Type", "Column6", "Column7", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Column6", "Column7", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"ID", Order.Ascending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each [Date] >= #datetime(2010, 1, 1, 0, 0, 0)),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each true),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each Text.StartsWith([ID], "AL1 "))
in
#"Filtered Rows3"

[jgordon11]

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Price", type number}}),
Group = Table.Group(ChangeType, {"ID"}, {{"All", each Table.Sort(_, {"Date", 0})}}),
Filter = Table.SelectRows(Group,each Table.RowCount([All])>1),
dp = {"Date","Price"},
dpt = dp & {"Type"},
RcdCol = Table.AddColumn(Filter, "Record", each Record.SelectFields(Table.First([All]),dp)),
RcdCol1 = Table.AddColumn(RcdCol, "Record1", each Record.SelectFields(Table.Last([All]),dpt)),
RemoveColumn = Table.RemoveColumns(RcdCol1,{"All"}),
ExpandCol = Table.ExpandRecordColumn(RemoveColumn, "Record", dp, {"Date1", "Price1"}),
Result = Table.ExpandRecordColumn(ExpandCol, "Record1", dpt, {"Date2", "Price2", "Type"})
in
Result

[Chelt75]

thank you.

I am assuming I just paste this onto the end of my existing stuff in advance editor? I tired but I got a token eof error?