Forum Discussion

clarkpaul's avatar
clarkpaul
Helper I
2 years ago
Solved

Limit Data Retrieval Based on Friday Dates

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

 

Need Help
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

    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

2 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    2 years ago

    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.

     

     

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    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