Hi Hasnat,

In the attached file I've given the dates the following named ranges:

StartDate

EndDate

1. Create a blank query and used the =Excel.CurrentWorkbook() function to get the two named ranges.

2. Filter the query to get the StartDate only.

3. Expand the table

4. Remove the Name column

5. Change the Data Type to 'Date' and rename the column 'StartDate'

6. Right-click on the cell containing the date > Drill down

7. Give this query a name 'QryStartDate'

8. Repeat the above steps for the End date and call the query 'QryEndDate'

9. Create a new blank query and enter this formula in the formula bar:

= List.Dates(QryStartDate,Number.From(QryEndDate) - Number.From(QryStartDate), #duration(1,0,0,0))

10. Convert it to a Table

11. Rename the column and give it Data Type: Date

12. Close and Load

See example file attached.

Mynda

Thanks Mynda,

Your solution is awesome. Liked it.

I did actually work out what I was after, but I found you solution is much more efficient, hence, I would replace my one.

http://stackoverflow.com/questions/26989279/how-can-i-reference-a-cells-value-in-a-power-query

However, in the above link, I found a fx  "GetValue' that can actually allows to refer any named range in the query which I would like to keep as I am building a template for my company. I have attached my file for your look if possible. I have used this GetValue fx query in my lonnnng steps solution.

I was wondering if I could use the 'StartEnd' named ranges directly (using GetValue fx query) to your Query step 9 and make the solution even simpler?

Would appreciate your comments/solution.

 

## My powerpivot and Power Query ribbons are hiding automatically every time I am closing Excel though Add-ins are checked in. solution please?

 

Thanks again.

Regards,

Hasnat

Hi Hasnat,

The StackOverflow solution is for getting a file path. It does not work with dates.

Your solution, or mine is fine. Many steps doesn't mean inefficient.

Mynda

Hi Hasnat,

You can also use the list generator shortcut of two dots .. with Number.From to generate the list of dates using this M code:

let
    DatesList = { Number.From(QryStartDate)..Number.From(QryEndDate)},
    #"Converted to Table" = Table.FromList(DatesList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Dates"}})
in
    #"Renamed Columns"

 

This is an alternative to the DatesList query M code in the file supplied previously.

Mynda

Thanks Mynda.

It's awesome.

Regards,

Hasnat