Use full history of query for visual

[Anonymous]

Hello everyone.

I have a large table with the rows being individual customer orders and the columns being the different features a customer can choose to configure his order:

Order Number	Order Date	Type	Color	...
101	2020	Small	Red	...
102	2019	Large	Green	...
103	2020	Large	Red	...
...	...	...	...	...

What I want to achieve is a table with a list of all features, the possible variants per feature and how often each variant was chosen:

Feature	Variants of feature	Count
Order Date	2020	2
	2019	1
Type	Small	1
	Large	2
Color	Green	1
	Red	2
...	...	...

So far, I edited the initial table in the Power Query Editor using a Python script and achieved the desired table:

import pandas as pd 

intermediate_result = dataset.apply(lambda x: x.value_counts(dropna = False)).T.stack().sort_index() 
final_result = intermediate_result.reset_index() 
final_result.columns = ['Feature', 'Variants of feature', 'Count']

However, once I visualise the table using a matrix visual, the matrix visual cannot be filtered by clicking on other visuals that are based on the columns of the inital table. For example, I would like to see only the ordered features and variants for a certain Order Date.

I think the problem is that the matrix visual only considers the last step in the "Applied Steps" in the Power Query Editor and not the full history of the query.

Is there a way to solve this problem?

Tank you very much!

[v-luwang-msft]

Hi @Anonymous ,

Could you pls share your pbix file? Remember to remove confident data.

Best Regards

Lucien

[Anonymous]

Hi @v-luwang-msft,

I have trouble uploading the pbix file. It says that pbix is not supported 🤔

However, I try to explain my problem in more detail.

My initial table looks like this:

What I would like to achieve is a matrix visual containing a list of all features, the possible variants and the count for each variant. It is supposed to look like this:

Therefore, I transformed my initial data in the Power Query Editor using the code from above. Then the final table in the Power Query Editor looks like this. This final table is used as basis for the matrix visual.

Using the slicer filter I would now like to filter the matrix visual only for specific years. For example, I would only like to see which colour or sizes were ordered in 2020. I tried this by addind the column "Year" of the initial table in the slicer visual but this of course does not work. Is there a way to fix this? 

Thanks in advance!