Project ID wise Status (In Query Editor)

[rohitpoojari]

Dear All,

I have been unable to find the solution trying for 4-5 weeks, really appreciate it if someone could help me, below is an example of the dataset & expected out. I am trying to achieve as shown in the "Expected Output" Table in Query Editor.

Priority of status

• Off Track  - This will be 1st priority, if any project ID has "Off Track" as the status then the rest of the row with a different status for that particular Project ID shall get null.
• On Track – This will be 2nd priority, if any project ID doesn’t have "Off Track" but has "On Track" & "Not Applicable" as statuses in such the record for "On Track" shall get captured & the rest of the row for that particular ID shall get null.
• Not Applicable - This will be the last priority, if any project ID only has "Not Applicable" as its status then the status for that particular project ID shall get captured as "Not Applicable" and the rest of the row for that ID shall get null.

For a specific reason, I am trying to achieve this in Power Query (Query Editor) only 

[PavanLalwani · Accepted solution]

In Power Query, you can achieve this prioritized filtering by creating custom steps that apply conditional logic to set the Record Status for each Project ID based on priority. Here’s how to do it step-by-step in Power Query Editor:

Step-by-Step Solution

1. Load the Data into Power Query:

   • Load your dataset into Power Query by selecting the table and going to Transform Data.

2. Group Rows by Project ID and Identify the Priority Status:

   • Go to Home > Group By.
   • Group by Project ID, and add a new aggregation column called PriorityStatus using the All Rows option. This will create a nested table with all rows for each Project ID.

3. Add a Custom Column to Determine the Priority Status for Each Project ID:

   • Click on Add Column > Custom Column and enter the following code to determine the highest priority status for each project based on your rules:

     M

     Copy code

     = Table.AddColumn(#"Grouped Rows", "PriorityStatus", each if List.ContainsAny(Table.Column([All Rows], "Record Status"), {"Off Track"}) then "Off Track" else if List.ContainsAny(Table.Column([All Rows], "Record Status"), {"On Track"}) then "On Track" else "Not Applicable" )

   • This PriorityStatus column checks if "Off Track" exists in any row for the Project ID, if not, checks for "On Track", and defaults to "Not Applicable" if neither is present.

4. Expand the Table Back to Original Format:

   • Now, expand the All Rows column to return the table back to its original form.
   • Click on the expand icon next to the All Rows column, uncheck any unwanted columns, and expand Product Name and Record Status columns.

5. Add Conditional Column to Nullify Non-Priority Rows:

   • Go to Add Column > Conditional Column to create a new column Filtered Status where only rows with the priority status are retained. Use this formula in the conditional column:

     • If [Record Status] equals [PriorityStatus], then [Record Status].
     • Otherwise, null.

6. Remove the Original Record Status Column:

   • Go to Home > Remove Columns and delete the original Record Status column, keeping only the Filtered Status column.

7. Rename Filtered Status to Record Status:

   • Rename the Filtered Status column back to Record Status to match your expected output.

8. Finalize and Load Data:

   • Click Close & Load to load your transformed data back into Power BI with the correct priority status.

Summary of Transformation

This approach groups by Project ID, calculates the highest-priority Record Status for each project, expands the data back to its original form, and uses conditional logic to set all lower-priority rows to null. This will give you the expected output where only the highest-priority status is retained per Project ID.

This should help you achieve the prioritized filtering in Power Query!

If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!

[rohitpoojari]

@PavanLalwani , Thanks for the detailed solution it works perfectly ......Thanks 

[wini_R]

Hi @rohitpoojari,

That is probably not the most elegant and simplest solution, but hopefully it works as expected. Please paste the code below into advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjx1TVU0lFydHIGkv55CiFFicnZSrE66FJ++SUKjgUFOZnJiUk5qVgU+KelIWn2c3fXNQKKRkRGYdeMqoBEzT4+PrrGQClfP39iFKB4i6BeX19fXROgVGBQMLpePFJRUVG6pkDB0LBw7MbiVxALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Product Name" = _t, #"Record Status" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Project ID", "Product Name"}, {{"all", each Table.AddIndexColumn(_, "index", 1, 1), type table [Project ID=text, Product Name=text, Record Status=text]}}),
    #"Added Status New" = Table.AddColumn(#"Grouped Rows", "Record Status New", each if List.Contains([all][Record Status], "Off Track") then "Off Track" else if List.Contains([all][Record Status], "On Track") then "On Track" else "Not Applicable"),
    #"Added cnt" = Table.AddColumn(#"Added Status New", "cnt", each Table.RowCount(Table.SelectRows([all], (i) => i[Record Status] = [Record Status New]))),
    #"Expanded all" = Table.ExpandTableColumn(#"Added cnt", "all", {"Record Status", "index"}, {"Record Status", "index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded all", each [Record Status New], each if [cnt] = 1 then (if [Record Status New] = [Record Status] then [Record Status New] else null) else (if [Record Status New] = [Record Status] and [index] = [cnt] then [Record Status New] else null), Replacer.ReplaceValue, {"Record Status New"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Project ID", "Product Name", "Record Status New"})
in
    #"Removed Other Columns"

Input and output tables:

[rohitpoojari]

