Forum Discussion

smsayedee's avatar
smsayedee
Copper Contributor
May 30, 2023
Solved

Help needed - filter, power queries and pivot

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 o...
excel
Formulas and Functions
smsayedee's avatar
smsayedee
Copper Contributor
May 31, 2023
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 🙂
Lorenzo's avatar
Lorenzo
Silver Contributor
May 31, 2023

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

  • smsayedee's avatar
    smsayedee
    Copper Contributor
    Jun 01, 2023

    Lorenzo 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. 🙂

    • peiyezhu's avatar
      peiyezhu
      Bronze Contributor
      Jun 01, 2023

      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?

       

      • smsayedee's avatar
        smsayedee
        Copper Contributor
        Jun 01, 2023

        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?

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 01, 2023

      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?

      • smsayedee's avatar
        smsayedee
        Copper Contributor
        Jun 01, 2023

        Lorenzo 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?

Resources