Connect any number of tables together via a common column


One of the key powers of Power BI is the ability to bring data from different sources into a single model and then join them together by creating relationships among tables to perform analysis and create reports across all the data in the unified model. But a lot of people are confused when they run into one of the errors below while trying to create a relationship between two tables inside Power BI Desktop:

  • We cannot create a relationship between ‘Table A'[Column X] and ‘Table B'[Column Y]. This could be because there is missing intermediate data to connect the two columns.
  • You can’t create a relationship between these two columns because one of the columns must have unique values.
  • You can’t create a relationship between these two columns because the ‘Column X’ column in the ‘Table A’ table contains null values.

Those who are familiar with relational database theories can appreciate the sound principles underlying…

View original post 374 more words

Categories: BI

Power Query List.Accumulate – Unleashed


Following a reader’s request, today we will unleash the power of List.Accumulate.

The official documentation on List.Accumulate here was very confusing for me:

List.Accumulate(list as list, seed as any, accumulator as function)as any
Argument Description
list The List to check.
seed The initial value seed.
accumulator The value accumulator function.

OK. I understood the list argument quite right, but the other two arguments were post-nuclear-bomb science fiction.

So let’s try to understand the example that was used in the official page:

// This accumulates the sum of the numbers in the list provided.
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) equals 15

Oh, so the code above sums up all the elements in the list. That is nice. Let’s make sure the calculation was done right. 1+2+3+4+5 = 15. Yes, this is right🙂

To prove that the code works, we can paste it to the Query Editor after creating a blank query:

View original post 918 more words

Categories: BI

Embed Tableau into Microsoft Power Point 2016

April 29, 2016 1 comment

In a recent project, I was asked to embed a Tableau dashboard into Microsoft PowerPoint. Also, as we use SharePoint Online, the embedded Tableau dashboard must work in the PowerPoint Online environment.

So far, the best option I found is to use an free Office Store app called “Web Viewer”: link

Once installed, you can find the icon, as shown below:Office_Store_App_Web_Viewer

Launch the app and specify the URL in the below format

With Tableau Toolbar:
https://<Tableau View URL>?:embed=y&:toolbar=yes

Without Tableau Toolbar:
https://<Tableau View URL>?:embed=y&:toolbar=no

This will allow embedding the Tableau view into the Power Point slides.


  • Microsoft official app for Power Point
  • Tableau dashboard remains fully interactive
  • Works in Office 365 and SharePoint Online environment


  • Tableau Server Single Sign On is required for seamless experience
  • As the “web viewer” only takes HTTPS for security reason, you must configure SSL and enable https on Tableau Server
  • Once SSL is enabled, you need to install the security certificate on your mobile device to get the Tableau mobile app works. Refer to the Tableau document for more details.



Categories: BI, Tableau Tags: ,

Re: Are Tableau and Power BI the future of Business Intelligence?

August 20, 2015 1 comment

Recently, I come across a LinkedIn post regarding MS Power BI and Tableau: Here

I love both and I posted my comment:

No doubt Tableau offers the best interactive data visualization front end but is weak at data preparation. You always need to use Tableau together with at least one data preparation package such as Alteryx, which is popular in high end Tableau community. With the recent release of Tableau 9.0’s LOD calculation,it is closing the gap with traditional OLAP technology such as SQL Server Analysis Services Multi-dimensional mode. But not there yet, for example, the lacking of SCOPE assignment (cell value overriding). But I do admit, using Tableau is addictive. It is an art, not just a tool.

Power BI is different. Within Power BI stack, Microsoft Power Query has VERY strong self service data preparing (ETL) and sharing (shared query repository, a.k.a Data Catalog) capabilities. Power Pivot, on the other hand, is a very powerful true (multi-tables) data modelling engine. You can build full dimensional modelling in Power Pivot and you can scale up from desktop based Power Pivot to server based SQL Server Analysis Services Tabular cube. Combined with the next generation SQL Server 2016, it is deeply integrated into the whole Microsoft Data Platform not just BI but whole data management landscape.

Categories: BI, Power BI, Power Query, Tableau

Use Power Query to connect to Salesforce custom domain

February 14, 2015 2 comments

Power Query Salesforce connector is great but it does not support Salesforce custom domain at the moment.

Well, that is the limitation of using the PQ UI. You can actually make it work if you use M directly.

With the help of Curt Hagenlocher from Microsoft, I figured out how. What he said:

You should be able to use a custom domain in Power Query itself by manually editing the formula to include the login domain name. That is, instead of
You’d have



Categories: BI, Power Query Tags: ,

A thought-provoking post – The Future of Data Warehousing: 7 Industry Experts Share Their Predictions

January 7, 2015 Leave a comment

The Future of Data Warehousing: 7 Industry Experts Share Their Predictions discusses the modern data warehouse architecture and emerging trends.

Categories: BI Tags:

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
                        [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.

  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}], 


    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.





Categories: Excel, Power Query Tags: ,