Forum Discussion
Power Query find minimum/maximum dates across multiple tables
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?
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:
Table
Name(table) ColumnName(text)------------------------ ------------------
Sales SalesDate
Sales OrderDate
Purchase PurchaseDate
Does this make sense?
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.
11 Replies
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"Can you give us a small example of the input table and the result table you want to see?
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
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:
Table
Name(table) ColumnName(text)------------------------ ------------------
Sales SalesDate
Sales OrderDate
Purchase PurchaseDate
Does this make sense?
