Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for May 2014

Implementing A Basic LIKE/Wildcard Search Function In Power Query

with 3 comments

Last week someone asked me whether it was possible to do the equivalent of a SQL LIKE filter in Power Query. Unfortunately there isn’t a function to do this in the standard library but, as always, it is possible to write some M code to do this. Here’s what I came up while I was waiting around at the stables during my daughter’s horse-riding lesson. At the moment it only supports the % wildcard character; also I can’t guarantee that it’s the most efficient implementation or indeed 100% bug-free, but it seems to work fine as far as I can see…

let

    Like = (Phrase as text, Pattern as text) => 

let

    //Split pattern up into a list using % as a delimiter

    PatternList = Text.Split(Pattern, "%"),

    //if the first character in the pattern is %

    //then the first item in the list is an empty string

    StartsWithWc = (List.First(PatternList)=""),

    //if the last character in the pattern is %

    //then the last item in the list is an empty string

    EndsWithWc = (List.Last(PatternList)=""),

    //if the first character is not %

    //then we have to match the first string in the pattern

    //with the opening characters of the phrase

    StartsTest = if (StartsWithWc=false) 

       then Text.StartsWith(Phrase, List.First(PatternList)) 

       else true,

    //if the last item is not %

    //then we have to match the final string in the pattern

    //with the final characters of the phrase

    EndsText = if (EndsWithWc=false) 

       then Text.EndsWith(Phrase, List.Last(PatternList)) 

       else true,

    //now we also need to check that each string in the pattern appears 

    //in the correct order in the phrase

    //and to do this we need to declare a function PhraseFind

    PhraseFind = (Phrase as text, SearchString as list) =>

    let

     //does the first string in the pattern appear in the phrase?

     StringPos = Text.PositionOf(Phrase, SearchString{0}, Occurrence.First),

     PhraseFindOutput = 

                 if

                 //if string not find then return false 

                 (StringPos=-1) 

                 then false 

                 else if

                 //we have found the string in the pattern, and

                 //if this is the last string in the pattern, return true

                 List.Count(SearchString)=1

                 then true

                 else

                 //if it isn't the last string in the pattern

                 //test the next string in the pattern by removing

                 //the first string from the pattern list

                 //and all text up to and including the string we have found in the phrase

                 (true and

                 @PhraseFind(

                 Text.RemoveRange(Phrase, 0, StringPos + Text.Length(SearchString{0})),

                 List.RemoveRange(SearchString, 0, 1)))

     in

      PhraseFindOutput,

    //return true if we have passed all tests    

    Output = StartsTest and EndsText and PhraseFind(Phrase, PatternList) 

in

    Output

in

    Like

 

Using the following test data:

image

I can run the following query:

let

    Source = Excel.CurrentWorkbook(){[Name="Phrases"]}[Content],

    ChangedType = Table.TransformColumnTypes(Source,{{"Phrases", type text}}),

    InsertedCustom = Table.AddColumn(ChangedType, "Test", each Like([Phrases],"%cat%sat%mat%"))

in

    InsertedCustom

 

And get this output:

image

You can download the sample workbook here.

I know the Power Query team have been asked for this several times already, but it would be really useful if we could package up functions like this and make it easy to share them publicly with other Power Query users…

Written by Chris Webb

May 27, 2014 at 2:19 pm

Posted in Power BI, Power Query

Expanding All Columns In A Table In Power Query

with 4 comments

When I’m working with XML files, or web pages, or any data with columns containing nested tables in Power Query, I often end up having to expand every expandable column in the table and then expanding any new columns that are revealed after that to find the data that I’m looking for. This is a such a pain I thought I’d write a function to do it for me – which is the subject of this post.

For example, consider the following XML:

image

If you load this into Power Query you will see the following table created for the first step:

image

To get to a table where all of the data is visible requires clicking on the expand icons in the address and the employees columns (highlighted), and then three more clicks after that. Sigh.

Here’s my function, called ExpandAll, to expand all the columns in a table that can be expanded:

let

    //Define function taking two parameters - a table and an optional column number 

    Source = (TableToExpand as table, optional ColumnNumber as number) =>

    let

     //If the column number is missing, make it 0

     ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,

     //Find the column name relating to the column number

     ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},

     //Get a list containing all of the values in the column

     ColumnContents = Table.Column(TableToExpand, ColumnName),

     //Iterate over each value in the column and then

     //If the value is of type table get a list of all of the columns in the table

     //Then get a distinct list of all of these column names

     ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, 

                        each if _ is table then Table.ColumnNames(_) else {}))),

     //Append the original column name to the front of each of these column names

     NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),

     //Is there anything to expand in this column?

     CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,

     //If this column can be expanded, then expand it

     ExpandedTable = if CanExpandCurrentColumn 

                         then 

                         Table.ExpandTableColumn(TableToExpand, ColumnName, 

                                ColumnsToExpand, NewColumnNames) 

                         else 

                         TableToExpand,

     //If the column has been expanded then keep the column number the same, otherwise add one to it

     NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,

     //If the column number is now greater than the number of columns in the table

     //Then return the table as it is

     //Else call the ExpandAll function recursively with the expanded table

     OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) 

                        then 

                        ExpandedTable 

                        else 

                        ExpandAll(ExpandedTable, NextColumnNumber)

    in

     OutputTable

