Hi, I am using a SharePoint list connection to connect to a table (SharePoint list) in SharePoint online. I don't see any option to pivot data like we see in Excel. I assume this is a limitation on this type of connection. As per this help page, pivot option is not available for all data sources. That said, it does mention options of using Custom SQL. I don't see that option either for SharePoint List.
I guess, I can export the list to an excel file and then using Pivot but I am trying to avoid that because I am trying to automate the refresh on an ongoing basis and would like to connect directly to a SharePoint List. Any workaround anyone aware of?