cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ugurgulluev Regular Visitor
Regular Visitor

Transforming the date range

Hi all,

 

I have a tough task at hand (at least for me).

 

I have a report as following, showing the changes in the organisation:

 

Attribute:employeeIDStart DateLine Manager
101.01.2018Alex
104.01.2018Sarah
109.01.2018John

 

That means the employee 1 was under the supevision of Alex between 01.01.2018 - 03.01.2018. As of 04-01.2018, the employee's depatment changed, and the supervisor became Sarah. Then, a change occured again and the supervisor became John as of 09.01.2018. Since there is no change in the list since then, we know that the current line manager of the employee is John.

 

Considering the information above, is it somehow possible to generate a report that is updated with the day's information each day? Like the following structure:

 

Attribute:employeeIDStart DateLine Manager
101/01/2018Alex
102/01/2018Alex
103/01/2018Alex
104/01/2018Sarah
105/01/2018Sarah
106/01/2018Sarah
107/01/2018Sarah
108/01/2018Sarah
109/01/2018John
110/01/2018John

 

 

Appreciate your support on this.

 

Best regards,

Ugur

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Transforming the date range

@ugurgulluev

 

With Power Query/ Query Editor...you can do transformation like this

 

Please see the attached file for the steps in Query Editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLNz0tJrNRR8ErMK00sqlQw1FEwMjC0AMo45qRWKMXqQJSFZJQWFaMoNIErDE4sSsxAqCxNRVVoCVfolZ+RB1ZnhM1iU7i6CLgiTGsNLeDKInGaZYSwM0opNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Attribute:employeeID" = _t, #"Start Date" = _t, #"Line Manager" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:employeeID", Int64.Type}, {"Start Date", type date}, {"Line Manager", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Attribute:employeeID"}, {{"ALL", each Table.AddIndexColumn(Table.Sort(_,{{"Start Date", Order.Ascending}}),"Index",1,1), type table}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Attribute:employeeID", "Start Date", "Line Manager", "Index"}, {"Attribute:employeeID.1", "Start Date", "Line Manager", "Index"}),
    RemovedColumns = Table.RemoveColumns(#"Expanded ALL",{"Attribute:employeeID.1"}),
    #"Added Custom" = Table.AddColumn(RemovedColumns, "End Date", each let 
myindex=[Index]+1, 
myemployee=[#"Attribute:employeeID"] 
in 
try
Date.AddDays( Table.SelectRows(RemovedColumns, each [#"Attribute:employeeID"]=myemployee and [Index]=myindex)[Start Date]{0},-1)
otherwise
[Start Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Final Dates", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Final Dates" = Table.ExpandListColumn(#"Added Custom1", "Final Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Final Dates",{{"Final Dates", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Start Date", "Index", "End Date"})
in
    #"Removed Columns"
Community Support Team
Community Support Team

Re: Transforming the date range

hi, @ugurgulluev @sturlaws

sturlaws's method is a good method for your case, but there is one change to be made,

First create a 'End Date'-column in your employee table:

EndDate =
VAR ed =
    CALCULATE (
        MIN ( Table1[StartDate] );
        FILTER (
            'Table1';
            Table1[StartDate] > EARLIER ( Table1[StartDate] )
                && [eID] = EARLIER ( Table1[eID] )
        )
    )
RETURN
    IF ( ISBLANK ( ed ); DATE ( 9999; 12; 31 ); ed )

 

Then create a new table, in the power bi modell, assuming you have a Date table:

NewTable =
FILTER (
    CROSSJOIN ( 'Employee'; 'Date' );
    'Employee'[StartDate] <= 'Date'[Date]
        && 'Employee'[EndDate] >= 'Date'[Date]
)

 

In there, it shouldn't use ">=", just ues ">" like 

NewTable =
FILTER (
    CROSSJOIN ( 'Employee'; 'Date' );
    'Employee'[StartDate] <= 'Date'[Date]
        && 'Employee'[EndDate] > 'Date'[Date]
)

Otherwise, it will lead to duplicate data.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Transforming the date range

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:employeeID", Int64.Type}, {"Start Date", type date}, {"Line Manager", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"try" = Table.AddColumn(#"Added Index", "custom column", each #"Added Index"{[Index]}[Start Date], type date),
    #"Removed Columns" = Table.RemoveColumns(#"try",{"Index"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each try Number.From([custom column])-1 otherwise DateTime.Date(DateTime.LocalNow())),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom", {{"Custom", type date}}, "en-IN"),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type with Locale",{"custom column"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom.1", each {Number.From([Start Date])..Number.From([Custom])}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom.1",{"Start Date", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom.1", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Attribute:employeeID", "Date", "Line Manager"}),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Reordered Columns", {{"Date", type date}}, "en-IN")
in
    #"Changed Type with Locale1"
4 REPLIES 4
sturlaws Regular Visitor
Regular Visitor

Re: Transforming the date range

Hi Ugur,

 

here is one way this could be achived:

 

First create a 'End Date'-column in your employee table:

EndDate =
VAR ed =
    CALCULATE (
        MIN ( Table1[StartDate] );
        FILTER (
            'Table1';
            Table1[StartDate] > EARLIER ( Table1[StartDate] )
                && [eID] = EARLIER ( Table1[eID] )
        )
    )
RETURN
    IF ( ISBLANK ( ed ); DATE ( 9999; 12; 31 ); ed )

 

Then create a new table, in the power bi modell, assuming you have a Date table:

NewTable =
FILTER (
    CROSSJOIN ( 'Employee'; 'Date' );
    'Employee'[StartDate] <= 'Date'[Date]
        && 'Employee'[EndDate] >= 'Date'[Date]
)

 

This newTable will give you the table you require.

 

Best regards,

Sturla

Highlighted
Super User
Super User

Re: Transforming the date range

@ugurgulluev

 

With Power Query/ Query Editor...you can do transformation like this

 

Please see the attached file for the steps in Query Editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLNz0tJrNRR8ErMK00sqlQw1FEwMjC0AMo45qRWKMXqQJSFZJQWFaMoNIErDE4sSsxAqCxNRVVoCVfolZ+RB1ZnhM1iU7i6CLgiTGsNLeDKInGaZYSwM0opNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Attribute:employeeID" = _t, #"Start Date" = _t, #"Line Manager" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:employeeID", Int64.Type}, {"Start Date", type date}, {"Line Manager", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Attribute:employeeID"}, {{"ALL", each Table.AddIndexColumn(Table.Sort(_,{{"Start Date", Order.Ascending}}),"Index",1,1), type table}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Attribute:employeeID", "Start Date", "Line Manager", "Index"}, {"Attribute:employeeID.1", "Start Date", "Line Manager", "Index"}),
    RemovedColumns = Table.RemoveColumns(#"Expanded ALL",{"Attribute:employeeID.1"}),
    #"Added Custom" = Table.AddColumn(RemovedColumns, "End Date", each let 
myindex=[Index]+1, 
myemployee=[#"Attribute:employeeID"] 
in 
try
Date.AddDays( Table.SelectRows(RemovedColumns, each [#"Attribute:employeeID"]=myemployee and [Index]=myindex)[Start Date]{0},-1)
otherwise
[Start Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Final Dates", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Final Dates" = Table.ExpandListColumn(#"Added Custom1", "Final Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Final Dates",{{"Final Dates", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Start Date", "Index", "End Date"})
in
    #"Removed Columns"
Community Support Team
Community Support Team

Re: Transforming the date range

hi, @ugurgulluev @sturlaws

sturlaws's method is a good method for your case, but there is one change to be made,

First create a 'End Date'-column in your employee table:

EndDate =
VAR ed =
    CALCULATE (
        MIN ( Table1[StartDate] );
        FILTER (
            'Table1';
            Table1[StartDate] > EARLIER ( Table1[StartDate] )
                && [eID] = EARLIER ( Table1[eID] )
        )
    )
RETURN
    IF ( ISBLANK ( ed ); DATE ( 9999; 12; 31 ); ed )

 

Then create a new table, in the power bi modell, assuming you have a Date table:

NewTable =
FILTER (
    CROSSJOIN ( 'Employee'; 'Date' );
    'Employee'[StartDate] <= 'Date'[Date]
        && 'Employee'[EndDate] >= 'Date'[Date]
)

 

In there, it shouldn't use ">=", just ues ">" like 

NewTable =
FILTER (
    CROSSJOIN ( 'Employee'; 'Date' );
    'Employee'[StartDate] <= 'Date'[Date]
        && 'Employee'[EndDate] > 'Date'[Date]
)

Otherwise, it will lead to duplicate data.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Transforming the date range

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:employeeID", Int64.Type}, {"Start Date", type date}, {"Line Manager", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"try" = Table.AddColumn(#"Added Index", "custom column", each #"Added Index"{[Index]}[Start Date], type date),
    #"Removed Columns" = Table.RemoveColumns(#"try",{"Index"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each try Number.From([custom column])-1 otherwise DateTime.Date(DateTime.LocalNow())),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom", {{"Custom", type date}}, "en-IN"),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type with Locale",{"custom column"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom.1", each {Number.From([Start Date])..Number.From([Custom])}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom.1",{"Start Date", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom.1", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Attribute:employeeID", "Date", "Line Manager"}),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Reordered Columns", {{"Date", type date}}, "en-IN")
in
    #"Changed Type with Locale1"