Filtering out uncomplete process data

[id_flk]

Hi all,

I’m trying to create a better filter for our factory’s dashboard. But I’m facing an issue:

Oversimplifying,

Source:

Date	Cell	Process
9/14/2022	A	W
9/14/2022	A	X
9/14/2022	B	W
9/14/2022	B	X
9/14/2022	B	Y
9/14/2022	C	X
9/14/2022	C	Y
9/14/2022	C	Z

Different cells, have different processes.

This is a summary of the cells’ process requirements. 1 = it is required to run that process. 0 = it does not.

Cell	W	X	Y	Z
A	1	1	0	0
B	1	1	1	1
C	0	1	1	1

I need a filter that

• Removes B – since B requires process Z but wasn’t in the record.
• Keeps A and C – since they completed all required processes

Does anyone know how to do this in PowerBI?

[v-jingzhang]

Hi @id_flk 

Here is my method with Power Query. Hope it helps. 

1. In Requirement table, select Cell column and unpivot other columns. You will have the following result. 

2. In Data table, click Merge Queries to merge Requirement table to Data table. Select Cell and Process as the matching columns (hold on Ctrl key to select multiple columns). And select Right Outer join kind. 

 You will see a null row. It contains all processes that are not present in Data table. 

3. Expand Requirement column, then add two custom filtering steps based on the expanded result.  

    #"Filtered Rows Not Completed" = Table.SelectRows(#"Expanded Requirement", each ([Requirement.Value] = 1) and ([Process] = null)),
    #"Filtered Rows Completed" = Table.SelectRows(#"Expanded Requirement", each ([Requirement.Value] = 1) and not List.Contains(#"Filtered Rows Not Completed"[Requirement.Cell], [Requirement.Cell])),

4. At last, remove unnecessary columns. You will see filtered result as below. 

The sample file is attached at bottom. You can see complete steps and code in it. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[id_flk]

Hi v-jingzhang,

Thank you so much for getting back! I tried that today and found that this part takes really long to load and was wondering if you have any workaround that. 

#"Filtered Rows Completed" = Table.SelectRows(#"Expanded Requirement", each ([Requirement.Value] = 1) and not List.Contains(#"Filtered Rows Not Completed"[Requirement.Cell], [Requirement.Cell])),

But otherwise, everything works great.

Thank you!

-Ivan

[v-jingzhang · Accepted solution]

Hi @id_flk 

I made some changes to the code. This may be faster. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3NNE3MjAyUtJRcgTicKVYHUzRCAxRJ6xqnXCqjcQQdcaq1hmn2iil2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Cell = _t, Process = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Cell", type text}, {"Process", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cell", "Process"}, Requirement, {"Cell", "Attribute"}, "Requirement", JoinKind.RightOuter),
    #"Expanded Requirement" = Table.ExpandTableColumn(#"Merged Queries", "Requirement", {"Cell", "Attribute", "Value"}, {"Requirement.Cell", "Requirement.Attribute", "Requirement.Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Requirement", each ([Requirement.Value] = 1)),
    #"All Cells List" = List.Distinct(#"Filtered Rows"[Requirement.Cell]),
    #"Not Completed Cells List" = List.Distinct(Table.SelectRows(#"Filtered Rows", each ([Process] = null))[Requirement.Cell]),
    #"Completed Cells List" = List.Difference(#"All Cells List", #"Not Completed Cells List"),
    #"Filtered Rows Completed" = Table.SelectRows(#"Filtered Rows", each List.Contains(#"Completed Cells List", [Requirement.Cell])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows Completed",{"Requirement.Cell", "Requirement.Attribute", "Requirement.Value"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.