Power BI: Get data from Facebook with Excel 2013, Power Pivot and Power Query

Today I stumbled upon this blog post van Kasper de Jonge:
http://www.powerpivotblog.nl/get-data-from-facebook-with-excel-2013-power-pivot-and-power-query/

He demonstrates how you’re able to analyze all activity on your Facebook (company) page by using Power BI.

You’re able to connect to Facebook and retrieve data by using Power Query:
Power-Query-From-Facebook

The end result is a Bing-map that displays the number of posts by location and gender:
"Facebook-Power-BI-number-of-posts-by-location-and-gender"

Imagine what else is possible!

Here’s a basic YouTube tutorial:



Join us at LinkedIn!
Don’t forget to join the ‘Microsoft Power BI‘ group on LinkedIn and stay updated!:
linkedin-microsoft-power-bi-group








Advertisements

T-SQL: Generate set based update statements easily!

CTRL+H (Replace) and use a Regex:

[,]\ +[a-z]+(\(+[0-9]+[,]*[0-9]+\))*[,]\>

Let me demonstrate, here’s my dummy table:

Dummy-Update-Table
Generate the standard UPDATE-scripts via SSMS:

SSMS-Generate-Update-Statement
And here’s the outcome:

SSMS-Generated-Update-Statement-result
Now hit CTRL+H to open the ‘find and replace’ window.
Paste in the Regex-statement and click on the checkbox for ‘Use‘ and select ‘Regular expressions

SSMS-Find-and-Replace-Regular-Expressions
Click on ‘Replace all‘ and have a look at the outcome:

SSMS-Generated-Update-Statement-After-RegEx
Almost done…click on the checkbox for ‘Use‘ again and deselect ‘Regular expressions‘ (disable). Now hit CTRL+H again and now replace the ‘<‘-character with ‘new.’:

SSMS-Find-and-Replace-New-Alias
The UPDATE-statement should now look like:

SSMS-Generated-Update-Statement-With-Alias
Final step is to add the FROM and WHERE-clauses as shown below:

SSMS-Generated-Update-Statement-Final-Result

You’re done! How easy huh?








List all columns with a specific data type in a database

Found this script and it’s a great addition to your cookbook!

An example for AdventureWorksDW2012:
Columns-with-a-specific-data-type-sql-server


SELECT
s.name AS SchemaName,
ts.name AS TableName,
c.name AS ColumnName,
SCHEMA_NAME(t.schema_id) AS DataTypeSchema,
t.name AS DataTypeName,
t.Is_user_defined,
t.Is_assembly_type,
c.Is_nullable,
c.Max_length,
c.Precision,
c.Scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
WHERE t.name = 'money'
ORDER BY 1,2,3