| Understanding |
| I do not know if the excel contains a whole suite of BI tools, what are those tools |
| The main purpose of power query is to get data and do the ETL |
| How to access the power query tools |
| How to find what data sources power query can take |
| How to get data from a folder, How to read from multiple files all at once |
| How to open the query editor window |
| How to open the query editor by creating a query |
| How to open the query editor from the data tab of excel |
| Where can we find query editing tools |
| Where can we find the formula bar |
| Where we can find the data preview |
| Where can we find the properties |
| Where can we name our table |
| Where can we find the applied steps |
| Where are all the three query editor tools |
| How to load data from power query into excel |
| How to load data to table |
| How to add data to data model |
| How to create a lookup table |
| Basic table transformations |
| How to load a csv file |
| First Check your table name after you load into the query. Name not long or confusing, no spaces |
| Always differentiate your lookup table from your data table by appending the name to the table xxx_lookup |
| Second check the headers ( specially their data types) |
| How to change the data type of a column |
| How to remove a column |
| How to sort a column |
| How to close and load |
| How to look quickly at the loaded data |
| How to save a project |
| Use "trim" to eliminate loading & trailing spaces, or "Clean" to remove non-printable characters |
| Run your data through a trim |
| You can see same tools in both "Transform" and "add column" . Use the tools in add column : when adding a new column, "tansform": when modifying an existing columns |
| How to open an excel file or load an excel file |
| How to make a column uppercase instead of lowercase |
| How to merge several columns by concatenating to create a single one |
| How to edit a step |
| How to extract the first three letters of a text into a new column |
| How to extract digit upto a certain characteris |
| What are number specific tools |
| How to convert a number type to text type |
| To count the number of Unique entries are there in a column |
| How to get the average of a numeric column |
| How to add a column by applying multiplication by a scalar |
| How to rename the applied steps |
| How to round up the values in a column |
| Which function is used to find the aging |
| How to strip the time component of time stamp |
| Which function is used to extract year/quarter/month/week/day |
| Which function is used to find the earliest date from a column |
| Which function is used to find the latest date from a column |
| Date Specific Query Editing Tools |
| How to create a basic calendar table from a single column of date table |
| How to make the week start from Monday |
| Steps to create rolling calendar means given a start date it creates a rolling calendar till today's date |
| How to create unique IDs with the help of Index columns |
How to add a column based on certain condition or how to call 'small' 'medium' 'large' based on some condition |
| How to drag a column to some other position |
| How to create a condition based on quantity |
| Grouping and aggregating data |
| How to group a column by another category column |
| Avoid using group by instead use power pivot |
| How to group a column by a month etc |
| Modify Excel workbook queries |
| How to change a query |
| Merging Queries |
| Avoid merging tables, keep them separate and use relationship |
| How to merge two tables based on a common factor |
| How to Append two or more queries: appending rows one after the other |
| Use the From Folder query option to autometically append all the queries withing the same folder |
| How to append queries |
| How to append all the files in a folder |
| Power Query Best Practices |
| What is piovting and unpivoting |
| Pivoting and unpivoting a table |
| Difference between transpose and pivoting/unpivoting |
| How to pivot/unpivot a table |
No comments:
Post a Comment