Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    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:

    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.

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        3 years ago

        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?

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Anonymous ,

    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:

    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.