Forum Discussion

henrys1995's avatar
henrys1995
Regular Visitor
2 years ago
Solved

Difference in Months - Columns

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



Need Help
Show and Tell
Tutorial Requests
  • jennratten's avatar
    jennratten
    2 years ago

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

     

4 Replies

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    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's avatar
      henrys1995
      Regular Visitor
      2 years ago

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

      • jennratten's avatar
        jennratten
        Super User
        2 years ago

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

         

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    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'))