Power Query usage?

[newexcler]

Hi Experts,

Is there an advantage in using a power query to analyze multi-filter tables where the primary key is not ID but a unique name or code (not numerical) ?

For example:

Name	Department	Access to roles	Validity

Say, I make Name and Department as another query, remove duplicates, and create a relationship to this table?
If I create a pivot table for the created data model, can I create multi-filter slicers to analyze better?
I tried but I am not able to make filter created in the pivot table link to other filters.
example: Filter by Name, I was expecting I get all the details of department, roles etc of the person and when I filter on the department, it provides a filter for all roles, names, etc.
But I couldn't So please can let me know if this use case is good for Power Query / Pivot or I am using it wrong?

 

[GraH]

Hi,
If I understand your query correctly then, yes...

Yes, it is smart to normalize the data and create unique lookup tables and use these in a data model.
For slicers, you are talking about a standard behaviour and there are visual clues inside the slicer (see below).

Remember to use "Department" and other fields from your lookup tables inside the slicers as filters flow down in he model and never up. Understand it as filtering the fact tables first, never filters the lookup table.
Side note: those slicers setting to visualize items yes or not is expensive and often a bad idea.

 

[newexcler]

Hi,
If I understand your query correctly then, yes...

Yes, it is smart to normalize the data and create unique lookup tables and use these in a data model.
For slicers, you are talking about a standard behaviour and there are visual clues inside the slicer (see below).

Remember to use "Department" and other fields from your lookup tables inside the slicers as filters flow down in he model and never up. Understand it as filtering the fact tables first, never filters the lookup table.
Side note: those slicers setting to visualize items yes or not is expensive and often a bad idea.
View attachment 44867