Creating a Filtered Table with Multiple Criteria and Where Rows are Date Dependent

[Casey_Alderson]

Hello all, 

Thanks in advance for your time to read and brainstorm with me. I'm attempting to take employee data that identifies tags for how an employee's cost is allocated and pull out specific changes. There is an example below simplified to be for just one employee (if we can determine the solution for one i think the extra build to relate by employee should be relatively simple). Rows are generated when any number of specific data points are changed regarding an Employee's allocation settings. Costing Allocation Start Date and Costing Allocation End Date are the effective dates of new and old settings. I want to create a solution that filters the primary table to only include specific data change triggers. In my use case if Project, Distribution Percent, and/or Service Line change I want to retain the impacted rows. But if there are records where other indicators may have changed I don't need those changes (example bottom 4 rows show the difference between the two allocations are changing Billing Classification from ADMIN to SERVICES). 

What is possible?

Worker	Supervisory Organization	Service Line	Costing Allocation Start Date	Costing Allocation End Date	Billing/Expense Attribute	Billing Classification	Legacy GL	Project	Region	Costing Company	Distribution Percent
Worker A	Example Org 1	Sales	10/31/2022	6/4/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ001	Gotham	ACME Co.	70%
Worker A	Example Org 1	Sales	10/31/2022	6/4/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ002	US Regional	ACME Co.	10%
Worker A	Example Org 1	Sales	10/31/2022	6/4/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ003	Smallville	ACME Co.	20%
Worker A	Example Org 1	Sales	6/5/2023	8/20/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ004	Smallville	ACME Co.	35%
Worker A	Example Org 1	Sales	6/5/2023	8/20/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ001	Gotham	ACME Co.	50%
Worker A	Example Org 1	Sales	6/5/2023	8/20/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ002	US Regional	ACME Co.	5%
Worker A	Example Org 1	Sales	6/5/2023	8/20/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ003	Smallville	ACME Co.	10%
Worker A	Example Org 1	Sales	8/21/2023	9/7/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ004	Smallville	ACME Co.	50%
Worker A	Example Org 1	Sales	8/21/2023	9/7/2023	Nonbillable	ADMIN	Legacy_GL_Not_Applicable	PRJ001	Gotham	ACME Co.	50%
Worker A	Example Org 1	Sales	9/8/2023	1/21/2024	Nonbillable	SERVICES	Legacy_GL_Not_Applicable	PRJ004	Smallville	ACME Co.	50%
Worker A	Example Org 1	Sales	9/8/2023	1/21/2024	Nonbillable	SERVICES	Legacy_GL_Not_Applicable	PRJ001	Gotham	ACME Co.	50%

[v-xinruzhu-msft · Accepted solution]

Hi @Casey_Alderson 

Based on the data you provided, the project is always changed, so I don't put it to the condition, and based on your description, you want to filter the rows if the Distribution Percent, and/or Service Line changed, then you can refer to the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdRda8IwFAbgvxIKuxOT9MOPy9IVcWg3GqYXIiVK6MpOTaki7t/vBOyFiGViZ65yDvSEh5OXrlbOUtffqiah03PikywrUOS9zgnHXkhQezw5ox6nLnNdbAbUN6WHZaJ3mwJAbkBhF77OpwmeM5XL7U82mWWJPmRhVUGxPX/ykb4xZm6e6MOXLM1QNI9JpPtYDtmLs+5ZAJlLPgVJVV7onYRLFbelMpOilABHnFWXKPfvqAENGscIi8dMfovJC+yYbsUpsLSj1jRZ2lFblu4IOEJ4AxnT4f9l6Y6369TUQZbGdNQY+JnmX3lEnC6mUSyet6auWR1sCn+XTzStfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Worker = _t, #"Supervisory Organization" = _t, #"Service Line" = _t, #"Costing Allocation Start Date" = _t, #"Costing Allocation End Date" = _t, #"Billing/Expense Attribute" = _t, #"Billing Classification" = _t, #"Legacy GL" = _t, Project = _t, Region = _t, #"Costing Company" = _t, #"Distribution Percent" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Worker", type text}, {"Supervisory Organization", type text}, {"Service Line", type text}, {"Costing Allocation Start Date", type date}, {"Costing Allocation End Date", type date}, {"Billing/Expense Attribute", type text}, {"Billing Classification", type text}, {"Legacy GL", type text}, {"Project", type text}, {"Region", type text}, {"Costing Company", type text}, {"Distribution Percent", Percentage.Type}}),

//in group rows, add index column group by the worker and Costing Allocation Start Date column

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Worker", "Costing Allocation Start Date"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),

//expand the table

    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Supervisory Organization", "Service Line", "Costing Allocation End Date", "Billing/Expense Attribute", "Billing Classification", "Legacy GL", "Project", "Region", "Costing Company", "Distribution Percent", "Index"}, {"Supervisory Organization", "Service Line", "Costing Allocation End Date", "Billing/Expense Attribute", "Billing Classification", "Legacy GL", "Project", "Region", "Costing Company", "Distribution Percent", "Index"}),

//in added custom step add the column to compare the current row with the last row that if it meets the condition you have mentioned, use the cuurent index-1 to get the last row, if the current row is different from the last row, then return 1 else 0

    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each let a=Table.SelectRows(#"Expanded Count",(x)=>x[Worker]=[Worker] and x[Costing Allocation Start Date]=[Costing Allocation Start Date]),
b=Table.SelectRows(#"Expanded Count",(x)=>x[Worker]=[Worker] and x[Costing Allocation Start Date]=[Costing Allocation Start Date] and x[Index]=[Index]-1)
in if Table.RowCount(a)=1 or (Table.RowCount(a)>1 and [Index]<>1 and (b[Service Line]{0}<>[Service Line] or b[Distribution Percent]{0}<>[Distribution Percent])) then 1 else 0),

//filter the table that the custom column equal 1

    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Index"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Casey_Alderson]

Thanks Yolo. Any chance you can walk me through what your code is doing? Admittedly this appears way over my head. 

That is the output I would have wanted. It removed rows where the system identified changes but only to columns I didn't care about (in the example Billing Classification). I'm just not able to logically walk myself through your code. 

Thanks again,

Casey

[v-xinruzhu-msft]

Hi @Casey_Alderson 

I have updated the 2th message to add notes at the code, you can refer it.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.