Com PowerPivots, encontre itens que aparecem * somente * em uma categoria

Question

Com o PowerPivot, como encontro itens apenas em uma categoria? Aqui está um exemplo:

item category number obera f6 7 artois f7 2 obera f7 3 ella f6 9

Se eu escolher f6 , a saída deste exemplo seria:

ella

(não conteria obera, visto que também aparece na categoria f7). Agradecemos antecipadamente.

Editar: o que eu quero fazer é para cada categoria (da qual eu tenho muito), exibir itens que só aparecem nessa categoria e em nenhum outro lugar.

    

Answer 1

Eu criei uma solução para isso usando o Power Query Add-In. Você pode fazer o download do meu OneDrive - é o arquivo Demonstração do Power Query - itens que estão em apenas uma categoria

link

Aqui está uma descrição de como eu construí:

Usando o Power Query, criei uma Consulta ("Tabela1") da tabela do Excel. Desmarquei a opção Load to worksheet - isso é apenas para entrada na próxima consulta.

Em seguida, criei outra Consulta ("Tabela1 multi-categoria") da mesma tabela do Excel. Nessa Consulta, fundei a Consulta "Tabela1", juntando-me ao Item. Em seguida, expandi as colunas Item e Categoria de "Tabela1". Note que neste ponto eu aumentei o número de linhas, cobrindo todas as combinações de Item.

Em seguida, adicionei uma coluna personalizada "multi-categoria". A fórmula é apenas:

[category] <> [NewColumn.category]

Eu já participei do item, então isso testa se há várias categorias para esse item. Então eu filtrado nesta coluna, apenas mantendo os valores VERDADEIROS.

Em seguida, adicionei um Group By para agrupar por Item e Categoria e para obter o valor Max da coluna "multi-category" - isso atende ao cenário quando um item tem mais de duas categorias.

Por fim, removi as colunas "working out" e novamente desmarquei a opção Load to worksheet - isso é apenas para inserir a próxima consulta.

Em seguida, criei outra Consulta ("Tabela 1 final"), começando novamente da tabela original do Excel. Desta vez eu me fundei na Consulta "Tabela1 multi-categoria", unindo-se em Item e Categoria, e incluindo linhas que não combinam.

Depois, expandi e renomei a coluna "multi-category" e substituí os valores nulos por FALSE. Esta consulta é entregue em uma nova tabela em uma nova planilha "Multi-categoria".

Por fim, criei uma tabela dinâmica nessa nova tabela na planilha "Várias categorias". A coluna "multi-categoria" informa quais itens existem em várias categorias.

Obviamente, são necessários alguns passos para chegar a este ponto. No entanto, cada passo em isolamento é bastante simples e bastante fácil de entender, dado o editor WYSIWYG Power Query. É certamente muito mais fácil de seguir do que depurar código VBA e 100 vezes melhor do que tentar depurar fórmulas do Excel.