Power Query

[Shay A]

Hi,
Is it possible to ask qustions about Power Query? (Including M)

TY

 

[Chihiro]

Yes, ask away.

Can't guarantee an answer but will certainly attempt to answer your query.

 

[Shay A]

Hi,
In the attached file I would like to add a column that will allow me to expand each end of quarter to every single date. I tried to use List.Dates in combination with #duration but I got an error.

TY

 

[GraH - Guido]

if you expand all dates for each quarter, you just end up with all dates of the year, no?
Would this work?

 

[Chihiro]

Here's how I would do it. All done in PowerQuery, M function.
1. Create StDate parameter. This should be Jan 1, of a given year.
2. Create new blank query. Use following function as source. If you need more than single year, change +1 to some other number.

= List.Dates(StDate, Duration.Days(Duration.From(#date(Date.Year(StDate)+1,1,1)-StDate)), #duration(1,0,0,0))

3. Convert resulting list to table.
4. Add other columns (such as Year, Month, Weekday etc) as needed.

See attached.

Complete M of sample.

let
    Source = List.Dates(StDate, Duration.Days(Duration.From(#date(Date.Year(StDate)+1,1,1)-StDate)), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Year", each Date.Year([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Column1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "OrdinalDate", each Duration.Days(Duration.From([Column1]-StDate+#duration(1,0,0,0)))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Weekday", each Date.ToText([Column1],"ddd","en-US")),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Quarter", each "Q" & Number.ToText(Number.RoundUp([Month]/3))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom5",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"OrdinalDate", Int64.Type}, {"Weekday", type text}, {"Quarter", type text}})
in
    #"Changed Type"

If you want to, you could replace parameter with calculation to dynamically create dimension table from your source data.

Edit: Alternately, you could just create list of dates. Then load it to data model and create all other columns in DAX.

 

[Shay A]

Thank you both! I thought that it was the List(Start)..List(End) only if we actually drill doen the values of year start and year and.

Thank you again.

 

[GraH - Guido]

Here's how I would do it. All done in PowerQuery, M function.
1. Create StDate parameter. This should be Jan 1, of a given year.
2. Create new blank query. Use following function as source. If you need more than single year, change +1 to some other number.

= List.Dates(StDate, Duration.Days(Duration.From(#date(Date.Year(StDate)+1,1,1)-StDate)), #duration(1,0,0,0))

3. Convert resulting list to table.
4. Add other columns (such as Year, Month, Weekday etc) as needed.

Haha, thank you Ken Puls? Now for a starting user of PQ the first step might be called "difficult". But, this is how you use PQ's - d'oh - POWER.
Furthermore it is like incredible easy to translate the name of months, weekdays in many languages. Just click those damn' buttons.

 

[Chihiro]

Nah, Chris Webb. From his blog back in 2013.
I first learned List function use from there

Though in most cases, since I use PowerBI for large data set and/or time intelligence, I create dynamic date dimension table in DAX.

 

[GraH - Guido]

since I use PowerBI for large data set and/or time intelligence

Now I'm sooo jealous.

 

[Chihiro]

Just spend time and build use case scenario and do cost/benefit analysis for your boss. Took me a bit of work, but got him convinced it was good investment. And price is reasonable for small group or large company. Mid-size price point is rather lacking though.