@wini_R , Thanks for your quick input but it doesn't seem to be working ... I am still working on it to alter it and check ..but prima facie it's not working for a particular Project ID ...status is still getting repeated again and again

[wini_R]

What is specific about that project where it does not work properly? Can you please provide an example?

[rohitpoojari]

@wini_R , I guess doing something wrong, due to some reason status for all the project IDs is getting repeated also the rows are getting duplicated, trying to figure it out. 

let
    Source = SharePoint.Files("https://lupinworld.sharepoint.com/sites/DataFlow-Testing", [ApiVersion = 15]),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Product Name", type text}, {"DP Site", type text}, {"Market", type text}, {"Doc Type", type text}, {"Part", type text}, {"Standard Targets from T-0 date", Int64.Type}, {"Actual Due Dates", type date}, {"Actual data for receipt", type date}, {"Documents Status (Select from Drop-down list)", type text}, {"Documents Track Record ", type text}, {"Reason/Remark (Incase- Off-Track)", type any}, {"Responsibility#(lf)(Dept)", type any}, {"Remark ", type date}}),
    #"Appended Query" = Table.Combine({#"Changed Type", NGP, GOA}),
    #"Added Custom" = Table.AddColumn(#"Appended Query", "On Track", each if[#"Documents Track Record "]="On Track" then "1" else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Off Track", each if [#"Documents Track Record "] = "Off Track" then "1" else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Not Applicable", each if [#"Documents Track Record "]= "Not Applicable" then 1 else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom2", "Documents Track Record (Number)", each if [#"Documents Track Record "] = "On Track" then 1 else if [#"Documents Track Record "] = "Off Track" then 1 else if [#"Documents Track Record "] = "Not Applicable" then 1 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "On Track (Standalone)", each if [#"Documents Track Record "] = "On Track" then [#"Documents Track Record "] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Off Track (Standalone)", each if [#"Documents Track Record "] = "Off Track" then [#"Documents Track Record "] else null),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Not Applicable (Standalone)", each if [#"Documents Track Record "] = "Not Applicable" then [#"Documents Track Record "] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column3", each ([Doc Type] <> "T0 Date")),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "DP Site", "DP Site - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 1, 1, Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Index", "Prev Row", each [Index]-1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom3", {"Prev Row"}, #"Added Custom3", {"Index"}, "Added Custom3", JoinKind.LeftOuter),
    #"Expanded Added Custom3" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom3", {"Product Name"}, {"Added Custom3.Product Name"}),
    #"Added Conditional Column4" = Table.AddColumn(#"Expanded Added Custom3", "Product Name (Non Duplicate)", each if [Added Custom3.Product Name] = [Product Name] then null else [Product Name]),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Added Conditional Column4", "Product Name", "Product Name - Copy"),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column1", "Source.Name", "Source.Name - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column2",{{"Documents Track Record ", "Documents Track Record"}}),
    #"Added Custom4" = Table.AddColumn(#"Renamed Columns", "Custom", each let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjx1TVU0lFydHIGkv55CiFFicnZSrE66FJ++SUKjgUFOZnJiUk5qVgU+KelIWn2c3fXNQKKRkRGYdeMqoBEzT4+PrrGQClfP39iFKB4i6BeX19fXROgVGBQMLpePFJRUVG6pkDB0LBw7MbiVxALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source.Name"=_t, #"Product Name" =_t, #"Documents Track Record"=_t]),
    #"Grouped Rows" = Table.Group(Source, {"Source.Name", "Product Name"}, {{"all", each Table.AddIndexColumn(_, "index", 1, 1), type table [Source.Name=text,Product Name=text,Documents Track Record=text]}}),
    #"Added Status New" = Table.AddColumn(#"Grouped Rows", "Record Status New", each if List.Contains([all][Documents Track Record], "Off Track") then "Off Track" else if List.Contains([all][ Documents Track Record], "On Track") then "On Track" else "Not Applicable"),
    #"Added cnt" = Table.AddColumn(#"Added Status New", "cnt", each Table.RowCount(Table.SelectRows([all], (i) => i[Documents Track Record] = [Record Status New]))),
    #"Expanded all" = Table.ExpandTableColumn(#"Added cnt", "all", {"Documents Track Record", "index"}, {"Documents Track Record", "index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded all", each [Record Status New], each if [cnt] = 1 then (if [Record Status New] = [Documents Track Record] then [Record Status New] else null) else (if [Record Status New] = [Documents Track Record] and [index] = [cnt] then [Record Status New] else null), Replacer.ReplaceValue, {"Record Status New"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Source.Name", "Product Name", "Record Status New"})
in
    #"Removed Other Columns"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom4", "Custom", {"Record Status New"}, {"Custom.Record Status New"})
in
    #"Expanded Custom"

[wini_R]

Hi @rohitpoojari,

I can see from the code above that you have just pasted my example solution into your query but this would never work. My example is based on simplified data that you provided in the initial post, you need to adjust it to your real-world dataset. It was only meant to show the logic how your challenge can be solved.

If you don't know how to use in your original query, I could help with that but I will need an extract of your data.

[rohitpoojari]

@wini_R Yes that will be helpful, Below is the extract of my data

PBIX 

[wini_R]

@rohitpoojari, unfortunately I'm not able to download the file from Sharepoint folder

[rohitpoojari]

@wini_R , thanks for your help