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

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

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