Discovering Minimal Date and Most Date Throughout All Tables in Energy Question in Energy BI and Excel

Date:


Finding Minimum Date and Maximum Date Across All Tables in Power Query in Power BI and Excel

After we discuss information evaluation in Energy BI, making a Date desk is inevitable. There are completely different strategies to create a Date desk both in DAX or in Energy Question. In DAX you my use both CALENDAR() operate or CALENDARAUTO() operate to create the Date desk. In Energy Question you might use a mixture of Checklist.Dates()#date() and #length() capabilities. Both approach, there may be one level that’s at all times difficult and it’s the way to discover out a correct date vary, ranging from a date previously and ending with a date sooner or later, that covers all related dates throughout the information mannequin. One easy reply is, we are able to ask the enterprise. The SMEs know what the legitimate date vary is..

Whereas this can be a appropriate argument it’s not at all times the case. Particularly with the Begin Date which is a date previously. In lots of instances the enterprise says:

Lets’s take a look on the information to search out out.

That can be an accurate level, we are able to at all times a have a look at the info, discover all columns with both Date or DateTime datatypes then type the info in ascending or descending order to get the outcomes. However what if there lots of them? Then this course of could be very time consuming.

A lot of you might already thought that we are able to use CALENDARAUTO() in DAX and we’re good to go. Properly, that’s not fairly proper. In lots of instances there are some Date or DateTime columns that should not be thought of in our Date dimension. Like Start Date or Deceased Date. Extra on this later on this submit.

On this submit I share a bit of code I wrote for myself. I used to be in a scenario to determine the Begin Date and the Finish Date of the date dimension many instances, so I assumed it’d make it easier to as nicely.

The Energy Question expressions I share on this submit begins with getting all present queries utilizing:

  • #sections intrinsic variable
  • Filtering out the present question identify, which is GetMinMaxAllDates in my pattern, to keep away from getting the next error:

Expression.Error: A cyclic reference was encountered throughout analysis.

Expression.Error: A cyclic reference was encountered during evaluation.

  • Filtering out the queries which are NOT as kind desk
  • Including a brand new structured column named TableSchema that features the tables’ construction
  • Increasing the TableSchema structured column protecting the Identify and Form columns and renaming the Identify column to Column Identify and the Form column to Datatype
  • Filter the outcomes to maintain solely the columns with both Date or DateTime datatypes
  • Filtering out pointless values from the Column Identify like Start Date
  • Including a brand new column named Min Date that will get the minimal worth of the column that seems within the Column Identify column of the desk worth that seems within the Worth column

Hmm! I suppose it’s an excessive amount of mentioning worthcolumn and desk in several contexts. I hope I’m not making it much more complicated although.

  • Including one other new column named Max Date much like how we created the Min Date
  • Extracting the minimal worth of the Min Date column
  • Extracting the utmost values of the Max Date column
  • Displaying the latter two as an inventory

So if you’re in search of an answer right here is the Energy Question expressions that I exploit:

let
    AllQueries = #sections,
    RecordToTable = File.ToTable(AllQueries[Section1]),
    FilterOutCurrentQuery = Desk.SelectRows(RecordToTable, every [Name] <> "GetMinMaxAllDates" and Kind.Is(Worth.Kind([Value]), kind desk) = true),
    AddTableSchemaColumn = Desk.AddColumn(FilterOutCurrentQuery, "TableSchema", every attempt Desk.Schema([Value]) in any other case null),
    ExpandTableSchema = Desk.Buffer(Desk.ExpandTableColumn(AddTableSchemaColumn, "TableSchema", {"Identify", "Form"}, {"Column Identify", "Datatype"})),
    FilterTypes = Desk.SelectRows(ExpandTableSchema, every ([Datatype] = "datetime" or [Datatype] = "date")),
    AddedMinDateColumn = Desk.AddColumn(FilterTypes, "Min Date", every Date.From(Checklist.Min(Desk.Column([Value], [Column Name])))),
    AddedMaxDateColumn = Desk.AddColumn(AddedMinDateColumn, "Max Date", every Date.From(Checklist.Max(Desk.Column([Value], [Column Name])))),
    FilterOutUnnecessaryColumns = Desk.SelectRows(AddedMaxDateColumn, every ([Column Name] <> "BirthDate")),
    MinDate = Checklist.Min(Checklist.Mix({FilterOutUnnecessaryColumns[Min Date], FilterOutUnnecessaryColumns[Max Date]})),
    MaxDate = Checklist.Max(Checklist.Mix({FilterOutUnnecessaryColumns[Min Date], FilterOutUnnecessaryColumns[Max Date]})),
    MinMaxDates = {"Min Date = " & Textual content.From(MinDate), "Max Date = " & Textual content.From(MaxDate)}
in
        MinMaxDates

You’ll be able to obtain the above expressions from right here.

The picture beneath illustrates the outcomes of working the above code in Energy Question Editor having 11 truth tables and 2 dimension tables. These tables have 17 columns with both Date or DateTime datatypes:

GetMinMaxAllDates Query in Power Query

Word: As soon as once more, you should move the present question identify within the expressions above. In my case the present question identify is GetMinMaxAllDates as proven within the picture beneath:

Filtering out the current Query Name

Earlier on this submit I discussed that in lots of instances we do NOT need all Date or DateTime columns to be coated by the Date desk. A great instance for it’s Start Date and Deceased Date. If we don’t observe that then we are able to create loads of irrelevant dates in our Date desk like what we get because the Min Date within the above picture which is 10/02/1916. As you may within the picture above there’s a FilterOutUnnecessaryColumns step. We click on on that step to filter the pointless values from the Column Identify column as proven within the picture beneath:

Filtering out Birth Date

Click on on the final step which is MinMaxDates to see the brand new values as proven within the picture beneath:

New Min Date after fingering out the Birth Date column

By working the above question you get the legitimate date vary, so now you can create a Date desk with any technique of selection, both in Energy Question or DAX utilizing the above date vary. Keep in mind, creating the Date desk is totally separate course of. This question is just serving to us discovering minimal and most legitimate dates throughout all tables loaded into the Energy Question Editor.

Concerns

  • The above tables altogether have 40M rows and the GetMinMaxAllDates question ran in roughly 10 sec on my machine which isn’t unhealthy in any respect. Nonetheless, in bigger tables it might take extra to provide the outcomes
  • You need to have some queries already loaded into the Energy BI Editor
  • This technique additionally works in Direct Question mode, however you count on the question to take extra time to get the outcomes
  • The above question retrieves the min date and max date throughout all tables. Once you create a Date desk, bear in mind that the Date column ought to begin from the 1st Jan of the min date going all the best way as much as the thirty first Dec of the max date
  • This technique works in Energy BI Desktop RS
  • This technique is NOT supported in Energy BI Dataflows

Get pleasure from your Relationship!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Share post:

Subscribe

spot_imgspot_img

Popular

More like this
Related

The right way to Cut back Enterprise Dangers

Should you go away your contact heart uncovered...

Japanese authorities confer on weak yen, trace at intervention choice By Reuters

By Tetsushi Kajimoto TOKYO (Reuters) - Japan's...

Why healthcare outsourcing is on the rise

For greater than twenty years, Black E book...