Create summary data using existing dataset/data source(detail level)

How can i extract a detail level dataset into a summary level NEW dataset, to be used for a join operation with other summary level datasets, in QuickSight?

There is a video on how to extract, but not what I am trying to do. - Summarize then join

If I understand your question, you are trying to aggregate/summarize data from a table before you join it to some other data?

Currently there is no way in the UI to do this, but it is something the product team is looking to add. Today you would need to use Custom SQL to do the aggregation, and then could join that to other tables or other Custom SQLs. Alternatively you could use AWS Glue DataBrew to summarize/aggregate the data before connecting to it in QuickSight.

DataBrew is not an option for me.
How would I use the custom SQL?
And could this custom SQL be applied to s3 files?

For S3 you would need to use Athena in order to use Custom SQL - that makes the S3 data look and feel more like a relational database (you will have tables/views, can write SQL, etc). Otherwise the S3 connector purely imports files via a manifest file.

With Custom SQL you would write a query which selects all the fields you want and includes a GROUP BY to the level you want to summarize/aggregate to. Then you can join the result of that to anything else you want.