How to create in Power-BI list date range from two dates?

Table of Contents

    The context: Power BI creates a list date range from two dates.

    In this case, the table on the left represents different periods of analysis in yo company. The first column has the financial year. You already saw that this company doesn`t work with a traditional 12 months. You will note that the second column “End Date” corresponds to the final of each period and the third column has a start period.
    Another observation, the first period starts on April 1 fo 2015 and Ends on 2 May. Look up the 5 lines – the 5 period starts on 26 July and ends on 22 August. We need to expand this table to create dates between these different ways. The target is to create a Power-BI list date range from two dates.

The original table to extract list date

    I. What is the Number.From function:

    In Power Query, a powerful data transformation tool offered by Microsoft Excel and Power BI, there are various functions that allow efficient manipulation and transformation of information to obtain Power-BI list date range. One of these functions is Number.From, which plays a fundamental role in converting data into numbers. In this article, we will explore in detail how the Number.From function can be used to convert different types of data into numbers, providing greater flexibility and accuracy in your analyses.

    The Number.From function is a native function in Power Query that aims to convert values into numbers. It is especially useful when dealing with data imported from external sources or in non-numeric formats, such as text, dates, or currencies.

    II. Syntax of the Number.From function:

    The basic syntax of the Number.From function is as follows:
    – Function: Number.From(value)
    In the event, that “value” parameter represents the data that you want to convert into a number. This value can be a column in a table, a specific cell, or even a constant.

    III. Using the Number.From function:

    The Number.From function is extremely versatile and can be applied in various situations. Let’s look at some practical examples of how to use it:

    a. Converting text into numbers:

    Suppose you have a column called “Value” in a table that contains numeric values stored as text. To convert these values into numbers, you can use the Number.From function as follows:

    =  Table.TransformColumnTypes(Table,{{"Value", Number.From}})

    Note: This expression will transform the “Value” column into numbers, allowing you to perform mathematical calculations and analyses more accurately.

    b. Converting dates into numbers:

    Converting dates into numbers:
    In certain situations, it may be necessary to convert dates into numbers for comparisons or specific calculations. By using the Number.From function, you can easily make this conversion. For example:

    = Table.AddColumn(Table, "NumericDate", each Number.From([Date]))

    Note: This formula will add a new column called “NumericDate” to the table, containing the numeric values corresponding to the dates in the “Date” column.

    c. Calculating the difference liste date range between two dates:

    Suppose you have a table with two columns, “StartDate” and “EndDate”, containing dates in text format. To calculate the difference in days between these two dates, you can use the Number.From function combined with other Power Query functions. Here’s an example:

    = Table.AddColumn(Table, "DaysDifference", each Number.From([EndDate]) - Number.From([StartDate]))

    In this case, the Number.From function is used to convert the dates into numbers, allowing the subtraction operation to calculate the difference in days.

    IV. The last tip is List.Distinct or {…} function:

    a. Create a simple list range:

    In Power Query, you can use the List.Distinct function to create a new list that contains only the unique values from an existing list. Here’s how you can use it with the list {1, 2, 3, 4, 5}:

    1. Open Power Query Editor by clicking on the “Edit Queries” button in the Power Query ribbon.
    2. Go to the “Add Column” tab in the Power Query Editor ribbon and click on the “Custom Column” button. This will open a dialogue box where you can enter a custom formula.
    3. In the “Custom Column” dialogue box, enter a name for the new column (e.g., “List”) in the “New column name” field.
    4. In the “Custom column formula” field, enter the following formula: List.Distinct({1, 2, 3, 4, 5}). Replace {1, 2, 3, 4, 5} with the name of your actual list if it’s different.
    5. Click the “OK” button to create the new column.
    Add new column list

    b. Expand the list date range:

    Power Query will apply the formula and create a new column with the unique values from the original list. You can then further manipulate or use this new column in your data transformation process.
    That’s how you can use the List.Distinct function in Power Query to create a new list with unique values.

    Expand column list date range

    IV. Finally, create a list date range from between dates :

    As demonstrated in the picture on the right or with more evidence in the function below. It starts with a list “{…}” inside its function Number.from get the first field date and after double dots”..” immediately after this repeat the function number.from but now get the field end date. To confirm click on the ok button. The column will be named “List Date”

    Expand column list date range

    Now, select the opposite narrow and Expand to new rows.

    Expand column list by rows

    Change the type number to the data type.

    List dates transform in numbers

    Whatever, we are our Power-BI list date range. In the picture on the right, The list will be expanded adding each row by the interval date.
    In order the details the explanation we will start with the first line in the column Start and End.

    These fields have the interval series date from 01/04/2015 until 02/05/2015 and these values will be repeated till line 32, in the next line will start the second interval date.

    In the new (4) column named List date, the first rows fell with the first date of the interval date from 01/04/2015 progressively fell rows by rows until the last date to the interval series 02/05/2015.

    Complete list date range from two dates.

    Additional considerations to create Power-BI list date range:

    In the final analysis, It’s important to note that the Number.From function may generate errors if the provided value cannot be converted into a number. Therefore, it’s necessary to ensure that the data is correct and in an appropriate format before performing the conversion.

    These functions are very important to create Power-BI list date range. This supposes in your company you use specific periods to analyze data when you have gaps in periods.

    To know how to create and why to create a date table see our post about this topic. click here.

    Artifical inteligence Power Apps Power Automate Power BI Power Platform Connector UX design

    Give your opnion about this article:

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Generic selectors
    Exact matches only
    Search in title
    Search in content
    Post Type Selectors