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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nuhasan
Helper IV
Helper IV

How to show true or false between row

Hi all,

 

Here is my data

 

Prod OrderDateCompletedBalance
10000272044310/7/2023262.5 
10000272044311/7/2023 32
10000262077612/7/202331.511.1

 

My expectation formulation is something as below

 

if Balance have different Date under same Prod Order, display "warning"

 

Regards,

Nuha

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @nuhasan ,

According to your description, here's my solution.

Add a custom column:

if List.Count(List.Distinct(Table.SelectRows(#"Changed Type",(x)=>x[Prod Order]=[Prod Order])[Date]))>1 then "warning" else null

Get the correct result:

vyanjiangmsft_0-1691548560282.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

Hi @nuhasan ,

According to your description, here's my solution.

Add a custom column:

if List.Count(List.Distinct(Table.SelectRows(#"Changed Type",(x)=>x[Prod Order]=[Prod Order])[Date]))>1 then "warning" else null

Get the correct result:

vyanjiangmsft_0-1691548560282.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Vijay_A_Verma
Super User
Super User

Insert following statement where #"Changed Type" should be replaced with your previous step

 

= Table.Combine(Table.Group(#"Changed Type", {"Prod Order"}, {{"All", each Table.AddColumn(_, "Indicator", (x)=> try if _[Date]{0} <> _[Date]{1}  then "Warning" else null otherwise null, type text)}})[All])

 

Complete code in action

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dczBDcAwCAPAXXiTCJsUhonYf42GvvKpP5alk/cW2AmTtpaLCo0+LAesR3A+p6X0H6KBivNSQcuMW7EJvjdgQqpe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prod Order" = _t, Date = _t, Completed = _t, Balance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prod Order", Int64.Type}, {"Date", type date}, {"Completed", type number}, {"Balance", type number}}),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Prod Order"}, {{"All", each Table.AddColumn(_, "Indicator", (x)=> try if _[Date]{0} <> _[Date]{1}  then "Warning" else null otherwise null, type text)}})[All])
in
    #"Grouped Rows"

 

Hi @Vijay_A_Verma ,

 

Done as per your code but it show error

 

#"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"OrderNumber_AUFNR"}, {{"All", each Table.AddColumn(_, "Indicator", (x)=> try if _[DocumentDate_BLDAT]{0} <> _[DocumentDate_BLDAT]{1} then "Warning" else null otherwise null, type text)}})[All])

 

Regards,

Nuha

Did you change #"Changed Type" to your previous step in your PQ editor?

If your previous step was also #"Changed Type", then error is due to some other factor. Could you please post that error?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors