Partitioning Data by Date using Power Query

[Anonymous]

Hello,

I'm currently looking to implement partitioning on a data model with several large fact tables in it, I've replicated the partitions in Tableau Editor but I need to add some MQuery into each to only add CY, PY, P2Y into the respective partitions. I've not really done much with Mquery until now so unsure how to do this in M.

I'm havinf to do it this way and not via SQL Server and Incremental refresh because these Fact tables are loaded from a CSV file thats regenerated nightly so need to do this in M.

Any help would be greatly appericated. 

Thanks 

[Anonymous]

Hello

I'm in Tableau Editor and I'm looking to create 3 partitions in a Fact Table (see below example) and then have some MQuery in this that only puts the CY in one partition, the some in Previous Year in another and Year on 2 Year in the last.

I think I need to use the Table.Partition M Function but unsure if this'll work and how to use it ? 

[BA_Pete · Accepted solution]

Hi @Anonymous ,

From what I understand of the Table.Partition function (not a huge amount), it only accepts a single hash function with which to evaluate all partitions i.e. it can't make three distinct assessments on the same table to partition.

Do you specifically have to output a list of tables, or could you just use three distinct table queries to import into Tableau?

If this is feasible, you can either import your table into PQ three times and filter them as required, or import once and reference this query three times to the same effect.

The table filters you would need to apply in M are as follows:

// CY filter
Table.SelectRows(
  previousStep,
  each Date.IsInCurrentYear([yourDateColumn])
)

// PY filter
Table.SelectRows(
  previousStep,
  each Date.IsInPreviousYear([yourDateColumn])
)

// P2Y filter (presume this means 'two years ago')
Table.SelectRows(
  previousStep,
  each [yourDateColumn] >= Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()), -2))
and [yourDateColumn] <= Date.EndOfYear(Date.AddYears(Date.From(DateTime.LocalNow()), -2))
)

Pete

[BA_Pete]

Hi @Anonymous ,

Not sure what you mean by 'partition' the fact table. Do you mean you want separate fact tables for CY/PY etc. or that you just want to be able to report on those different timeframes?

Pete