Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sdjensen
Solution Sage
Solution Sage

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?

/sdjensen
2 ACCEPTED SOLUTIONS
arify
Microsoft Employee
Microsoft Employee

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?

View solution in original post

arify
Microsoft Employee
Microsoft Employee

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.

View solution in original post

11 REPLIES 11
arify
Microsoft Employee
Microsoft Employee

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 

/sdjensen
arify
Microsoft Employee
Microsoft Employee

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?

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.

/sdjensen
arify
Microsoft Employee
Microsoft Employee

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 🙂 )

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.

/sdjensen
arify
Microsoft Employee
Microsoft Employee

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
Microsoft Employee
Microsoft Employee

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
Solution Sage
Solution Sage

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"

/sdjensen

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors