Written by:

Written by:

Thomas Szigeti

BI, PowerBI, Power Query…

Do we have to be afraid?
What are theese?

If you work with Excel you probably met with BI guys who are “Business Intelligence” experts and they are connecting you to fancy databases…

I want to talk about this a little bit to dispell the myth that is surrounding data science.

If you have been working with Excel you probably seen some excel files that are connected to some data source.



Data Connection abstract

In essence these data models are exactly the same as when you refer to a table with your data in it.
But really a data query is there to bring some shaped data in to the picture that you can use.

In the old days it was a hole shebang with guys writing the query to these databases with code.

Since Microsoft put PowerQuery in place our life is a lot more easier.
Don’t get me wrong. Power query is still a beast and you have to learn DAX formulas (Data Analyst Expresions) but if you familiar with Excel formulas you can pick it up relatively easy.  Also, there is the whole documentation of these DAX formulas are available at Microsoft’s DAX function reference page.


Why I wanted to put this lighthearted post in place?

I wanted to make it availabel for you so yoou won’t shoot blank when they ask you about these topics on your Excel Interview.

What is BI?

BI stands for Business Intelligence

If you join an organisation and they have a BI Team, they will supply you with some structured data, from Databases, Data warehouses and so on. 

Don’t have to be afraid, most of the time connecting to a database can be sorted on the Data tab->Get Data. 
The Database will have credentials, these credentials either supplied to you, or you will be loggin on to the database with your network logon. 

Once you connected the data will load in to a table, a Data model a Pivot Table or a Pivot chart. The choice is yours.  

What is MS PowerQuery?

Power Query is an Addon/Plugin for MS Excel

Power query is a graphical representation of data manipulation. Like I said in the old days, you would have to express your desire to remove a column, with a piece of code. In PowerQuery you can click a button, and the code is written for you in the query.

What is PowerBI?

PowerBI is a relatively new piece of software from Microsoft

It is a great tool to create dashboards that can define complex data models, and complex filters with visuals that are attached to the data. 

It’s biggest feature is the drilldown function which means that a dashboard can be configured so when a piece of data is clicked on a visual it will be filtered down, and can be drilled down. I.e: You click on a month in a chart and that month gets filtered. 

Very much like a Pivot Slicer. 

What to take away from this.

By enlarge, modern data manipulation tools are working very much like your Pivot table panels. Now this is a very broad statement, but if you familiar with Pivot tables and Power query you can and will be able to handle data connections.


Come and visit us.

Come and visit excelangel on Facebook to find and join the growing community of true office workers.

Oh, I almost forgot.
Drop us a like while you there…

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]