Product Filter Across Multiple Columns For Single Sale

[dcech7]

Hello,

I am new to powerBI and I'm running into an issue my data looks something like this

Deployment ID	Approval Status	Country	Product Details	Sales Out	Forecasted Units	Product Details #2	Sales Out #2	Forecasted Units #2	Product Details #3	Sales Out #3	Forecasted Units #3
1	Approved	USA	Apple	1	2	Orange	2	4	Apple	4	6
2	Approved	Germany	Orange	4	5	Lemon	4	4	Kiwi	2	6
3	Approved	Germany	Lemon	5	5	Kiwi	2	2	Orange	1	5

My issue is that when I try to filter by Product Details and choose Apples, it doesn't pick up apples in Product Details #3. My hope is that I can analyze sales for each product in respect to other filters like country, ie "What is the Distribution of units in germany by product name." 

Anything helps thank you. Please reply if more clarification is needed. 

[MattAllington · Accepted solution]

The best approach is to reshape the data. You should have 1 column for product, 1 for sales out, 1 for forecast. Add a column for details, like #1, #2 etc if needed. You can do this with Power Query, but I can't say how without seeing the data. My guess is unpivot columns other than the first 3, remove the references to #1, #2 etc so all data has the same column names, then repivot the columns. That will combine all into the same column

[dcech7]

Hi Matt,

Thanks for the reply. My only issue with reshaping the data would be maintaining the integrity of future data that comes in. Would I need to transform the data every time we have an update or would Power Bi be able to pick up the pattern? Anyways thanks for the advice I can definitely try to do that. 

[MattAllington]

Well, it does depend on what can change, but if you follow my recipe by unpivoting other columns, it will correlctly handle new sets of columns.