Filtering to reverse filtering. What??

[ypaotroicn]

Hello community!

I have a bit of a complicated question so I will try to set up the scenario.

I have an Excel data table showing deal numbers, individuals working on the deal, and then the amount the deal is worth. Numerous people work on each individual deal, so there are multiple rows with the same deal numbers and amounts, but with different individuals listed. These individuals also work on multiple deal numbers, and they are not always the same. This data goes through thousands of lines.

My question is, if I were to look at an individual (John Smith) and all the deals that he's worked on, but then also want a list of the individuals who worked on that deal, is there an easy way to use PowerBI to show that information?

I can set it up so that I can see all the deals that John Smith worked on, but then I would have to write down each deal (which can be many), and then filter them individually, but that would take a while.

I know how to do this through vlookups and such, but I was just looking for a more streamlined process.

Any help would be great!

[Anonymous]

Hi @ypaotroicn

Can you please share data model, sample data and the output you expect.

Cheers

CheenuSing

[MarcelBeug]

The query below reads tables "Deals" and "SelectedName" (1 row with the selected name).

It filters the deals on the selected name.

The result is joined with all deals (nestedjoin).

A column is added with all individuals (<comma><space> separated).

The column with tables from the nested join is removed,

let
    Source = Excel.CurrentWorkbook(){[Name="Deals"]}[Content],
    Deals = Table.TransformColumnTypes(Source,{{"Deal", Int64.Type}, {"Individual", type text}, {"Amount", Int64.Type}}),
    Source2 = Excel.CurrentWorkbook(){[Name="SelectedName"]}[Content],
    SelectedName = List.Single(Source2[Name]),
    FilteredDeals = Table.SelectRows(Deals, each ([Individual] = SelectedName)),
    Merged = Table.NestedJoin(FilteredDeals,{"Deal"},Deals,{"Deal"},"NewColumn",JoinKind.LeftOuter),
    Individuals = Table.AddColumn(Merged, "Individuals", each Text.Combine([NewColumn][Individual],", ")),
    RemovedNewColumn = Table.RemoveColumns(Individuals,{"NewColumn"})
in
    RemovedNewColumn