Find values based on a condition but return all occurrences where there are multiples of the value

[Liam01]

Hello,

Help required please and hopefully I explain this correctly.

I'd like to be able to find where a value meets a condition but it returns all occurrences of that value including the ones that don't meet the condition.

So in the below example, data on the left and I've searched for all orders that appear on sheet A. The results show all those but also the occurrences of those orders that are on sheets B and C.

Is there a way to do this as a measure - maybe filter by A then shown as a count with the number of total sheets they appear in?

Thanks

Order number	Sheet		Results	Order number	Sheet
12345	A			12345	A
12345	B			12345	B
23456	B			34567	A
34567	A			34567	B
34567	B			34567	C
34567	C			89123	A
45678	B			89123	C
45678	B
56789	B
89123	A
89123	C

[lbendlin]

let

     Source = <your source query>,

    ...

[Liam01]

Hi,

I don't understand which part of the text this should replace - is the Source added before Table.FromRows? So

let

Source = #"Query Name", Table.FromRows...

I've done this and get a 'Token Equal expected' error?

Or is it this entire section after Source = ?

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyVIrVQfCcwDwQxwzOA3HM4SphPFQ5ZzAPxLGAy6HyQBxLOM/CEmgj3EwYD2hKLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, Sheet = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Sheet"}, {{"Rows", each _, type table [#"Order number "=nullable text, Sheet=nullable text]}}),

[Kaly · Accepted solution]

Hi @Liam01 ,

According to your description, here's my solution. Create a measure.

Measure =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Order number] = MAX ( 'Table'[Order number] )
                && 'Table'[Sheet] = "A"
        )
    ) > 0,
    1
)

Put the measure in the visual filter and select its value to 1.

Get the correct result.

Best Regards,

Kaly

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

[Liam01]

Hi @Kaly

That seems to have done the trick and what I was after, thanks for that.

Thanks

[lbendlin]

So for each sheet you want to list the orders that only appear on that sheet? Or do you want to list the number of sheets each order appears in?  What about 45678? Should it count once or twice?

[Liam01]

Hi,

I want to show the orders that appear in sheet A but also show which other sheets those orders appear in. 45678 wouldn't appear as it's in sheet B only.

Thanks

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyVIrVQfCcwDwQxwzOA3HM4SphPFQ5ZzAPxLGAy6HyQBxLOM/CEmgj3EwYD2hKLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, Sheet = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Sheet"}, {{"Rows", each _, type table [#"Order number "=nullable text, Sheet=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Intersect({[Rows][Order number],#"Grouped Rows"{0}[Rows][Order number]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Sheet", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Custom"}, {{"Sheets", each _, type table [Sheet=nullable text, Custom=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom.1", each Text.Combine([Sheets][Sheet],", ")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Custom", "Order"}, {"Custom.1", "Sheets"}})
in
    #"Renamed Columns"

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".

[Liam01]

Hello,

How do I relate this to the query/table that the information is in? Do I need to change the names for some of the information in here to connect it to my data?

Thanks

[lbendlin]

yes, change the Source to the data you want to use.

[Liam01]

Hello,

I've had a look and this appears to be spot on (although it's somehow recreating the example table I made) I'm probably being a little slow but I can't manage to change the source so that's it's reflective of my data?

I assume i'll also need to change some column names somewhere within that code as the column names I put on my example table above aren't the same.

Thanks

[lbendlin]

Change the first step ("Source" ) to point to your actual data source.

[Liam01]

Hello,

I've edited the part of the text in bold below to the name of query I want it to relate to but I get the following error - 

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyVIrVQfCcwDwQxwzOA3HM4SphPFQ5ZzAPxLGAy6HyQBxLOM/CEmgj3EwYD2hKLAA=", BinaryEncoding.Base64)

Thanks

[Liam01]

Hi,

Apologies for my delayed response - Thanks for the help and your suggested solution. I've not had chance to try this yet - it looks a little beyond my current level of understanding to be honest - but I will have a go at implementing it when I get chance and see if it works.

Thanks,