Filter/Compare column values with headers

[slanka]

Mates,

My data is in below structure,

EmpName	StartDate	DateEndate	2015	2016	2017	2018	2019
Emp1	2/25/2018	3/31/2019	1	1	1	1	1
Emp2	4/19/2015	22/12/2018	1	1	1	1	1
Emp3	10/12/2015	15/08/2017	1	1	1	1	1

But, I want to see the the data as below,

EmpName	StartDate	DateEndate	2015	2016	2017	2018	2019
Emp1	2/25/2018	3/31/2019				1	1
Emp2	4/19/2015	22/12/2018	1	1	1	1
Emp3	10/12/2015	15/08/2017	1	1	1

Flag (1) for each employee based on their Year(Min StartDate & Max EndDate). If both Years are same, then Flag should appear only in that respective Year. If StartDate & EndDate are in different Years, then Flag should spread across all the Years.

I am trying to figure out if there is a way to compare column value from StartDate with Header and generate the flag if it matches. Appreciate any help!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[slanka]

Not able to download the file you shared. Can you pls check once.

[Ashish_Mathur]

Hi,

That link is working just fine.

[v-lili6-msft · Accepted solution]

hi, @slanka 

You could try this way in Edit Queries:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tMFTSUTLSNzLVNzIwtACyjfWNDUFsSyDbEAPH6oA1GQE5JvqGliCFpiAJoAlGMBNw6jIGcQz0DY1g2gws9A3BFptj1xYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [EmpName = _t, StartDate = _t, DateEndate = _t, #"2015" = _t, #"2016" = _t, #"2017" = _t, #"2018" = _t, #"2019" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmpName", type text}, {"StartDate", type date}, {"DateEndate", type date}, {"2015", Int64.Type}, {"2016", Int64.Type}, {"2017", Int64.Type}, {"2018", Int64.Type}, {"2019", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"2015", "2016", "2017", "2018", "2019"}),
    #"Inserted Year" = Table.AddColumn(#"Removed Columns", "Year", each Date.Year([StartDate]), Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Year",{{"Year", "StartYear"}}),
    #"Inserted Year1" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([DateEndate]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year", "EndYear"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Year", each List.Buffer({2010..2020})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Year"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "tag", each if [Year] >= [StartYear] and[Year]<=[EndYear] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([tag] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"StartYear", "EndYear"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Year", type text}}, "en-US")[Year]), "Year", "tag", List.Count),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"EmpName", "StartDate", "DateEndate", "2015", "2016", "2017", "2018", "2019"})
in
    #"Reordered Columns"

For example:

and here is pbix file, please try it.

Best Regards,

Lin