lördag 3 oktober 2015

Excel Machine Learning (add-in)

Machine Learning inside Excel.

Try without installing:

Source: Jen Underwood tweet


What is Machine learning ?



'Deep learning (neural networks)' 

Link: Twitter flow, Machine Lerning


Machine learning


1. Take a small sample, 2. train it to cluster, 3.


























Link: Best Cheat sheets


Recurrent Neural Networks (RNN) - one of many neural network patterns.
"The idea behind RNNs is to make use of sequential information. In a traditional neural network we assume that all inputs (and outputs) are independent of each other. But for many tasks that’s a very bad idea. If you want to predict the next word in a sentence you better know which words came before it. RNNs are calledrecurrent because they perform the same task for every element of a sequence, with the output being depended on the previous computations. Another way to think about RNNs is that they have a “memory” which captures information about what has been calculated so far. In theory RNNs can make use of information in arbitrarily long sequences, but in practice they are limited to looking back only a few steps (more on this later). 

Here is what a typical RNN looks like:
Embedded image permalink

Semantic analysis: http://news.mit.edu/2015/more-flexible-machine-learning-1001
Cheat sheets: http://designimag.com/2015/06/best-machine-learning-cheat-sheets/

Cortana gallery: https://gallery.cortanaanalytics.com/

Other
Power BI Custom Visualisation Competition - Developer tools, Start page
http://thevisualcommunicationguy.com/wp-content/uploads/2015/06/Infographic_RulesOfPunctuation1.jpg

torsdag 2 juli 2015

PowerQuery: Combine files & Promote Headers + fnSWITCH() + Alternating rows

image_thumb26
Picture: Add a column: =Excel.Workbook([Content])
Source: link




Smart solution to a common problem.
Link


Insert BlankQuery: fnSWITCH()

(input) =>
let values = { {result_1, return_value_1}, {input, "Undefined"} },
Result = List.First(List.Select(values, each _{0}=input)){1}
in Result

Link


Alternating rows


Retreive Named ranges
= Excel.CurrentWorkbook()  //finds all
= Excel.CurrentWorkbook(){[Name="MyRange"]}[Content]
= Files = Folder.Files(Source{0}[Column1]),                                    // Single value

----
Undocumented
BI/Analytics training

torsdag 14 maj 2015

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

lördag 25 april 2015

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/

lördag 11 april 2015

Power BI Designer & Analytics



Power Query Function example video

Power BI Designer review
Power BI Designer: 5 play list


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

lördag 14 mars 2015

Combining Arrays - UDF

=CombineArray()

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

lördag 14 februari 2015

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 Studiocreate 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