Connecting from Power BI and reporting (incremental refresh)

Hi All..

I am trying to connect to Elasticsearch from Power BI to do some reporting. I am opting for ODBC connector route.

My indexes are Name-2025.35 (Year.week no)..hence i dont have a single source of truth rather multiple indexes. So I need to build a custom query either in the ODBC sql connector box OR in Power Query..

My ask is can I leverage incremental refresh after the first full load…? Does elastic have query folding capabilities…?

My reporting is of medium complexity (1-time monthly refresh pulling all indexes for previous month - i.e. refresh on Sept 5th will pull all weekly indexes for Aug, appx row count for a month 0.6 to 1M), and I just have to pull 5/6 fields from indexes which does have a timestamp field in it. My team has platinum subscription.

I am new to Elastic and I need to get this clarity at the earliest n order to proceed with PoC.