Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mpraka
Advocate I
Advocate I

Status based on one Category

Hi All,

 

I want to achieve this scenario in Query Editor:

I want to create a new column in query editor.

Sample Data:

Question1.JPG
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:

Q2.JPG
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

3 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

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.

View solution in original post

bolfri
Super User
Super User

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

bolfri_0-1690934562287.png

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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bolfri
Super User
Super User

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

bolfri_0-1690934562287.png

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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




HotChilli
Super User
Super User

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.