Excel & Analytics: 2014

måndag 8 december 2014

Vis2014 - Domino

Link: http://caleydo.org/projects/domino/ (open source)

söndag 16 november 2014

Gartner BI-Survey 2014 + Tableau v.s. Qlikview

BI Survey 2014 by Gartner: Summarised by Tableau in Omaha (above)
BI Survey 2014 by Gartner: Summary by Jen Underwood

2014 Comparison: Tableau v.s. Qlikview

lördag 15 november 2014

Business Decision Architecture & Microsoft Machine Learning

They've almost discovered the full picture ;-)

1. Resource Management: SQL-server (Gartner)
2. Process Management: Power Query/Pivot, Excel, R
3. Presentation Management: Tableau, Power View/Map (not ready)

Machine Learning (MAML)
Why care: Microsoft's Bing predicted midterm election with 95% accuracy
How ToDo: Getting Started on Azure Machine Learning Build a "Data Service", Blog, 2 (with VB)
TED: Where are we today, Kaggle, 2, blogg, tools used

Unpublished: Modular 3D Printer

söndag 26 oktober 2014

Power Query and API

Excellent post+sample file by Erik Svensen how to import data via Power Query and a JSON query.

...

....

Link: Excel-File

Unpublished: Import Excel file to SQL Server, Microsoft Product Roadmap, Office365 Roadmap, Excel Data Mining Add-Ins

måndag 13 oktober 2014

Power Query: SQL-server query based on an Excel table

My first trial to query a SQL-database via an Excel list:

let
Source1 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
Source2 = Sql.Database("MS10\SQLEXPRESS", "TSQL2012"),
Sales_CustOrders = Source2{[Schema="Sales",Item="CustOrders"]}[Data],
Joined = Table.Join(Source1, "custid", Sales_CustOrders, "custid"),
in
Joined

Unpublished: Import Pictures, Book: Power Query for Power BI and Excel

Similar posts: dynamic filters, dynamic function, FastCombine
Videos:
Deep Dive into Power Query Formula Language
The Power Query Formula Language, PowerPivot video

fredag 3 oktober 2014

List unique values - non duplicates

The value list is placed in column A2:A100000 (A1 is the header)
Place the first formula in column B2 (B1 is the header) and copy down as far as needed.
Prerequsites: All cell-values are valid (no "errors"), just to simplify.

Excel 2010-
=AGGREGATE(15;6;$A$2:$A$100000;ROW(A1))

Excel -2007
Alt.1: Dual cells, one Array formula in B3
B2: =SMALL($A$2:$A$100000;1)
B3: =SMALL(IF(($A$2:$A$100000-$B1)>0;$A$2:$A$100000);1)

Alt.2: Singel cell, Array formula
B2: =IF(ROWS($B$2:$B2)>1;SMALL(IF(($A$2:$A$100000-$B1)>0;$A$2:$A$100000);1);MIN($A$2:$A$100000))

Array formulas: The formulas with IF(..) has to be confirmed with Ctrl+Shift+Enter, not just Enter