Status based on one Category

[mpraka]

Hi All,

I want to achieve this scenario in Query Editor:

I want to create a new column in query editor.

Sample Data:

For each and every name Category "Basic" is very important. If Basic is Fail Status then everything for that particular person should be failed.
If Basic is Pass then everything for that particular person should be Pass
Sample output:

So here if you see - for Prakash the Basic is in Fail status thus all the 3 rows for Prakash is "Fail"
For Sanju if you see - Basic is Pass status so all the rows for Sanju is in Pass Status.

Please help me to create a new column in Transform Data.

Thank you in Advance
Prakash

[HotChilli · Accepted solution]

Duplicate the table (let's call it TableY). Now filter TableY to keep only the Category = Basic rows.

From the original table, do a Merge with TableY on Name (left outer join).

You can then expand only the Status column to return Pass or Fail for each row (as long as there was a Basic category row for the Name).

If there is a Name which does not have any Basic rows, you can add a conditional column to return the status.

[bolfri · Accepted solution]

Custom column code:

= Table.AddColumn(Source, "Basic Status",
    each let currentName = [Name],
    BasicStatus = Table.First(Table.SelectColumns(Table.SelectRows(Source,
        each [Category] = "Basic" and [Name]=currentName),"Status"))[Status]
    in BasicStatus)

Add custom column with Basic Status

All code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKzE4szlDSUXJKLM5MBtJuiZk5SrE6yFK+qSmZpblARkBicTGanEdiUQqyruDEvKxSJOPgWmAScMMgWmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Basic Status",
    each let currentName = [Name],
    BasicStatus = Table.First(Table.SelectColumns(Table.SelectRows(Source,
        each [Category] = "Basic" and [Name]=currentName),"Status"))[Status]
    in BasicStatus)
in
    #"Added Custom"

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Status"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Basic Status", each if _[Basic]="Pass" then "Pass" else "Fail"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Name", "Basic Status"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Hope this helps.

[bolfri · Accepted solution]

Custom column code:

= Table.AddColumn(Source, "Basic Status",
    each let currentName = [Name],
    BasicStatus = Table.First(Table.SelectColumns(Table.SelectRows(Source,
        each [Category] = "Basic" and [Name]=currentName),"Status"))[Status]
    in BasicStatus)

Add custom column with Basic Status

All code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKzE4szlDSUXJKLM5MBtJuiZk5SrE6yFK+qSmZpblARkBicTGanEdiUQqyruDEvKxSJOPgWmAScMMgWmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Basic Status",
    each let currentName = [Name],
    BasicStatus = Table.First(Table.SelectColumns(Table.SelectRows(Source,
        each [Category] = "Basic" and [Name]=currentName),"Status"))[Status]
    in BasicStatus)
in
    #"Added Custom"

[HotChilli · Accepted solution]

Duplicate the table (let's call it TableY). Now filter TableY to keep only the Category = Basic rows.

From the original table, do a Merge with TableY on Name (left outer join).

You can then expand only the Status column to return Pass or Fail for each row (as long as there was a Basic category row for the Name).

If there is a Name which does not have any Basic rows, you can add a conditional column to return the status.