Transforming the date range

[ugurgulluev]

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:employeeID	Start Date	Line Manager
1	01.01.2018	Alex
1	04.01.2018	Sarah
1	09.01.2018	John

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:employeeID	Start Date	Line Manager
1	01/01/2018	Alex
1	02/01/2018	Alex
1	03/01/2018	Alex
1	04/01/2018	Sarah
1	05/01/2018	Sarah
1	06/01/2018	Sarah
1	07/01/2018	Sarah
1	08/01/2018	Sarah
1	09/01/2018	John
1	10/01/2018	John

Appreciate your support on this.

Best regards,

Ugur

[Zubair_Muhammad · Accepted solution]

@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"

[v-lili6-msft · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

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"

[v-lili6-msft · Accepted solution]

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

[sturlaws]

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

[Zubair_Muhammad · Accepted solution]

@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"