Filter in Power Query Based on Specific Criteria

[ndbennett]

I have a spreadsheet with a table containing sales data by supermarket chain, unique store and by date. 80% of the sales are within one chain of stores; the rest of the sales come from the other chain. The chain with the most sales was routinely the most frequent and reliable in updating so I have created a report to show the total sales over the previous 7 days by store compared to each store’s 7 day average.

I do this in Power Query by setting a RefDate = Date.AddDays(List.Max(SetTypes[Date]),-7)

Then FilterSetOnRefDate = Table.SelectRows(SetTypes, each [Date] > RefDate)

The problem I now have is that the updates from the larger chain have recently become erratic and irregular, meaning that the smaller chain’s reports are usually several days ahead of the larger.

This means that the time period being considered for actual sales is based upon the most recent update of the smaller chain, and therefore there can be 2 or 3 days’ missing data from the larger chain, making its 7 day sales data meaningless.

I would like to change the RefDate so that it only looks at the Max date of the larger chain rather than the max date of both chains. Is this possible?

I appreciate this will probably overstate the performance of the smaller chain, but the value of knowing what is happening in the larger chain is greater to me.

Thanks.

 

[gravanoc]

I'm not positive, but I'd suggest adding a conditional column as a step. Use the If statement so..

= Table.AddColumn(previousStep, "CustomColumn", each if [ChainSize] = "Large" then "Large" else "")

See if that helps. Note that previousStep is whatever variable or step you intend to reference.

 

[ndbennett]

I'm not positive, but I'd suggest adding a conditional column as a step. Use the If statement so..

= Table.AddColumn(previousStep, "CustomColumn", each if [ChainSize] = "Large" then "Large" else "")

See if that helps. Note that previousStep is whatever variable or step you intend to reference.

Thanks for your reply. I didn't follow it exactly, but you gave me an idea of how to resolve my issue easily - I simply created a custom column with an IF statement to pick out the dates for the 80% chain only, then set the max of that date as the ref date. Thanks again!