in

    Source

 

You can then use this function on the XML file shown above as follows:

let

    //Load XML file

    Source = Xml.Tables(File.Contents("C:\Users\Chris\Documents\PQ XML Expand All Demo.xml")),

    ChangedType = Table.TransformColumnTypes(Source,{{"companyname", type text}}),

    //Call the ExpandAll function to expand all columns

    Output = ExpandAll(ChangedType)

in

    Output

 

And bingo, in one step, you get everything:

image

You can download the sample workbook here.

Written by Chris Webb

May 21, 2014 at 8:29 pm

Posted in Power Query

BI Survey 14

with 2 comments

It’s that time of year again: the BI Survey, the world’s biggest annual survey of BI users, is looking for your feedback. If you use any Microsoft BI tools they want to know what you think of them – and that applies to you too, Power Pivot/Power Query/Power View/Power BI fans!

Here’s the link to use:

https://digiumenterprise.com/answer/?link=1905-6T9AM9S9

If you take the survey you’ll get a summary of the results and a chance to win some Amazon gift vouchers. For promoting the survey here I get to blog in detail about the results when they come out, and the results are always interesting. I’m particularly looking forward to seeing what people will say about Power BI.

Written by Chris Webb

May 16, 2014 at 9:32 am

Posted in BI

More Thoughts On The New Power BI Features

with 18 comments

My last post on the new Power BI features announced at the PASS BA Conference was not much more than a list of bullet points written during the keynote. Now that the conference is over and I’ve had a bit more time to reflect, I thought I would try to come to some conclusions about what they all mean. And, of course, indulge in some wild speculation too.

Cross-platform

Microsoft’s new-found enthusiasm for working on multiple platforms is clear from two things: first, the announcement that the iOS Power BI app will be coming soon (though we should not forget that this was promised a long, long time ago and is very late), followed by native apps for other mobile platforms; and secondly the work that has gone into the HTML 5 version of Power View. Indeed, the latter was demonstrated using Google Chrome to underline the point. Of course this is the only commercially sane direction to take but it’s very welcome nonetheless.

Power View new features

The cool new Data Exploration features in Power View, which allow you to edit existing Power View reports – creating new graphs and tables and merging existing ones – are I think only going to be available in HTML 5 Power View running inside a Power BI site. The same goes for the new time series forecasting functionality. The demos also seemed to make a point of the touch-friendly interface. Now I can’t imagine that Excel Power View will move from Silverlight to HTML 5 any time soon (the Office team are notoriously conservative when it comes to big changes like this) so maybe we should assume that, going forward, the main focus will be the use of Power View inside a Power BI site to build reports and dashboards rather than Power View inside Excel on the desktop? Maybe the HTML 5 version of Power View will be what is used in the touch-optimised version of Office that is slated to appear this summer? Who knows. I liked what I saw though and these additions will go a long way towards boosting Power View’s credibility as a client tool.

Time series forecasting

It seemed like Microsoft had abandoned ‘data mining for the masses’ after SSAS data mining failed to take off, but clearly not. The time series forecasting functionality seems very easy to use (you can read more about it here and here) and I got the impression that other algorithms might be added soon – maybe Project Sage is relevant here? Another question to ask is whether ease-of-use was the real reason why ‘data mining for the masses’ failed to take off the first time around? It might have been part of the reason but another factor must surely be that business users don’t trust predictions when they don’t understand how they are made, while the data scientists and statisticians are already using other tools that give them a lot more control for forecasting.

RIP PerformancePoint?

The first reaction of several people at the BA Conference (me included) to the new dashboard and KPI creation features in Power BI sites was that this was the replacement for PerformancePoint. Personally I never liked PerformancePoint much and rarely used it, and it doesn’t seem that MS has had much enthusiasm for it in recent years. I don’t think we’ll ever see it in the cloud either. Killing it off would have the added benefit of removing a client tool from a stack that has a confusing number of client tool options right now. However I got the impression that the dashboard and KPI creation features in Power BI sites, as demoed, were fairly basic and they may not be much more than widgets that can be placed on the Power BI home screen with nothing to tie them together, but I don’t know enough to judge properly. I think it might be better to think of Power View as the place to build dashboards.

