Forum Discussion

MVenables's avatar
MVenables
Advocate II
1 year ago
Solved

Filter down Data in a Fact table Power Query

Hi all,

 

I need some help/suggestions please.

 

I have a fact table called Customer Invoice (Which is Imported), but i don't want to load in all the sales data as its not needeed.

 

I only want to see Data that is contained in my Dimensions table 'StarCustomer'[CustomerCode] (as i have slimmed down the data just to see sales from new customers). My Customer Invoice table does have [CustomerCode] as a column. I have tried an inner merge but it just fails or won't load. Is there a way of using parameters as i do with RangeStart & RangeEnd as i have an imcremental refresh setup on this model. 

Or any other method i could try. Any help is much appreciated as i need a method i can use for the future when slimming down data that i don't need from a fact table.

 

Thank you,

 

Michael

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi MVenables  Inner join should work. However, You could also try left outer join, expand only customer code and then filter out null values.

     

    Also, you could create a list of customer code from customer table and based on that list filter fact table. 


    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

3 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi MVenables  Inner join should work. However, You could also try left outer join, expand only customer code and then filter out null values.

     

    Also, you could create a list of customer code from customer table and based on that list filter fact table. 


    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi MVenables using M code 

     

    = Table.SelectRows(CustomerInvoice, each List.Contains(StarCustomer[CustomerCode], [CustomerCode]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi MVenables ,
    Thank you techies  and shafiz_p  for the helpful repsonses.

    May I ask if you have gotten this issue resolved?
    If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

    Thank you for being a valued member of the Microsoft Fabric Community Forum!