Power Query find minimum/maximum dates across multiple tables

[sdjensen]

Hi,

I am trying to create some M code, that will find the minimum and maximum year of all my dates in my model.

Lets say I have 2 fact tables in my model: Sales and Purchase. In these 2 tables I have SalesDate and PurchaseDate

I now need to find the first and last date in those 2 columns to find the first year and last year that I have data. So far I have done this by a query against my SQL source, but I really want to avoid this solution to minimize load on the source server.

I have so far created this function, but I have no idea how to feed this function with values from a table.

What I have been trying to do is create a table with 2 columns, one containing the names of the tables and the other containing the names of the columns with the dates, and then I want to run my function for each row of this table, but how do I do that??

My function is this:

let
    FnMinMaxDate = (TableName as table, ColumnName as text) =>
let
    DatesPerTable = Table.FromRecords(
        {[MinDate = List.Min(Table.Column(TableName, ColumnName))
            , MaxDate = List.Max(Table.Column(TableName, ColumnName))]}, 
        type table [MinDate = datetime, MaxDate = datetime])
in
    DatesPerTable,
    #"Invoked Function" = FnMinMaxDate(Sales, "PostingDate")
in
    #"Invoked Function"

The result I expect could be just a table with 2 colums and one row containing the minimum year (or date) and maximum year(date) or a table with 2 columns: Type and Value and then 2 Rows: MinYear, xxxx and MaxYear, yyyy

 Can someone please assist me getting the last step I need?

[arify · Accepted solution]

Btw, looks like there's a slight confusion in the function signature:

   FnMinMaxDate = (TableName as table, ColumnName as text)

Do you want your first parameter to be table, or a table name? Looking at your parameters name and your input table, you're passing down the table's name (text), but your parameter type is table. I would recommend passing the table itself, instead of its name (check Table.Column's input parameters). So it should look like this:

   FnMinMaxDate = (InputTable as table, ColumnName as text)

And your input table:

TableName(table)    ColumnName(text)

------------------------   ------------------

Sales                          SalesDate

Sales                          OrderDate

Purchase                   PurchaseDate

Does this make sense?

[arify]

Can you give us a small example of the input table and the result table you want to see?

[sdjensen]

I need to create the input table myself, but I expect it to have to columns. First column should contain the name of the the table and the second column should contain the name of the columns containing the dates in the table. Since there can be mutiple date columns in a fact table I would have to list those table multiple times.

This input table could look like this.

TableName    ColumnName

----------------   ------------------

Sales             SalesDate

Sales             OrderDate

Purchase      PurchaseDate

The first output using my function would in this example contain 2 colums and 3 rows

MinDate        MaxDate

---------------   ---------------

2014-05-01  2016-04-22   <= being min and max data in Sales.SalesDate

......              .....                 <= being min and max date in Sales.OrderDate

......             ......                 <= being min and max date in Purchase.PurchaseDate

From this I can then calculate the min year and max year of dates in all my data and dynamically create a dates table that I am sure will contain all the dates present in my data.

I hope this makes sence 

[arify · Accepted solution]

Btw, looks like there's a slight confusion in the function signature:

   FnMinMaxDate = (TableName as table, ColumnName as text)

Do you want your first parameter to be table, or a table name? Looking at your parameters name and your input table, you're passing down the table's name (text), but your parameter type is table. I would recommend passing the table itself, instead of its name (check Table.Column's input parameters). So it should look like this:

   FnMinMaxDate = (InputTable as table, ColumnName as text)

And your input table:

TableName(table)    ColumnName(text)

------------------------   ------------------

Sales                          SalesDate

Sales                          OrderDate

Purchase                   PurchaseDate

Does this make sense?

[sdjensen]

How do I create a table that in the 1st column contains the full table  and then column names that I type in the 2nd column?

Is there a function that will return the full table in a single cell?

I would really prefer a solution where i create this in one single query and then loop my custom function instead of having a query with my function and then calling this in another query, but if this works then I will have to live with that.

[arify]

About how to create a table with tables in it, #table function is an easy way in M

table1 = ..

table2 = ...

table3 = ...

tablesTable = #table({"first column name", "second column name"

] , { {table1, columnname1], {..], {..] ..

(replace ]s with closing curly braces, there's a forum bug that swallows my code apparently 🙂 )

[sdjensen]

I used Table.FromRecords function to create my table and then call my custom function with values from this table.

Thanks again.

[sdjensen]

If anyone should read this I will not recommend this approach to anyone.

Power Query can't optimize the query for this, so when you use a table like this it will get the full table twice from the source instead of noticing "Hey, this query use a table I will get in another query, so I will go get the table content there first".

[sdjensen]

Figured out how to create my input table with tables in the first column... just need to work the rest.. I will get back to you if I need more help and mark as reply if I don't.

I am new to M, but it's really awesome to work with, but also frustrating at times.. ha ha

Thanks mate.

[arify]

I'm glad you liked M 🙂 If you're interested, there are some resources (like books or blogs) that can help you learn more about it.

[arify · Accepted solution]

And then, what you need is probably to call Table.AddColumn function (in the query editor, you would see a button "Add Custom Column" for it)

you would call it in a way like this:

= Table.AddColumn(tableWithTablesAndColumnNames, "MinAndMax", each FnMinMaxDate([Table], [ColumnName]))

One other thing, if you're returning only 1 row, consider returning just a record instead of a table with 1 row.

[sdjensen]

hmmm.. doesn't show all my code?

let
    FnMinMaxDate = (TableName as table, ColumnName as text) =>
let
    DatesPerTable = Table.FromRecords(
        {[MinDate = List.Min(Table.Column(TableName, ColumnName))
            , MaxDate = List.Max(Table.Column(TableName, ColumnName))]},
        type table [MinDate = datetime, MaxDate = datetime])
in
    DatesPerTable,
    #"Invoked Function" = FnMinMaxDate(Sales, "SalesDate")
in
    #"Invoked Function"