Archive

Archive for the ‘Excel’ Category

Follow Up of Chris Webb’s post of “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query” – Using Dynamic Value

November 28, 2014 Leave a comment

Another great blog post of “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query” from Chris Webb.

In his post, he showed a way to use a dropdown instead of a plain text box to prompt the user to select the value for the parameter of a Power Query function.

In his code between line 13 and 17:

MySecondNumberParamType = type number
                        meta
                        [Documentation.Description = "Please choose a number",
                         Documentation.AllowedValues = {1..5}],    

The allowed value 1,2,3,4,5 is hard-coded. I wonder can I use a Power Query function to dynamically populate the AllowedValues instead? So I did the following test:

  1. Create a single column table in Excel, looks like the blow.
    ID
    =============
    1
    2
    3


    10000
  2. Add this table to Power Query (Power Query ribbon –> From Table) and name this Power Query as “Table_Param”
  3. Change the column type to Text. This is important, see blow:
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", type text}})
    
  4. Go to Chris’s Power Query “MultipleTwoNumbersV2” and open the advanced editor
  5. Change the code from
    Documentation.AllowedValues = {1..5}], 
    

    to

    Documentation.AllowedValues = Table.ToList(Table_Param)],
    
  6. Now try to invoke this Power Query function and you will find the dropdown box having the value return form the function.

This dynamic value approach should enable you to get the data from various sources e.g. database, Excel, web etc

However, there is two limitations:

  • The data type in Table_Param must be Text. And this is highlighted in the above step 3. Therefore, you need to either perform an inline type cast or a change data type step in the Power Query.
  • Although the Table.Tolist() support large volume of data, the number of the allowedvalue populated in the dropdown box will only be first 1000 items. Well, in most cases, that’s more than enough.

 

 

 

 

Advertisements
Categories: Excel, Power Query Tags: ,

Use Power Query to Generate Date Table with Australian Financial Year and Holiday Flag

November 13, 2014 Leave a comment

This is not a new technic as Chris Webb and Matt Mason have both blogged about formulas for generating  a date table using Power Query. Darren Gosbell has another blog for ISO weeks and 4-4-5 period. They are all excellent articles that you can reference as use straight away.

In my recent project, I need to filter out the data with Australian Financial Year (FY) and holidays. So I wrote my own version of the Power Query to get it done. I’ll share the Excel 2013 workbook below for you to download.


I’ll briefly talk about how I do it. You can view the actual Power Query code in my shared Excel 2013 workbook.

Step 1 – Take Matt Masson’s Power Query as the start point and add FY calculation.

Step 2 – Find Australian Holiday information. I could find any useful one by just use Power Query online search. Jump to Google search and find http://australia.gov.au/topics/australian-facts-and-figures/public-holidays. I then use the Power Query to load it and perform all sorts of data manipulation to make it ready for merging with my base query. Having said that, for demo purpose, it is good. In reality, I would prefer to load it once and save it as a static table somewhere for reference. As you can imagine, the Power Query will break if the web page changes and for such small volume, automation does not add much value to manual maintenance.

Step 3 – Merging the Power Query produced from Step 1 and 2 and do the final cleansing.

Enjoy!

Categories: BI, Excel, Power Query Tags: , ,

Displaying Top N, Bottom N, and “All Others” – Dynamically

November 12, 2014 12 comments

I just read Rob Collie’s post Displaying Top N, Bottom N, and “All Others”. Its a great post with lots of details.

I’ve come up with another approach that use the Set to achieve the similar outcome but dynamically. The final solution looks like the below

Dynamic TopN and AllOthers

The base data looks like the below:

base data

Both table as added to Model as the linked table.

I created the following calculated measure in the Power Pivot Model:

SelectedN:=MAX([N])

Total Sales:=SUM(Sales[Sales])

Rank:=RANKX(All(Sales),[Total Sales])

Top N Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]<=[SelectedN]))

All Other Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]>[SelectedN]))

In addition to the DAX fomula, I created two sets using MDX

Excel OLAP Set

Set Name: TopN

Set Definition: TOPCOUNT([Sales].[CustomerID].Children, [Measures].[SelectedN], [Measures].[Sum of Sales])

Set Name: AllOthers

Set Definition: [Sales].[CustomerID].Children – [TopN]

* Both of the above two sets has the checkbox “Recalculate set with every update” checked.

You can download the workbook (Excel 2013 version) at: https://onedrive.live.com/?cid=8BA997FE18F7FDAE&id=8BA997FE18F7FDAE%212373

Categories: BI, BISM Tabular, DAX, Excel, PowerPivot Tags: ,

Floor Map Visualisation

November 6, 2014 3 comments

In a recent POC project, I have to produce the data visualization over custom images, namely, floor maps. I’ll briefly talk about what I did for Excel 2013 and Tableau 8.2.4
Excel 2013

Excel 2013 floor map visulisation

The only option is to use VBA Macro. I know, I know, VBA should be the past and we should avoid using it. However, with Excel 2013, it is the only option. Basically, I insert a image (floor map, PNG format preferred). “Format” ribbon –> “Color” –> “Set to Transparent Color”. Once you got the floor map displayed, “Insert” ribbon –> insert a shape to overlay the shape on top of the floor map. You can also set the shape to be transparent, as shown below. Of course, in the final solution, the transparent level should be controlled via the VBA code.

Excel Format Shape Pane

Excel 2013 allow you to assign Macro to all your shapes and custom images and text box. This is the entry point you can do some very powerful interactive features to your workbook.

 

 

Tableau 8.2

Tableau is no doubt the best commercial data visualization software and the overall dashboard design experience is awesome.

Tableau 8.2 floor map visulisation

Tableau 8.2 floor map visulisation

Categories: BI, Excel, Power BI, Tableau Tags: , ,