Help needed - filter, power queries and pivot

[smsayedee]

Hi All,

I've a table with below details:

Column A: Activity Name

Column B: Start Date

Column C: End Date

Now an activity can be completed in a day, so the start and end date are the same or it can even span over few months. Is there a way I can create a filter where it would list all the months where there is/are activity(s) occurring (in MMMM YYYY format) and when I select a month from that filter it would return the activities that are occurring that month. For example, if Activity1 has a start date 13/05/2023 and end date 15/07/2023 then there would be May 2023, June 2023 and July 2023 in the filter and even if I click June the activity would appear as the month June 2023 falls between the start and end date of that activity. 

Please note, I can't use any macro and probably only limited to excel (office 365) with power query.

Any idea would be highly appreciated as I'm learning excel. Thank you :)

[#2]

Hi @smsayedee 

Not sure I fully understand how you want to represent your filter. See if Sheet2 (done with Power Query) allows doing what you expect, otherwise please clarify

[#3]

@smsayedee 

Attached is another option with a PivotTable (after Power Query)

[#4]

Thank you so much for this, however, if some of the startDate cells and/or endDate cells are empty, then could you please advise how to write the GetPeriods function so that it doesn't return an error ('can't convert null value to date')? many thanks in advance :)

[#5]

There's probably something to do. However, what "MMMM yyyy" - or any other value i.e. null - should be returned in such a case?

[#6]

@L z. thanks for helping me out. I've attached a mockup spreadsheet so that I probably be able to explain my problem better. In the spreadsheet, the Sheet1 contains the raw data with sheet 'Table1' is the output of query. Now you would notice that there may be some blank rows in Table1, especially some may have start date but not the end date, some may not have both and others may be completely blank rows. I want these incomplete rows not to be included in the output of the query.

Therefore, incorporating your previous solution to get the period for each activity I want to create a pivot slicer with list of months that allows me to filter the activities that are occurring that month.

Again, thank you so much for your help with this. :)

[#7]

@smsayedee 

I want these incomplete rows not to be included in the output of the query OK, no problem

I want to create a pivot slicer with list of months that allows me to filter Do you run Excel on Windows?

[#8]

@L z. Thank you!

excel on windows. But the spreadsheet actually saved in sharepoint for collaboration. So other user can use it from other platform. Does it matter?

[#9]

@smsayedee 

 there may be some blank rows in Table1, especially some may have start date but not the end date, so

 what did you mean blank start end date in Table1?

They are all filled in the attachment.

And Table1 and Sheet1 are same?

pivot slicer with list of months that allows me to filter the activities that are occurring that month.

pivottable?

[#10]

@smsayedee 

excel on windows. But the spreadsheet actually saved in sharepoint for collaboration. So other user can use it from other platform. Does it matter?

I was thinking of using the Data Model (Power Pivot). Will avoid then...

[#11]

I’ve been using data model, no issues so far!

[#12]

@peiyezhu sorry my bad. say there are rows with incomplete entry, e.g. start date there but not the end date, vice versa or both the columns are empty or even the complete row is empty (data yet to be entered).

blank means no value entered (null)

yes, that’s the ultimate goal but in order to get there I would need a query that would list all months where an activity occuring between the start and end date.

does that help?

[#13]

@smsayedee 

I’ve been using data model, no issues so far! So 2 options attached

Benefit of the PowerPivot option: Periods in Slicer are sorted chrono.:

[#14]

@L z. Thanks for your help. Its really working. However, I've encountered another problem while solving one. For example, if I create a pivot table where I want to see the count of activities based on progress status it counts one activity 5 times where that activity spans over 5 months. In the below example, although there are only 4 distinct activities, Activity1 was counted 6 times since the start date was 23/03/2023 and end date was 25/04/2023 and 3 Person was involved, hence 3x2=6.

Could you please advise how can I resolve this so that if I create such pivot if would only reflect the correct number which is 4 with 1 completed, 2 in progress and 1 is planned. I've attached the spreadsheet for your convenience. Thanks once again.

[#15 · Accepted solution]

@smsayedee 

Linking posts is appreciated, marking solutions helps people who Search - Thanks

See attached file that does what you expect in term of Count by Activity Status