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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jamesc926
Frequent Visitor

Test serial numbers to check to see if conditions are met

I need help testing my table of serial numbers 

For each serial #

        if the Order Type = 60 AND 64 then Status = nOK

        if the Order Type = 60 then Status = OK

 

jamesc926_0-1711062475231.png

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @jamesc926 

 

Download example PBIX file with the code below

 

Try this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYxsDQwMzU2NVbSUTIEYjMDpVgdNHFdsIQJWMIpxNjCwNDFAKgORQNc3BBV3AAMDE1NLPCII1sAlTAxMsaqASIO1xALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, Qty = _t, #"Order Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Qty", Int64.Type}, {"Order Type", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Order Type] = 64 then "" else if List.Contains((let x = [Serial Number] in Table.SelectRows(Source, each [Serial Number] = x)[Order Type]), "64") then "nOK" else "OK")
in
    #"Added Custom"

 

stats.png

 

regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

Hi @jamesc926

different approach:

 

Result

dufoq3_0-1711116637457.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYxsDQwMzU2NVbSUTIEYjMDpVgdNHFdsIQJWMIpxNjCwNDFAKgORQNc3BBV3AAMDE1NLPCII1sAlTAxMsaqASIO1xALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, Qty = _t, #"Order Type" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Qty", Int64.Type}, {"Order Type", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Serial Number"}, {{"All", each 
        if List.ContainsAll([Order Type], {60,64}) then Table.FromColumns(Table.ToColumns(_) & {{"nOK", null}}, Table.ColumnNames(_) & {"Status"})  else Table.AddColumn(_, "Status", each "OK") , type table}}),
    All = Table.Combine(GroupedRows[All])
in
    All

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

PhilipTreacy
Super User
Super User

Hi @jamesc926 

 

Download example PBIX file with the code below

 

Try this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYxsDQwMzU2NVbSUTIEYjMDpVgdNHFdsIQJWMIpxNjCwNDFAKgORQNc3BBV3AAMDE1NLPCII1sAlTAxMsaqASIO1xALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, Qty = _t, #"Order Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Qty", Int64.Type}, {"Order Type", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Order Type] = 64 then "" else if List.Contains((let x = [Serial Number] in Table.SelectRows(Source, each [Serial Number] = x)[Order Type]), "64") then "nOK" else "OK")
in
    #"Added Custom"

 

stats.png

 

regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors