Forum Discussion
Power Query find minimum/maximum dates across multiple tables
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.
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?
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.
