Speaking at SQL Saturday #588 NYC #SQLSATNYC

I still hasn’t really sunk completely in but I have been selected as a speaker at SQL Saturday #588 in New York.

The session line up is amazing and with a lot of Power BI celebs – both Adam Saxton and Patrick Leblanc are speaking as well – so I fell a little nervous but extremely proud

My session is called “Power Query – Don’t be afraid of the advanced editor” and will be about how you can make your Power Queries do even more and make them more dynamic and flexible. This can be done by knowing the M language a lit bit better and use it in ways you perhaps hadn’t thought of before.

I will show some of the cases and examples where the interface in the query editor wasn’t capable of solving my problems but using the advanced editor could.

So, join me on a ride into the advanced editor and hopefully I can teach you how to

  • Use variables in M
  • Refer to other queries and columns
  • Make a dynamic date table
  • Handle different number of columns in your datafiles
  • Handle different headers in your datafiles
  • Discover some of the hidden parameters in the M commands

 

Read about the event here – http://www.sqlsaturday.com/588/eventhome.aspx

 

 

Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

Just wanted to share a quick tip I just stumbled upon when designing a data model in Excel Power Pivot.

For creating a measure in Power Pivot, I place the cursor under the column and then I click the AutoSum button on the Home tab –

And then I get the formula created quick and I can modify the name.

But did you know that if you multiselect several cells in the Calculation Area and click the AutoSum – you get formulas for all the columns you have selected

 

Hope you find this useful as well …

 

 

 

Create your own Help for all the Power query functions help in Power BI Desktop

I have used a lot of time in the Power Query Formula specification help on the web – and several time for the same function – so I ended up switching between browser and Power BI Desktop.

Then I recalled reading this blogpost – http://www.excelguru.ca/blog/2015/05/13/what-power-query-functions-exist/ and decided to put the help directly in as a Query in the “Edit Queries” window.

Now I can filter the functions by name and/or when I click the “Function” in the Value column – I get the help about the function below.

The steps to create your own table is like this

And for you to copy

let

Source = #shared,

#”Converted to Table” = Record.ToTable(Source)

in

#”Converted to Table”

Hope this can help you as well.

Get selected items in a slicer in Excel 2016

My most popular post so far has been “How to get selected items in a slicer without VBA” – Link – and after reading Chris Web’s post about the new Excel 2016 ConcatenateX function in DAX – link – I thought that this function could be used to calculate the selected items in a slicer as well.

And it turned out the function is the perfect solution to do this – and very easy to implement

First I created two simple tables – one with sales and one product table

Add it to the data model via the PowerPivot tab – and I add a calculated measure Sales Value as SUM([SalesValue]).

Now Add a Pivottable and a slicer to filter the table by Product.

Now I add a calculated measure to calculate the selected items

Adding the measure to the pivottable will result in this

But you might want it as a measure in the pivottable but the value stated in a single cell.

This can be done by using the CUBE functions – Use the following formula to accomplish this

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Selected Products]”,Slicer_Product)

The two member expressions in the formula refers to the measure and then the name of the slicer.

It has never been easier to retrieve the selected items from the slicers with the new CONCATENATEX function..

You can download the example file here

Decorating the X-mas tree using Power Query, Power Pivot and Power Map #PowerBI

I really feel like the Power BI has supplied us with many nice presents during the year – by delivering the rapid development of the Power BI tools.

So why not try to pay a little back by using some of the tools to create a solution to decorate the Christmas tree with lights.

Of course, we need some data to do this – so I build a table in Excel to enter the information

You can enter the start time and interval of adding the lights and set the duration of adding the light.

In the color column, you can add the color of the light.

The lights will be positioned according to the X and Y coordinates – and in order to get them right I used Paint to get them

In this example – 263, 493 as you can see in the status bar.

Next up – Start power map in Excel – and use the recently added new Custom map feature

Remember to select Pixel Space so the max for x and y is set to the dimensions of the picture.

In order to make the lights appear one by one we have to use the timer function in Power Map but then the data will have to contain a record for each time – and for that we use Power Query to create a list of records to make the lights appear slowly.

So switch to Power Query and use the table as source

But I want each light to appear slowly – so I need to have number of rows for each light as stated in the Max column – in order to do this I use the List.Numbers function

For each row I then get a list of values from 1 to Max

Expand this

Then I create a calculated timer column

Where I get a time for each Intensity.

Add this to the datamodel of the Workbook and add a measure to sum the Intensity

Now we have the data ready to decorate the tree in Power Map

Link the X and Y to the X and Y Coordinate

Add the sum of intensity to Size, the color as Category and Timer as Time.

On the Layer options in the settings area – change the size and thickness in order to adjust the sizes – and in the color choose the right combinations of Color category and Map color.

And then you are ready to decorate the x-mas tree 🙂

 

Here is a link to the file – the file

And the video – Link

And Merry Christmas to all – and especially to the Power Bi Team 🙂

 

Using Power BI to extract data from Office 365 Reporting web services #powerbi #office365

Office 365 supplies a web service that enables you to extract a lot of information on email and spam, antivirus activity, compliance status and Lync Online activities – you can read more about this here.

I am one of the administratosr of our companys Office 365 account , and decided to see if I could use the Power BI tools to extract the information.

The web service contains many different reports where you can extract a lot of information.

In this example, I will show you how to extract information about inbound and outbound mail traffic per mailbox.

