Difference in Months - Columns

[henrys1995]

Hi all,

How can Power Query give the below required output:

Source Data:

Employee ID             Employee Name               Start Date                End Date

2321                          John Smith                       01/05/23                  31/12/23

Required Output:

Employee ID    Employee Name     Jan23  Feb23  Mar23  Apr23  May23  Jun23  Jul23  Aug23  Sep23  Oct23  Nov23  Dec23

2321                 John Smith                                                             1          1         1         1          1           1         1           1

[Joe_Barry]

Hi @henrys1995 

Is the required out what you want to visualise after loading the data? If so try this.

Leave the data as it is and load it in. Are you using a Date Dimension Table? Then Try this

Hope it helps

Joe

Employee RT = 
VAR _MaxDate = MAX('Date'[Date])

RETURN

CALCULATE(
    DISTINCTCOUNT('Table'[Employee ID]),
    KEEPFILTERS('Table'[Start Date] <= _MaxDate
    && 'Table'[End Date] > _MaxDate),
    ALL('Date'))

[jennratten]

Hello - this is how you can do it in Power Query. Basically you would add a column which contains a list of months from the start to the end date, expand the list to rows and pivot it to columns.

Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTI0MjYBUqb6hvpGBkbGYBF9YygnNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, ID = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"ID", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform ( List.Select ( List.Dates([Start Date], Number.From ( [End Date] ) - Number.From ( [Start Date] ),#duration(1,0,0,0)), each Date.Day ( _ ) = 1 ), each Date.ToText ( _, "MMMyy" ) ) ),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Employee", "ID", "Custom"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Other Columns", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Other Columns", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Employee", List.Count)
in
    #"Pivoted Column"

Result

FYI, when needing to show dates in columns instead of rows there is usually a better way of designing the solution, such as using a data table in the model.  

[henrys1995]

@jennratten thanks - is it possible have instead of MMMyy - have the first of each month so 01/01/24 as a date

[jennratten · Accepted solution]

Hi @henrys1995 - yes, absolutely.  You can modify this part of the script to customize the format of the date.