Integration with old-school SQL Server BI

The ability of Power BI sites to host SSRS reports, and for Power View to connect back to SSAS on-prem, is an important bridge between what most of Microsoft’s BI customers are actually using and the new world of cloud-based Power BI that Microsoft is promoting. For me this was the biggest announcement of all. Will these customers be interested in buying Power BI licences for their users if SSRS and on-prem SSAS is all they want to use though? I don’t know, but I assume that this will also enable mobile access to SSRS and SSAS via the Power BI mobile apps, so that will be a plus for some customers, and all the new Power View functionality makes it quite an attractive web-based reporting tool for SSAS users. The per user cost of a Power BI licence might make it too expensive to buy if all you want are web-based dashboards and mobile support but customers who are already checking out Power BI for self-service will be more likely to buy because of this.

Q&A

New features in Q&A were not mentioned in the keynote on Thursday, but on Friday afternoon I saw an interesting session that detailed some of the new functionality coming in Q&A later this year including the support for phrasing that is mentioned in this blog post. It’s clear that Q&A is getting a lot of love at MS and technically it is very impressive. I’m still not totally convinced that this is something people actually want or will use but I’m less cynical than I was. I also smell a lot of consultancy money in building and tuning models for Q&A if it does get popular (the session showed that there are a lot more features coming that will help with tuning). If I understand correctly, the output from Q&A is basically a Power View report which can then be edited manually if you wish; this means that Q&A should not be thought of as a standalone tool but as one of the ways that you can start to build a Power View report, and Q&A will benefit from all of the new features that are going to be added to Power View.

Timelines

Apart from the time series forecasting, which is available now, ‘this summer’ was the most common response to all questions about when the new stuff would be available. Maybe all of these features will be released when the introductory pricing period for Power BI ends? Or in time for the Worldwide Partner Conference in mid July, as Jen Underwood suggests? Hopefully it won’t be in the middle of my summer holidays.

Written by Chris Webb

May 11, 2014 at 10:11 pm

Posted in Power BI

Power BI Announcements At The PASS BA Conference

with 8 comments

This morning I was wondering whether we’d see any cool new stuff announced at the PASS BA Conference this year, or whether we’d see the same old Power BI demos yet again. It turns out there were a whole load of announcements, some of them very cool indeed – and here’s a brief summary:

  • A native Power BI mobile for iOS will be available by the end of the summer. Other platforms (which means Android I guess) will come soon after that.
  • SSRS will be available in Power BI sites and will be able to connect back to on-prem data sources. This is big, in my opinion – it will be very attractive for a lot of existing MS BI customers. Also the way to get SSRS reports on mobile?
  • Power View in Power BI will be able to connect back to SSAS on premises (just Tabular though, or Multidimensional too?)
  • A new KPI editor in the Power BI site will allow you not only create KPIs but arrange them to create dashboards. This looks like the replacement for PerformancePoint.
  • Time series forecasting is available in Power View online now. It’s available in charts and you just forecast by dragging the chart forward; outliers can be corrected easily. Will need to check this out later.
  • Power View is getting a treemap visualisation.
  • Power View is getting a new Data Exploration mode that allows you to edit reports in the browser. This has a lot of cool new stuff, such as the ability to drag data points out of existing charts to create new charts.

Lots to follow up on there… more blog posts on this coming soon, I promise!

Written by Chris Webb

May 8, 2014 at 5:24 pm

Posted in Events, Power BI

Displaying Help For A Power Query Function

with one comment

Matt Masson and Theresa Palmer gave an excellent presentation on Power Query and M for the PASS DW/BI Virtual Chapter a few days ago (hopefully it will be on their YouTube channel soon). One thing that they showed which I hadn’t seen before was that you can display help for a function in M’s built in library simply by typing its name in the formula bar.

Take, for example, the Text.Replace() function. With a new blank query, if you create a step with the following definition:

= Text.Replace

You will see help and examples as shown below:

image

By doing this you are creating a step that returns the function itself – note that this is not the same as invoking the function, although it does mean you can invoke the function in a subsequent step. If you do decide to use the function you just need to click Invoke and a dialog will appear to prompt you for the values to pass to the function:

image

Clicking OK will show the output of the function:

image

Here’s the full M code for the query for those of you who are curious:

let

    Source = Text.Replace,

    InvokedSource = Source("the cat sat on the mat", "cat", "dog")

in

    InvokedSource

A very useful tip! Unfortunately you can’t specify help text for your own functions yet, although Matt did say it was something they wanted to do.

Written by Chris Webb

May 1, 2014 at 9:30 am

Posted in Power Query

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers