Power BI - Clean, Transform, and Load Data Flashcards
How do you promote headers?
You can promote headers in two ways:
- By selecting the Use First Row as Headers option on the Home tab
- By selecting the drop-down button next to Column1 and then selecting Use First Row as Headers.
How do you rename column headers?
You can rename column headers in two ways.
- Right-click the header, select Rename, edit the name, and then press Enter.
- Double-click the column header and overwrite the name with the correct name.
What do the load and transform tools allow you to do?
- Load: Automatically load your data into a Power BI model in its current state.
- Transform Data: Open your data in Microsoft Power Query, where you can perform actions such as deleting unnecessary rows or columns, grouping your data, removing errors, and many other data quality tasks.
What do you need to do to correct column names that are not interpreted as headers?
When a table is created in Power BI Desktop, Power Query Editor assumes that all data belongs in table rows. However, a data source might have a first row that contains column names. To correct this inaccuracy, you need to promote the first table row into column headers.
How early do you want to remove unnecessary columns in the data shaping process?
A key step in the data shaping process is to remove unnecessary columns. It is much better to remove columns as early as possible. One way to remove columns would be to limit the column when you get data from data source. For instance, if you are extracting data from a relational database by using SQL, you would want to limit the column that you extract by using a column list in the SELECT statement.
What does Unpivot Columns do?
Turns columns to Rows; Name, Values
What does Pivot Columns do?
Turns Name,Value Rows to Columns
What does unpivoting do with respect to creating DAX measures?
Unpivoting streamlines the process of creating DAX measures on the data later. By completing this process, you have now created a simpler way of slicing the data.
What are your options in the Navigator window?
You now have the option to select the Load button to automatically load your data into the Power BI model or select the Transform Data button to launch the Power Query Editor, where you can review and clean your data before loading it into the Power BI model.
What is Group by columns?
The process of shaping data by converting your flat data into a table that contains an aggregation value for each unique value in a column.
What is the purpose of data profiling?
Profiling data is about studying the nuances of the data: determining anomalies, examining and developing the underlying data structures, and querying data statistics such as row counts, value distributions, minimum and maximum values, averages, and so on.
Why is data profiling important?
This concept is important because it allows you to shape and organize the data so that interacting with the data and identifying the distribution of the data is uncomplicated, therefore helping to make your task of working with the data on the front end to develop report elements near effortless.
How do you examine the data structures?
Before you begin examining the data in Power Query Editor, you should first learn about the underlying data structures that data is organized in. You can view the current data model under the Model tab on Power BI Desktop.
How does Power BI determine data anomalies?
Power Query Editor determines data anomalies by using the Column Distribution feature.
How many rows does Power Query examine by default? How do you increase?
By default, Power Query examines the first 1000 rows of your data set. To change this, select the profiling status in the status bar and select Column profiling based on entire data set.
