Forum Discussion

DAST's avatar
DAST
Frequent Visitor
1 year ago
Solved

Earliest date from two fact tables as new query/parameter for date table

I need two new queries or parameter to compare and find the earliest and latest dates in the date columns of two fact tables. These two dates are then passed to the date table as its start and end points.

 

I know what the general structure should be but I don't know enough about M syntax and functions to write it out. I've tried various combinations of Table.AddColumn, List.Generate etc. to no avail. Here's how it looks in my head:

 

  1. Create a list containing two values:
    • The earliest of column Date in table 1
    • The earliest of column Date in table 2
  2. Select the earliest of the two dates.

A similar query would find the latest date of both columns. I can then point the date table at these two values/parameters and tell it to start and end at these dates.

 

Can anyone tell me how to write this in M?

Need Help
Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    Hi DAST ,

     

    In Power Query, I think your parameter code should look something like this:

    // Start Date
List.Min(
    {
        List.Min(Query1[DateColumn]),
        List.Min(Query2[DateColumn])
    }
)
    // End Date
List.Max(
    {
        List.Max(Query1[DateColumn]),
        List.Max(Query2[DateColumn])
    }
)

     

     

    Pete

3 Replies

  • DAST's avatar
    DAST
    Frequent Visitor
    1 year ago

    Thanks both, good to know I was on the right lines - all I was missing was the curly brackets!

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    1 year ago

    Hi DAST ,

     

    In Power Query, I think your parameter code should look something like this:

    // Start Date
List.Min(
    {
        List.Min(Query1[DateColumn]),
        List.Min(Query2[DateColumn])
    }
)
    // End Date
List.Max(
    {
        List.Max(Query1[DateColumn]),
        List.Max(Query2[DateColumn])
    }
)

     

     

    Pete

  • Akash_Varuna's avatar
    Akash_Varuna
    Icon for Super User rankSuper User
    1 year ago

    Try these
    Create a query to find the earliest date by comparing the minimum dates from the Date columns in both fact tables.

    Create another query to find the latest date by comparing the maximum dates from the Date columns in both fact tables.

    Use these two queries as parameters (StartDate and EndDate) for the range in your date table.

    Generate the date table dynamically by creating a continuous list of dates between the earliest and latest dates.