Power Query slim down Fact Table data

[MVenables]

Hi Community,

Need some support with slimmind down a fact table (Imported) using a Table contains List method please.

so the example below i have created my list of 'CustomerCode' from my dimension table as i only want to bring in the data for these codes.

Below is my M code for when i apply the filters, the issue i have is, it just seems to be loading and whirling round with no end after trying numerous times. (I have also added parameters to the end for Incremental refresh policy).

let
Source = Sql.Databases("afb-sql-v4\afbsqlv4"),
RetailSalesDatawarehouse = Source{[Name="RetailSalesDatawarehouse"]}[Data],
pbi_vw_Sales_StoreCommodityDate_Schema = RetailSalesDatawarehouse{[Schema="pbi",Item="vw_Sales_StoreCommodityDate"]}[Data],
#"Removed Columns" = Table.RemoveColumns(pbi_vw_Sales_StoreCommodityDate_Schema,{"SalesDate"}),
// CustomerCode query
CustomerCodeList = CustomerCode,

// Apply filtering using List.Contains
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each List.Contains(CustomerCodeList, [StoreCode])),
#"Filtered Parameters RS&RE" = Table.SelectRows(#"Filtered Rows", each [SalesDate_Time] > RangeStart and [SalesDate_Time] <= RangeEnd)
in
#"Filtered Parameters RS&RE"

I get the below and it doesn't actually calculate anything.

Just to help with the context of this FACT table. It is transactional data from Retail Stores (247 of them) so it is a lot of data which i believe is the main issue. Any work arounds or there maybe something i havent tried please recommend.

Or maybe you work with similar data yourselves which would be great!

Thanks for reading
Michael

[v-priyankata]

Hi @MVenables 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-priyankata · Accepted solution]

Hi @MVenables 

Thank you for reaching out to Microsoft Fabric Community.

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each List.Contains(CustomerCodeList, [StoreCode]))

This line is likely causing the slowdown because:

• List.Contains() checks every row in your fact table against the full list of store codes.
• It's not folded to SQL,  meaning it's processed in memory after pulling all data from the source (even if it appears early in your query)

To avoid this you can try creating a dimension table of store codes and join instead of list contains or Apply all the changes in the db and in the view get the final data.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks.