Partition Table in Tabular Model using column from related Dimension table using M / Power Query

[nikilouwgmail]

Partition Table in Tabular Model using column from related Dimension table using M / Power Query

I would like to partition my fact tables leveraging the relationship with the Date dimension table, i.e. partition using Date[Date] rather than Fact[Date] column.

I am currently defining the partitions in Power Query using M. This is where I would like to amend my code to achieve above.

The reason I want to do this is so that I can reference only Date[Date] everywhere rather than Fact[Date] for each fact table in my tabular model.

[v-yueyunzh-msft]

Hi , @nikilouwgmail 

According to your description, you want use the dimension column in your fact table using M code?
In your fact table , if you want to get the dimension table's column in Power Query Editor.

You can try to add a "Custom Column" and then enter your dimension table name and the column like this:
DimensionTableName[ColumnName]

Then you can put the column to the face table and then you can use other M code to transform your data.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[nikilouwgmail]

@v-yueyunzh-msft 
hi Aniya
Thank you for your response.

I don't want the column from the related on the fact table, because then I have to do this for all the fact tables and there are many.

I would rather like to use the one dimension table with the column I want to partition on (by harnessing the power of the relationships that are already there between the one dimension table ( DIM_Date[Date] ) and all the fact tables.

Thus I can reference DIM_Date[Date] as a parameter everywhere eg Power Query on all fact tables references this eg SQL agent jobs reference DIM_Date[Date] etc

a cleaner solution 🙂

[v-yueyunzh-msft · Accepted solution]

Hi ,  @nikilouwgmail 

Sorry for i may misunderstand your need, according to your description, you want to use the dimension date table to filter all the fact table due to that you have many fact tables and you do not want to filter one by one?
If this , when power bi get the data , it will execute the M code in Power Query, if you want to filter the data , you can just modify the M code in Power Query Editor.  So, i think It is inevitable that you need to add corresponding M codes to all fact tables one by one.
Power BI is a tool for connecting data. Even creating corresponding parameters to modify the M code requires editing the M code of each fact table.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[nikilouwgmail]

Thank you Aniya