Forum Discussion

New_hello188's avatar
New_hello188
Icon for Helper I rankHelper I
4 years ago

Grouping row with multiple condition and rules _ Idea ?

Hi All , 

 

May I get your help to guide on grouping this datasets? 

 

Condition 

1 Job-code has multiple line due to its different class
2 Ignore the Completed status for row under class = Resource
3 Any row with Completed = True under same Job-code, will be True.

 

My current logic fail to meet Case 1 example. Here is my method (simple and long ><) 

1. Countrow 

2. New column created for Completed , change Row with type = False to True. 

3. Count row for those is False 

4. If statement ; If Row with Completed (False) = total row of Job-code , then it will be False 

My excel file : https://1drv.ms/x/s!Al4ndRgX_oariEQPbNObEUNV1qJl?e=tBYUwE

 

Appreciate if anyone can help to shed light on it.! Thanks in advance! 

 

 

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Thank you for providing sample data.

     

    You don't really need Power BI for that, this can be done in Power Query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZQ7b8MwDIT/i+cUkCgrEccMLYKiUx5TkMkI2i4N0Db/P7WJIgYfl8nDB4r03ZHHY/eacnpKlFO36Daf7x9/n/328NydFiNbavayftv9wxUonBgBVgDrAauALQFbAdYA45iVBBjQpQS6tLFf8GYDWjegdQNaM3jzzrbnn8v1ezgr8xk05UdNA4MZGMzAYAYGszbY/ERTf6mLgwQwSADHCcgpdlkYqvMFF+YLLswXXJgvuDBfcGG+4ML8jRLGvhkTDAQVBoQJVkpYCRpm4EQBhy/3DyEhGM3Ta6fmsIJhK4hNBbGpIDbmuM+HMdfdwOgfwY3L5saZQnCQsrmCPgyqzS00MHiazM4aCCsDocisrYE1gGOs8zTQehgu169fxSlOizA/LcL8tFDRts4bGqgLg2F6UIdWcAZ9twntKKEdJbSjAh1bTjc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job-code" = _t, Class = _t, Completed = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job-code", type text}, {"Class", type text}, {"Completed", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Class] <> "Resource")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Job-code"}, {{"Final Status", each List.Max([Completed]), type nullable logical}})
in
    #"Grouped Rows"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".