Power Query Group By not merging rows with same ID

Question

I have a table structure given below. I am using Group By in power query to merge the data based on the ID. Since the name is different it seems the power query is not merging the data in one row.

I want the data to be in one row for the same ID and the name I want is for whatever is the name in the last row for that ID.

I hope I could explain my issue well enough. Thanks for your help,

Input

ID	Name	Q1 Sales	Q2 Sales	Q3 Sales	Q4 Sales
A1	Demo	100	200	300
A1	Demo1				400

Output

ID	Name	Q1 Sales	Q2 Sales	Q3 Sales	Q4 Sales
A1	Demo1	100	200	300	400

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"All", each _, type table [ID=nullable text, Name=nullable text, Q1 Sales=nullable number, Q2 Sales=nullable number, Q3 Sales=nullable number, Q4 Sales=nullable number]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Name", "Q1 Sales", "Q2 Sales", "Q3 Sales", "Q4 Sales"}, {"Name", "Q1 Sales", "Q2 Sales", "Q3 Sales", "Q4 Sales"})
in
#"Expanded All"

Answer 1

Try this in the group dialogue: