Get data - Module 1,2 Flashcards
Flat file location
Local, OneDrive for Buisness and Personal, SharePoint - TeamSites
Difference between One Drive Business and Personal
personal OneDrive account get many of the same benefits that you would with OneDrive for Business. However, you’ll need to sign in with your personal OneDrive account, and select the Keep me signed in option.
Difference between One Drive and SharePoint
Saving your Power BI Desktop files to SharePoint Team Sites is similar to saving to OneDrive for Business. The main difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.
How to change the source
Data source settings, Query setting, Advanced Editor
How to get data from Relational Data Sources
https://learn.microsoft.com/en-us/training/modules/get-data/3-relational-data
What is a Parameter
A parameter serves as a way to easily store and manage a value that can be reused. Parameters give you the flexibility to dynamically change the output of your queries depending on their value, and can be used for: 1. Changing the argument values for particular transforms and data source functions. 2. Inputs in custom functions.
What is a Function
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order or structure.
How to create Dynamic Reports with single value
https://learn.microsoft.com/en-us/training/modules/get-data/3b-parameters
How to create Dynamic Reports with multiple value
https://learn.microsoft.com/en-us/training/modules/get-data/3b-parameters
How to import NoSQL database
https://learn.microsoft.com/en-us/training/modules/get-data/4-nosql-database
Type of Storage Mode and Definition
Import, DirectQuery, Dual - https://learn.microsoft.com/en-us/training/modules/get-data/6-storage-mode
Differences between Azure Analysis Services and SQL Server
Analysis Services models have calculations already created. If you don’t need an entire table, you can query the data directly. Instead of using Transact-SQL (T-SQL) to query the data, like you would in SQL Server, you can use multi-dimensional expressions (MDX) or data analysis expressions (DAX).
what is Connect Live in Azure Analysis Service
Connect live is an option for Azure Analysis Services. Azure Analysis Services uses the tabular model and DAX to build calculations, similar to Power BI. These models are compatible with one another. Using the Connect live option helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI. Azure Analysis Services can have a fast refresh schedule, which means that when data is refreshed in the service, Power BI reports will immediately be updated, without the need to initiate a Power BI refresh schedule. This process can improve the timeliness of the data in your report.
What is Query Folding
Query folding is the process by which the transformations and edits that you make in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you’re actively making transformations. The reason for implementing this process is to ensure that these transformations can take place in the original data source server and don’t overwhelm Power BI computing resources.
Benefits of Query Folding
More efficiency in data refreshes and incremental refreshes. Automatic compatibility with DirectQuery and Dual storage modes.
How to know Query Folding is enable
If the View Native Query option isn’t available (not displayed in bold type), then query folding isn’t possible for this step, and you’ll have to work backward in the Applied Steps area until you reach the step in which View Native Query is available (displays in bold type). This process will reveal the native query that is used to transform the dataset.
Native queries aren’t possible for the following transformations:
Adding an index column. Merging and appending columns of different tables with two different sources. Changing the data type of a column
Power BI Query Error: Timeout expired
This error indicates that you’ve pulled too much data according to your organization’s policies. Administrators incorporate this policy to avoid slowing down a different application or suite of applications that might also be using that database. You can resolve this error by pulling fewer columns or rows from a single table. While you’re writing SQL statements, it might be a common practice to include groupings and aggregations. You can also join multiple tables in a single SQL statement.
What is Power Query Editor
Power Query Editor in Power BI Desktop allows you to shape (transform) your imported data. You can accomplish actions such as renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, and much more. It is important to shape your data to ensure that it meets your needs and is suitable for use in reports.
Difference between Pivot and Unpivot
Pivot aggregates or reshape data based on one or more columns, creating new columns for each unique value in those columns. This is often done to summarize or consolidate data.. Unpivot reshape data so that you have fewer columns and more rows, making it easier to analyze and work with.
Difference between Transpose and Unpivot
In Transpose, Each row in the original data becomes a column in the transposed data, and each column becomes a row. This operation is useful when you want to present data in a different layout for analysis or reporting. Unpivoting is often used when you have data with multiple columns representing different attributes or dimensions, and you want to consolidate them into a single column while retaining their values.
How to combine multiple tables into a single table
https://learn.microsoft.com/en-us/training/modules/clean-data-power-bi/5-combine-tables
Profile data in Power BI
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. Determines 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.
Column quality
shows you the percentages of data that is valid, in error, and empty. In an ideal situation, you want 100 percent of the data to be valid.
Column distribution
shows you the distribution of the data within the column and the counts of distinct and unique values, both of which can tell you details about the data counts. Distinct values are all the different values in a column, including duplicates and null values, while unique values do not include duplicates or nulls.
Column profile
gives you a more in-depth look into the statistics within the columns. 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. ]
What is Minimum and Maximum value in Column Profile
the minimum value is the first value and the maximum value is the last value when in alphabetical order.
Value distribution
Tells you the counts for each distinct value in that specific column. Value distribution feature allows you to pinpoint a place to begin your investigation into why this is so.
Column Statistics Include?
Count, Error, Empty, Distinct, Unique, Empty String, Min, Max
What is M code
M Query is a “mashup” query language that can be used to query a lot of data from many sources. It is used in the first part of the data import process for Power BI Desktop, which is when data is loaded into the data model and queries are run in the background using M.
Difference between M and Dax Language
DAX is used to create calculations and aggregations within the data model, whereas M is used to query and transform data from various sources before loading them into the data model.
What data cleaning we can perform in Power Query
We can Remove Columns, rows, Replace errors and Value, Promote first row as header, Change Data type, Split or Merge column, Trim, Group by, Transpose, Pivot, Unpivot, Merge, Append, Extract Value, Fill up or Down, Reverse Rows and Count rows
