Code Explainer | DAX

Custom Date Table Generation for Fiscal Years in Power Query M

This Power Query M code snippet utilizes a custom function, 'fxFY', to generate a fiscal year date table from the years 2020 to 2025, adding a 'CY' column based on 'StartDate' year. It employs key functions like List.Transform, Table.FromColumns,


Empty image or helper icon

This Query related with thread "Power Query Date Table Generation and Refactoring"

Prompt

let
    fxFY = ( years as list ) as table => [
        t = List.Transform( years, each [
            n = _ - List.First(years),
            FYs = List.Repeat( {_}, 52),
            FYID = List.Repeat( {n+1}, 52),
            Weeks = {1..52},
            FWID = List.Transform( Weeks, (x)=> (52 * n) +x ),
            FYPeriod = List.Transform({0..51}, (x)=> Number.IntegerDivide(x, 4) +1),
            FPID = List.Transform(FYPeriod, (x)=> (13 * n) +x ),
            StartDate = List.Transform( {0..51}, (x)=> Date.StartOfWeek( Date.AddDays( #date(_, 4, 1), x *7), Day.Saturday )),
            EndDate = List.Transform( {0..51}, (x)=> Date.EndOfWeek( Date.AddDays( #date(_, 4, 1), x *7), Day.Saturday )),
            Dates = List.Transform( StartDate, (x)=> List.Dates(x, 7, Duration.From(1))),
            t = Table.FromColumns(
                {
                    FYs, FYID, Dates, Weeks, FWID, StartDate, EndDate, FYPeriod, FPID
                },  type table [
                    FY=Int64.Type, FY ID=Int64.Type, Date={date}, Week=Int64.Type, FW ID=Int64.Type, StartDate=date, EndDate=date, FY Period=Int64.Type, FP ID=Int64.Type
                ]
            )
        ][t] ),
        r = Table.ExpandListColumn( Table.Combine(t), "Date")
    ][r],
    // Invoke custom function on a list of year numbers
    Source = fxFY( {2020..2025} ), 
    // Use UI to extend this custom date table with CY attributes
    InsertYear = Table.AddColumn(Source, "CY", each Date.Year([StartDate]), Int64.Type)
in
    InsertYear

Answer

The provided code snippet is written in Power Query M, a functional language used in Power BI and Excel for data manipulation and transformations. The code generates a custom date table for fiscal years.

Code Analysis

The code uses a custom function, fxFY, passed with a list of years ({2020..2025}), and finally adds a new column to the produced table called CY that represents the year of the StartDate.

Code Structure

  1. Function Definition: The fxFY function takes a list of years as input and returns a table. It uses several core functions (List.Transform, List.First, List.Repeat, Table.FromColumns, Table.ExpandListColumn, Table.Combine) and also resolves list range elements ({1..52}, {0..51}).

  2. Function Invocation: The function fxFY is invoked with the list of years {2020..2025}.

  3. Column Addition: Table.AddColumn function is used to add a new column named CY, calculated from the StartDate field.

Key Functions
  • List.Transform: Applies a function to each item of the list and returns a list.
  • List.First : Returns the first item of the list.
  • List.Repeat: Repeats a value or a list of values.
  • Table.FromColumns: Creates a table from a list of columns.
  • Table.ExpandListColumn, Table.Combine: Manipulates and merges tables.
  • #date: Creates a date.

The function fxFY creates a list (t) where each year is represented by a table that includes fiscal year columns (like FYs, FYID, Weeks, FWID, FYPeriod, FPID, StartDate, EndDate, Dates). Then, it combines all these tables into one table and expands the column Date as it contains lists of dates.

Finally, the function Table.AddColumn is used to add a new column "CY" to the Source table by extracting the year part of the StartDate column. It uses the Date.Year function to extract the year. The output table will have data for years from 2020 to 2025.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

This Power Query M code snippet utilizes a custom function, 'fxFY', to generate a fiscal year date table from the years 2020 to 2025, adding a 'CY' column based on 'StartDate' year. It employs key functions like List.Transform, Table.FromColumns, and Table.Combine for data manipulation.

More Code Explainers
Apache Flink Code ExplainerApache Pig Code ExplainerAzure Data Factory Code ExplainerC/C++ Code ExplainerCouchDB Code ExplainerDAX Code ExplainerExcel Code ExplainerFirebase Code ExplainerGoogle BigQuery Code ExplainerGoogle Sheets Code ExplainerGraphQL Code ExplainerHive Code ExplainerJava Code ExplainerJavaScript Code ExplainerJulia Code ExplainerLua Code ExplainerM (Power Query) Code ExplainerMATLAB Code ExplainerMongoDB Code ExplainerOracle Code ExplainerPostgreSQL Code ExplainerPower BI Code ExplainerPython Code ExplainerR Code ExplainerRedis Code ExplainerRegex Code ExplainerRuby Code ExplainerSAS Code ExplainerScala Code ExplainerShell Code ExplainerSPSS Code ExplainerSQL Code ExplainerSQLite Code ExplainerStata Code ExplainerTableau Code ExplainerVBA Code Explainer