PowerQuery: Input - Process - Output

Picture: Simple transformation (link). Note: FunctionToApply can be placed on any row.

Note:

Excel.CurrentWorkbook() = All named ranges

Table1 = Range name, selected

[Content] = All three columns, selected

Input{0}[Column1] = 1st cell in range Column1

PowerQuery: Retreive several web-pages

(page as number) as table =>
let
    Source = Web.Page(Web.Contents("http://boxofficemojo.com/yearly/chart/?page=" & Number.ToText(page) & "&view=releasedate&view2=domestic&yr=2013&p=.htm")),
    Data1 = Source{1}[Data],
    RemoveBottom = Table.RemoveLastN(Data1,3)
in
    RemoveBottom


Link: http://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/

Upload, Download DAX tables

Source: http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

Power BI Designer & Analytics

Power BI Made Simple from James Serra

Power BI: Get started, Installing, Support blog

Power Query Function example video

Power BI summary, recommendations

Power BI Designer review

Power BI Designer: 5 play list

How to AutoRefresh Excel data via O365. 

Picture: Picture summary, will Tabular replace MultiDim ?


Video:
1. Create Tabular Cube with SQL Server, Analysis Services, Tabular Model
2. Scaling from PowerPivot (XL) --> PowerPivot (SharePoint) --> SSAS, Tabular Model -->

BI and statistics: link to video

Video: Predictive Analytics

Combining Arrays - UDF

=CombineArray()

and...
=CHOOSE({1;2},TRANSPOSE(List1),TRANSPOSE(List2))
File: http://www.interactiveds.com.au/software/CombineArray.xls
Source: link

PowerPivot & DAX functions

The step between Excel and DAX functions is surprisingly small.
Print this Summary of DAX functions + cheat sheet + 2016 functions. 95% are the same functions.

New functions RELATED(), FILTER(), or here.

Learn via...
Videos: CALCULATE(), Microsoft
Forums: MrExcel, StackOverflow,
Articles: FILTER() vs CALCULATETABLE(), DAX Patterns

Extra
DAX Studio, create measures,
SQL-Server: PowerPivot/DAX in SQL server,
Enabling Tableau to read PowerPivot
To be clear, you are not connecting to the Excel pivot table, but to the underlying PowerPivot model.


CALCULATE(): Any FILTER() in a CALCULATE statement overrides the existing PivotTable selection (for the columns it contains). If we don’t want to loose the existing PivotTable selection, we can use VALUES() instead of ALL().


DAX Studio
EVALUATE
Your OutputTable

EVALUATE
ROW ("Result", Your OutputMeasure)

Hidden DAX-editor in Excel
Enables AutoRefresh (histogram example)

Compare SQL, MDX, DAX
Simple example

DAX joins
Measure(SourceValue;ForeignKey) by Dimension(Related;Key)
=INDEX(Source;MATCH(RelatedKey;ForeignKey;0))
=VLOOKUP(ForeignKey;SourceTable;ForeignKey/Col.;0)

Source: Book

Vis2014 - Domino

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