Using Power Query

So open a blank workbook and switch to the Power Query tab and select “From Web”

The web service report I want to use is called “MailTrafficSummary” – details about Fields and OData options can be found here

https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom

As specified in the parameters I want it returned as Atom meaning that we will get an xml file back from the web service. Because I am the administrator I will not get prompted for a username and password.

The file contains information about the message count per user and the structure can be seen below.

 

So right click the binary file and choose to open the file as XML

 

The XML is returned and we can begin to drill down for find the entry element we are interested in.

So after expanding and removing columns I have the nodes I am interested in

Then I use the transformation of the data to the proper datatypes and add columns for date and time values that could be relevant to group the data by.

Here are the steps applied

let

Source = Xml.Tables(Web.Contents(“https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom”)),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“id”, type text}, {“updated”, type datetime}}),

#”Expand entry” = Table.ExpandTableColumn(#”Changed Type”, “entry”, {“content”}, {“entry.content”}),

#”Expand entry.content” = Table.ExpandTableColumn(#”Expand entry”, “entry.content”, {“Attribute:type”, “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”}, {“entry.content.Attribute:type”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” = Table.ExpandTableColumn(#”Expand entry.content”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”, {“properties”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”, {“http://schemas.microsoft.com/ado/2007/08/dataservices”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”}),

#”Removed Columns” = Table.RemoveColumns(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”,{“id”, “title”, “updated”, “link”, “entry.content.Attribute:type”, “http://www.w3.org/XML/1998/namespace”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices” = Table.ExpandTableColumn(#”Removed Columns”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”, {“Date”, “Name”, “Direction”, “MessageCount”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Name”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Direction”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, {“Element:Text”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date.Element:Text”}),

#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”, {“Element:Text”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount.Element:Text”}),

#”Renamed Columns” = Table.RenameColumns(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”,{{“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount.Element:Text”, “Messagecount”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Direction”, “Direction”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Name”, “Mailbox”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date.Element:Text”, “DateTime”}}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“DateTime”, type datetime}, {“Messagecount”, Int64.Type}}),

#”Inserted Day of Week” = Table.AddColumn(#”Changed Type1″, “DayOfWeek”, each Date.DayOfWeek([DateTime]), type number),

#”Inserted Hour” = Table.AddColumn(#”Inserted Day of Week”, “Hour”, each Time.Hour([DateTime]), type number),

#”Inserted Week of Year” = Table.AddColumn(#”Inserted Hour”, “WeekOfYear”, each Date.WeekOfYear([DateTime]), type number)

in

#”Inserted Week of Year”

 

After all these steps I get a nice clean table

 

This is returned to the data model of the workbook.

Then I add a measure to the model to summarize the MessageCount field.

 

And then we can start visualize the mail traffic in Excel …

 

So as you can see its fairly straight forward to get data from the usage of your Office 365 subscription into Excel and use the Power BI tools to analyze the data.

So check out all the other options/reports in the Office 365 Reporting web service and see if there is something you can use in your reporting.

 

 

 

 

 

 

 

 

 

 

The Power Query update for August is late ? #powerbi

The updates to Power Query has a monthly update cycle and I decided to see if I could use Power Query and Power View to analyze when to be ready at http://blogs.msdn.com/b/powerbi/ to be the first to tweet the news.

So by using the RSS feed as an XML table in Power Query – I was able to create a power pivot model and visualize it with Power View in Excel.

Here is what I discovered about the latest 25 blog posts from the Power BI team.

  • Expect the blog post to turn up at 16:00 GMT
  • Don’t expect a blog post on a Friday – there is a much better change on a Tuesday or Wednesday
  • Expect the update to Power Query to be at the end of the month J

You can download the model here – and make let’s keep our fingers crossed for yet an update to Power Query – hopefully arriving at 16:00 GMT today – J

#SQLSat275 – Prepare your models for Power BI Q&A

Yesterday I presented a session on how to prepare your Excel Power Pivot model for Power BI’s Q&A functions at the SQL Saturday 275 – Copenhagen.

I have uploaded my presentation and the optimized model of one of my example files used in the demo – you can download it here.

The example file contains public data for the position, size, KWH of all the Danish windmills in denmark.

I would like to thank the organizers of #SQLSat 275 for another great event and I look forward to the 2015 SQL saturday Copenhagen event.

 

Presenting at SQL Saturday 275 Copenhagen #sqlsat275

I am looking forward to saturday where I have been selected to give a session about Power BI Q & A – link

I really look forward to demonstrate the possiblities for the participants, and discuss how Q & A can fit in the Self Service BI toolpack.

I also presented last year at SQL Saturday and really had a great time – and if you don’t know what to do on saturday – hurry up and sign up for this free SQL server event – you won’t be disappointed.

http://sqlsaturday.com/275/

 

“You do not have a model or the model is empty” – Not really true – #PowerPivot

Power Pivot in Excel sometimes gives me this frustrating model even though my workbook actually does have a Power Pivot model, when I clicked Calculated Fields – Manage Calculated Fields.

Previously I then closed the workbook and Excel and tried again and it usually helped.

But there is actually a workaround that doesn’t require a restart of Excel.

If you click ok in the dialog – then choose Calculated Fields and “New Calculated Field” … the Calculated field dialog appears

 

Click Cancel and try the “Manage Calculated Fields” again … and now Power Pivot suddenly can find the model