date list

[Anonymous]

hi every one,

I need help.

I need to list date form  2 datas based on below tables.

I have data in table  one  and I  need resulte the data in table 2

[TomMartens]

Hey,

there are two possibilities.

Assuming your data looks like this:

In Power Query just, create a new custom column:

Enter this formula, please be aware that the column names might not match:

List.Dates([Start], Number.From([End]) - Number.From([Start]) + 1, #duration(1 , 0 , 0 , 0))

Then the final move expand the list to new rows:

And voila:

The DAX solution looks like this, create a new table :

Before you can create your new table it's necessary to create a dedicated Calendar table like so (using DAX):

Calendar = 
CALENDAR("2019-01-01", "2019-12-31")

Then you create this DAX statement to create your new table, please be aware, that the name of the table that contains the Start and End column is called 'Table2':

the new table = 
GENERATE(
    'Table2'
    , DATESBETWEEN('Calendar'[Date] , [Start] , [End])
) 

The result fo course is the same 🙂

My recommendation, use the Power Query approach.

Regards,

Tom

[Anonymous]

thanks @TomMartens , 

i tried from query editor , but show error based on below image

[TomMartens · Accepted solution]

Hey,

hit one of the Error values and provide the error information that you receive.

Regards,

Tom

[Anonymous]

thanks @TomMartens ,

can you tell  me  how we can optimize data because I have many records after apply your solution and there is a delay to apply data to the data model.

Regards,