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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.