Hi GK,

Thanks for sharing your file. It's perfect. 

You can add a conditional column that tags the data with "Yesterday&time" and "Today&Time" which you can then use to filter out the data you don't want. From there you can create a PivotTable to extract the data in the layout you want. See file attached.

Note: I've hard coded the dates in the conditional column with #date(... ) but you can replace the hard coded date for today with Date.From(DateTime.LocalNow()) and for yesterday's date use Date.From(DateTime.LocalNow())-1 like so:

=if [Date] = Date.From(DateTime.LocalNow()) then "Today"&Time.ToText([Time]) else if [Date] = Date.From(DateTime.LocalNow())-1 then "Yesterday"&Time.ToText([Time]) else "Prior"

Mynda

Hello Mynda,

That saved a lot of time Mynda and not to mention the headache that came with it.  So a big thank you for not only resolving but giving it your priority even on a week end, for which I am much obliged.

But I have a query (as always) :).   

In the query, you had hardcoded the date as "each if [Date] = #date(2021, 4, 1) then "Today"&Time.ToText([Time]) else if [Date] = #date(2021, 3, 31).....".  Since my source will be dynamically updated as and when a new day starts, can we change the above code to check only data for the current day and the previous day, as against specifically adding dates.

Thank you Mynda for all your help.

Cheers
GK

Hi GK,

I answered this question in the 'note' in my reply.

Mynda

Damn, my bad, Mynda, I was so focused on the excel sheet missed this dearly.

My apologies, Mynda.

That clears the air, so all is well.

Thank you dearly,
~GK

Hi Mynda,

The pivot that you created requires auto-refresh.  While we have auto-refresh for the query at any specified intervals, to refresh a pivot, from what I understand from the internet, certain lines of codes needs to be added as a VB Module.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

I did try a few other VB Codes as well, that doesnt seem to refresh and always requires manual refresh.  

FYI - I have moved the pivot to a separate sheet now (Pivot), thought that could have been the problem, but still no go.

So I am back to the expert for assistance.

Cheers
GK

Hi GK,

You can set PivotTables to refresh at set intervals via the Connection Properties:

Data tab > Queries & Connections > Connections pane > right-click > Properties > Usage tab.

If you require VBA, please start a new question specific to the VBA you require in the VBA forum group.

Mynda

Thanks Mynda.

This only refreshes the power query but I am looking for the pivot that you had created.

Believe VB is the only answer, will create a new thread.

Warm Regards
GK