Power Query: How to iterate over a list

By Satalyst Brilliance on 30 Apr 2015

If you’ve been using Power BI or you’ve seen the demos, then like me, you’re probably excited about Power Query. As a data nerd, I’ve been having a lot of fun pulling data sets off the web and mashing them up. Getting web data used to be quite a manual task for me, since I wasn’t about to write a custom screen-scraper. I really love the “From Web” functionality that mines web pages for HTML tables. But I’m a developer, so of course, I wanted to load a list of files, or a list of websites, and I had some trouble figuring out how to do it. Hopefully, my experiences will help someone else. One of the tricks that I found was that the list that you want to iterate over has to be pulled into Power Query designer. So let’s start with looping over a list of files – this is something that EVERYONE will want to do at some point. So first, launch the Power Query designer from the Power Query ribbon, and use the folder that you want as your data source: As you can see, you get a lot of meta data about your folder contents. The most important…

Read More

Microsoft to offer Office 365 and Dynamics CRM from Australian datacentres

By Satalyst Brilliance on 19 Dec 2014

Microsoft Corp have recently announced plans to deliver Microsofts cloud productivity suite – Office 365 – and business application – Dynamics CRM Online – from datacentres in Australia by the end of March, 2015. These locally hosted services will be delivered from two existing datacentres in New South Wales and Victoria and follows the launch in October of local Azure services. This move gives you a range of widely-used cloud services and the ability to run cloud services from infrastructure to full application services, from the same datacentre, and with the benefits of increased speed and the ability to keep all of your data onshore in Australia. To learn more – please visit the Microsoft – Australia News Centre.

Read More

Creating a list workflow for SharePoint Online 2013 using Visual Studio

By Satalyst Brilliance on 01 Dec 2014

A few months ago, we migrated from SharePoint 2010 on premise to SharePoint Online 2013 – a huge paradigm shift for us and our custom workflows. If you’re reading this blog you’ve probably noticed that documentation for SharePoint Online workflows is hard to come by, especially using Visual Studio rather than SharePoint Designer. So I’ll share my experiences and hope it helps. Calling RESTful Services The first pain point that I encountered was migrating to declarative workflow and losing code-behind functionality. All custom work must be done in a RESTful service, the response comes back as JSON, and must be parsed out of the new dynamic value type. The syntax is quite verbose: I assign a string variable ServiceCall to the endpoint plus the method, like so: ServiceCall = “http://satalyst.com/services/MyService.svc/SomeRESTfulMethod” Whether or not your method is returning a value, it should be tagged in the service interface with the WebGet attribute. Then I execute an HttpSend activity. The ResponseContent must be a variable of the new DynamicValue type. Finally, I parse the dynamic value response. In this case, the response JSON looks like this: {“IsCubeProcessingResult”:{“Status”:”COMPLETE”}}           So parsing the response using the GetDynamicValueProperties activity looks like…

Read More