To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel). However, there is a huge overlap between the two exams resulting from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.
We, at G Com Solutions, recommend to the clients to whom we supply Power BI Training that they prepare for both exams simultaneously and then sit both exams on the same day. We also run a 5 day Power BI MCSA Certification Prep course in London which includes both exams 70-778 and 70-779.
In this series of blog posts, we will be examining some of the key features of Power BI, Power Pivot and Power Query which you will need to master in order to pass the two exams.
In this topic, which applies equally to both Power BI and Excel Power Query, we will compare the Table.SelectColumns and Table.RemoveColumns functions.
“If I add new columns to a data source will these new columns be loaded into Power BI on refresh?” Well, it depends on the syntax used in your query and, in particular, the choices you make when specifying the columns which you want to retain and those which you wish to remove.
The Table.SelectColumns function is used to determine which columns in a table are loaded into the data model. It corresponds to the interface command Home > Manage Columns > Choose Columns.
It is also the syntax which is generated when you select the columns you wish to retain and use the command Home > Manage Columns > Remove Columns > Remove Other Columns.
When you use the Table.SelectColumns command, you are asking Power Query to load specific columns with each refresh. This means that, if new columns are created within the data source, they will not be loaded.
By contrast, if you specify the columns you wish to keep by using the Table.Remove columns command, any new columns created in the data source will be loaded on refresh. The Table.RemoveColumns command is generated by selecting one or more columns and using the command Home > Manage Columns > Remove Columns > Remove Columns.
Table.SelectColumns or Table.RemoveColumns(
table as table,
columns as any,
optional missingField as any
) as table
The table whose columns you wish to select.
A list of columns which will be included in the resulting table.
The policy regarding any columns in the list which are not present in the data. Possible options are:
Return an error. This means that the query will be “broken” and will not produce a usable table.
This option is the complete opposite of MissingField.Error. It produces a table which simply ignores the non-existent column reference.
A useful alternative to producing an error, this option creates the named column but fills it with null values; thus, highlighting the error while still producing a usable table.