March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
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"
regards
Phil
Proud to be a Super User!
Hi @jamesc926,
different approach:
Result
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
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"
regards
Phil
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
12 | |
10 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
16 | |
16 | |
11 |