Limit Data Retrieval Based on Friday Dates

[clarkpaul]

Our data warehouse has a daily snapshot of inventory.  I want to show trends over time on a weekly basis (every Friday).
I have a dynamic date table that has every date from 1/1/2024 to current date.  I filtered it down to show only the Friday dates and created a list called "Fridays"
How can I use this to limit my data pull from the data warehouse? (The name of the field is SNAPSHOT_DATE)

 

let
Source = Odbc.DataSource("dsn=Snowflake", [HierarchicalNavigation=true]),
MAC_RAPID_PROD_Database = Source{[Name="MAC_RAPID_PROD",Kind="Database"]}[Data],
APPS_Schema = MAC_RAPID_PROD_Database{[Name="APPS",Kind="Schema"]}[Data],
FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V_View = APPS_Schema{[Name="FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V",Kind="View"]}[Data]
in
FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V_View

[ahadkarimi · Accepted solution]

Try this code, If you have any questions or need more info, just hit me up!

let
Fridays = YourFridaysListQuery,
Source = Odbc.DataSource("dsn=Snowflake", [HierarchicalNavigation=true]),
MAC_RAPID_PROD_Database = Source{[Name="MAC_RAPID_PROD",Kind="Database"]}[Data],
APPS_Schema = MAC_RAPID_PROD_Database{[Name="APPS",Kind="Schema"]}[Data],
FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V_View = APPS_Schema{[Name="FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V",Kind="View"]}[Data],
FilteredData = Table.SelectRows(FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V_View, each List.Contains(Fridays, [SNAPSHOT_DATE]))

in
FilteredData

[tharunkumarRTK]

@clarkpaul 

You can add one more line to your m-cdoe and it filters your data.

let
Source = Odbc.DataSource("dsn=Snowflake", [HierarchicalNavigation=true]),
MAC_RAPID_PROD_Database = Source{[Name="MAC_RAPID_PROD",Kind="Database"]}[Data],
APPS_Schema = MAC_RAPID_PROD_Database{[Name="APPS",Kind="Schema"]}[Data],
FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V_View = APPS_Schema{[Name="FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V",Kind="View"]}[Data],
FilteredDate = Table.SelectRows(FACT_SCM_ONHAND_DETAILS_SNAPSHOT_V_View, each List.Contains(Fridays , [SNAPSHOT_DATE] ) )
in

But I dont think the above step supports query folding, I would suggest you to perform this transformation in a SQL statement and pass it as a native query. 

https://learn.microsoft.com/en-us/power-query/native-database-query

You are using odbc connection and it does support native query.