SQL Query to pull data and store in PowerBI table

[AlanTee]

HI, 

I work for a large global company in a customer specific role. I'm looking to create a dashboard to give visibility to select customers who are dealt with by my team. A single customer can generate millions of records and as the customer list currently is around 60, this can easily move into the 100's of millions of records. I would normally write a SQL query for the specific customer/parameters I want to review however I want to try automate this process and give visibility to colleagues who are not able to use SQL. 

What I want to know, is it possible to do the following. 

1. When records added to a Sharepoint list, update a table with the customer number to be added to the SQL query.
2. Daily (early AM) do a SQL import of all newly generated records where the customer number matches the list. 
3. Weekly do a status update against existing records until a certain status is reached, I was going to add a Update boolean column to define this query query.  
4. Retain records for 6 months. 
5. Remove records after 6 months. 
   
   

My idea was to run the query, and add the data to a table (Customer_ID) then run the next Query based with the SQL query indicating WHERE Customer_ID IN 'LIST OF CUSTOMER_IDs' then once this data is obtained store within a static table that doesn't update. Then a seperate query to run Weekly WHERE Record_ID IN = 'List of Record_IDs' AND multiple additional query steps. 

I've tried this second query dynamically passing a string QUERY with all of the records & the rest of the query, however I'm continually getting an error stating i'm trying to use an external source. 

Do you think it is possible what I'm trying to achieve? If so what is the best method?

Thanks in advance.

[littlemojopuppy]

This is only the solution for #4 and 5: look at Date.IsInPreviousNMonths in Power Query.  That will limit to the previous six calendar months.  You'll probably want to combine that with Date.IsInCurrentMonth so you get current month as well.  It would probably be more efficient to write SQL to filter for the current MTD and the previous six months than use Power Query.  But what I provided will work for #4 and 5.