I can offer you some intuitive advice, just from long experience with Power Query.
Thats a lot of filters, before an unpivot and then some more filters, and then removing duplicates. There's no way query folding is surviving that--I guarantee it's folding no further than after you remove duplicates,
It looks like these are all tables from the same database--they may even be properly keyed and indexed. I would do all of the table joins after one or two of the filters that cut down the most data--but as far upstream as possible. I would left join them as Table.Join, as opposed to Table.NestedJoin, so you may have to adjust some column names for your joins. Nevertheless, I am often surprised at how easily you can join quite a few tables without issue, because it's all folding to SQL, which can handle it easily. "Do all the joins while you are still folding, no matter how many rows it returns."
You could probably still fold after applying the current month fillter--which you should calculate as a single date value in another query Named ThisMonthStart, so that you have that single date for your main query to use as a filter, and consequently for Power Query to turn into SQL, instead of however you might be doing it now.
ThisMonthStart = let DayNumber = (Date.Day(DateTime.LocalNow())*-1) in Date.AddDays(Date.From(DateTime.LocalNow()), DayNumber)
And then you can make your main query's #"Filter Only Current Month" step can be
Table.SelectRows(PriorStepName, each [DateColumnName] > ThisMonthStart)
So apply the date filter before the joins and see if that helps.
If you are using some of these join steps merely to select a list of values in your table against another table (as in, you are just getting rid of all the columns in the other table right away), and its not too many values or a lot of strings, consider filtering instead, using a buffered list, like
Table.SelectRows(PriorStepName, each List.Contains(List.Buffer(OtherTableQuery[ValuesToKeepColumn]), [ThisTableColumn]))
That gives SQL an IN statement with those values in it.
Make sure you are extracting dates using Date.From, not DateTime.Date. They claim DateTime.Date folds, but it will fail you in places where Date.From succeeds.
The Unpivot steps are not necessarily hit or miss as far as folding, but it's not something I can define here.
The group will likely always be slow, so do it as far down as possible, like you already have it.
OK, good luck!
--Nate
I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
