Forum Discussion
ypaotroicn
9 years agoNew Member
Filtering to reverse filtering. What??
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 am...
MarcelBeug
9 years agoCommunity Champion
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