Tag: Power Query

VBA With Power Query: Maximize Efficiency and Automate Your Data Processes in Excel (with VBA code)

Posted on by bonbon

What are the benefits of using VBA with Power Query?

Using VBA in conjunction with Power Query can provide significant benefits, especially when you need to automate complex workflows, integrate data from multiple sources, or perform advanced transformations programmatically. Here are the key benefits of using VBA over just using Power Query directly:

  1. Automation and Repetition:
    • Automation: VBA allows you to automate repetitive tasks, such as importing data from multiple files, applying the same transformations, and saving the results in a consistent format.
    • Scheduling: You can schedule VBA macros to run at specific times, ensuring data is updated automatically.
  2. Customization and Flexibility:
    • Custom Functions: VBA enables you to create custom functions and procedures that can be used within Power Query M code.
    • Dynamic Parameters: You can pass dynamic parameters to Power Query queries using VBA, allowing for more flexible data processing. This alone is a huge benefit!!
  3. Integration with Other Applications:
    • Interoperability: VBA can interact with other applications and services, such as databases, web APIs, and email clients, enhancing the capabilities of Power Query.
    • Data Export: You can use VBA to export data to various formats, such as PDF, CSV, or other Excel files, after it has been processed by Power Query.
  4. Complex Logic and Control:
    • Conditional Logic: VBA provides powerful conditional logic and control structures that can be used to handle complex data processing tasks.
    • Error Handling: You can implement robust error handling in VBA to manage unexpected issues during data processing.
  5. User Interface:
    • Custom UserForms: VBA allows you to create custom user interfaces (UserForms) for data entry and interaction, making it easier for users to perform complex tasks without needing to know Power Query M code.
    • Buttons and Macros: You can add buttons and macros to Excel worksheets to trigger VBA scripts, making it user-friendly.
  6. Advanced Data Manipulation:
    • Data Cleaning: VBA can be used for advanced data cleaning tasks, such as removing specific patterns, handling missing data, and normalizing data formats.
    • Data Transformation: VBA can perform complex transformations that might be difficult or impossible to achieve with Power Query alone.
  7. Version Control and Collaboration:
    • Version Control: VBA code can be version-controlled using tools like Git, allowing for better collaboration and tracking changes.
    • Shared Macros: You can share VBA macros with your team, ensuring consistency in data processing workflows. This can be especially helpful for vacation coverage or spreading the workload among multiple team members!
  8. Performance Optimization:
    • Efficiency: For large datasets, VBA can be more efficient in certain scenarios, especially when combined with Power Query for initial data loading and filtering.
    • Resource Management: VBA can manage system resources more effectively, ensuring smooth performance during data processing.

When to Use VBA Over Power Query

  • Complex Workflows: When you need to perform a series of complex transformations and data manipulations that are difficult to achieve with Power Query alone.
  • Integration with Other Systems: When you need to integrate Excel with other applications, databases, or web services.
  • Automated Reporting: When you need to automate the generation of reports and dashboards based on dynamic data sources.
  • Custom User Interfaces: When you need to create custom user interfaces for data entry and interaction.
  • Advanced Error Handling: When you need robust error handling and logging for data processing tasks.

When to Use Power Query Alone

  • Simple Data Transformation: When you need to perform simple data transformations and cleaning tasks.
  • Data Visualization: When you need to create dynamic data visualizations and dashboards.
  • Data Integration: When you need to integrate and combine data from multiple sources without complex logic.
  • Data Refresh: When you need to refresh data regularly from external sources.
  • Data Transformation: Power Query allows for complex data transformations, such as filtering, merging, and aggregating data.
  • Refreshable Data: Data imported using Power Query can be easily refreshed to update with new data.
  • Scalability: Power Query is better suited for larger datasets and more complex data processing tasks.

VBA with Power Query Code Samples with Explanations

Use Case: Importing Data from CSV Files Using Power Query

Power Query is a more advanced and flexible tool for data import and transformation in Excel. It allows for more complex data transformations and can handle larger datasets more efficiently. Here’s how you can use VBA to import a CSV file using VBA with Power Query.

End Sub

Explanation of Power Query VBA Code

  1. File Path and Connection Name:
    • filePath is the path to your CSV file
    • connName is the name of the Power Query connection.
  2. Delete Existing Connection:
    • The code checks if the connection already exists and deletes it to avoid conflicts.
  3. Create New Power Query Connection:
    • The Queries.Add method creates a new Power Query connection.
    • The Formula parameter specifies the Power Query M code to import and transform the CSV file.
  4. Load Query into Worksheet:
    • A new worksheet is created, and the query is loaded into it using LoadFromText

Use Case: Importing and Transforming Data from an Excel File Using Power Query and VBA

Objective: Import data from a specific worksheet in an Excel file, filter out rows with specific criteria, perform some transformations, and load the cleaned data into a new worksheet.

Preparation:

  1. Prepare the Source Excel File:
    • Ensure your source Excel file is located at a known path, e.g., C:\Data\source_data.xlsx.
    • Ensure the data is in a worksheet named SalesData.
  2. VBA Code to Import and Transform Data Using Power Query:

Explanation of the VBA Code

  1. Set File Path, Workbook Name, Sheet Name, and Names:
    • sourceFilePath is the path to your source Excel file.
    • sourceWorkbookName is the name of the source Excel file.
    • sourceSheetName is the name of the worksheet containing the data.
    • connName is the name of the initial Power Query connection.
    • queryName is the name of the transformed Power Query query.
  2. Delete Existing Connection and Query:
    • The code checks if the connection and query already exist and deletes them to avoid conflicts.
  3. Create a New Power Query Connection:
    • The Queries.Add method creates a new Power Query connection to import data from the specified worksheet in the Excel file.
    • The Formula parameter specifies the Power Query M code to import the data.
  4. Create a New Power Query Query for Transformation:
    • This query uses the initial connection as its source.
    • It skips the first row (assuming headers).
    • It removes duplicates based on specified columns.
    • It filters rows where Sales is greater than 100.
    • It sorts the filtered rows by Date in ascending order.
    • It adds a new column TotalSales calculated as Sales * Quantity.
  5. Load the Transformed Query into a New Worksheet:
    • A new worksheet is created, and the transformed query is loaded into it using LoadFromText.
  6. Format the Worksheet:
    • The code automatically fits the columns and applies a table style for better readability.

Detailed Steps

  1. Prepare the Source Excel File:
    • Update the path to match your source Excel file.
    • Ensure the data is in a worksheet named SalesData, or update that variable to match your data.
  2. Open VBA Editor:
    • Press Alt + F11 to open the VBA editor.
  3. Insert a New Module:
    • In the VBA editor, go to Insert > Module to create a new module.
  4. Copy and Paste the VBA Code:
    • Copy the above VBA code and paste it into the module.
  5. Run the Macro:
    • Close the VBA editor and return to Excel.
    • Press Alt + F8, select ImportAndTransformExcelDataWithPowerQuery, and click Run.
  6. View the Results:
    • A new worksheet named TransformedData will be created, showing the imported and transformed data.

Additional Tips

  • Customizing Columns:
    • You can customize the column names and the criteria for filtering and sorting based on your specific dataset.
  • Handling Different Workbooks and Sheets:
    • Adjust the sourceFilePath, sourceWorkbookName, and sourceSheetName variables to match your source file and worksheet.
  • Error Handling:
    • Add error handling to manage potential issues, such as file not found or invalid data.

Example: Combining VBA with Power Query for Automation of Reporting

Here’s a more detailed example that combines VBA with Power Query to automate a complex data processing workflow that can automate data reporting from start to finish, including transforming the data and outputting a user-friendly report.

Use Case: Automating Data Import, Transformation, and Reporting

Objective: Import data from multiple Excel files, perform transformations, and generate a consolidated report.

VBA Code:

Explanation of the VBA Code

  1. Set Folder Path and Names:
    • folderPath is the path to the folder containing the Excel files.
    • connName is the name of the initial Power Query connection.
    • queryName is the name of the transformed Power Query query.
  2. Delete Existing Connection and Query:
    • The code checks if the connection and query already exist and deletes them to avoid conflicts.
  3. Initialize a New Worksheet:
    • A new worksheet named ConsolidatedData is created to store the consolidated data.
  4. Loop Through Excel Files:
    • The code loops through all Excel files in the specified folder.
    • For each file, it creates a new Power Query connection to import data from the SalesData worksheet.
  5. Create a New Power Query Query for Transformation:
    • This query uses the initial connection as its source.
    • It skips the first row (assuming headers).
    • It removes duplicates based on specified columns.
    • It filters rows where Sales is greater than 100.
    • It sorts the filtered rows by Date in ascending order.
    • It adds a new column TotalSales calculated as Sales * Quantity.
  6. Load the Transformed Query into the Consolidated Worksheet:
    • The transformed data is loaded into the ConsolidatedData worksheet.
  7. Delete the Power Query Connection and Query:
    • After processing each file, the connection and query are deleted to clean up.
  8. Format the Worksheet:
    • The code automatically fits the columns and applies a table style for better readability.

Conclusion

Using VBA in conjunction with Power Query provides a powerful combination for automating and managing complex data workflows. While Power Query is excellent for data transformation and integration, VBA offers the flexibility and control needed for advanced automation and integration tasks. By combining these tools, you can create robust and efficient data processing solutions.

Feel free to comment other examples you would like to see as we continue to explore automation of workflows via both VBA and Power Query!

How To Filter Data Using Anti Join In Excel With Power Query

Posted on by Annie D

Automating your data filtering and cleaning processes can be such a huge time saver. For today’s video, George is going demonstrate how you can compare and merge queries by using Anti Join in Excel with Power Query.

***** Video Details *****
00:00 Continue reading “How To Filter Data Using Anti Join In Excel With Power Query” →

rank in Access

Solved: Pass a Parameter to a SQL Query in Power Query

Posted on by Bb

I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query.  After much trial and error, I finally have found a solution that works.  Here’s the quick and easy way to do this and harness the power of query folding.  This solution works in Excel – the process is a bit different in Power BI.

Please note that this post assumes you are at least moderately familiar with Power Query and SQL querying. Your mileage may vary depending on your situation.

In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].

Create the parameter:

  • Add a table, with a single record and column.  This can be on any tab in your workbook.
  • Add this table into Power Query by selecting the table, then Data > From Table/Range.  When the query editor opens, if necessary, change the data type (in my case I need it to be text).
  • Right click on the record and select “Drill Down”.  Since you have only a single record, this step will automatically create a parameter.
  • In the properties, I name this SParameter (you may enter the name of your choice for your parameter).  Note that the name is case sensitive.

Create the SQL query:

Build your SQL query as normal, but note that we are not going to put a where clause in referencing the new parameter here (see “Why Not Include the Parameter in the SQL Statement” section below).  Here’s my simple query example – notice that I’m not including the store field in the criteria section of the query though this is the field my parameter will ultimately filter on.

Select * FROM Storelist WHERE OpenDate < GetDate()-365

If you think like me, you may be pausing here – my table has millions of records and I don’t want them all pulling in – don’t worry, this is the beauty of the process.

  • Write your SQL statement in Power Query as noted above.
  • When the Query Editor returns the columns and record sampling, for the field you will be using your parameter, filter with any single value (Text Filters > Equals). This is simply a placeholder, to create the Power Query M formula for the next step.
  • You will now see that filtering in the formula bar. 
  • Replace the placeholder value with the name of your parameter, removing any quotes from the placeholder.  This is how it looks for me: = Table.SelectRows(Source, each [Store] = SParameter).
  • Power Query is smart enough to modify its native query to use the parameter, so it’s not going to pull in the millions of records and then filter after the fact.  Success!!  This is the power of Query Folding!
  • Side note – if you are wondering about GetDate(), this is the SQL current system date, so in a way this is technically a dynamic parameter as well.  In my case, the query will only return stores with an open date that is older than 365 days based on today’s date.  You may find this useful for your query as well.

Why Not Include the Parameter in the SQL Statement?

In the SQL statement, logic would lead us to add the store number/SParameter as part of the criteria in the select statement.  It may seem counter intuitive, but we cannot successfully put the parameter directly into the SQL code/query.  I have tried several times, but the challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).

If you are tempted to test this yourself, the format would be Select * FROM Storelist WHERE Open < GetDate()-365 and Store = “&SParameter&”.  You will likely run into this error “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”

I truly hope this post helped you out.  If so, please feel free to leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post. Also, feel free to share this with someone else who may find it useful.

Please bookmark and subscribe to my blog!  I am always curating and adding new, relevant content! Thanks so much!

Also, be sure to check out Dose for Excel (click image below)!  Add over 100 functions to Excel to increase your productivity and more!  They have a free trial right now, so you can try it out today!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in.  Thanks for supporting my blog!

Dose for Excel - +100 Functions

Power Query Filter Rows by NOT Contains Criteria – Single Formula Solution

Posted on by Annie D

Learn how to filter a table based on NOT Contains Criteria. Download an example workbook here.

Easy step by step instructions below.  See a single formula solution using the functions Splitter.SplitByAnyDelimiter, List.Count and Table.SelectRows. Amazing formula solution from Power Query Poet, Bill Szysz.

Please watch the video if you’d like for a guided walkthrough and also another method that can be useful for multiple tables.  

Criteria Table

  1. Create a new, separate table with the list of terms you will want to exclude. Name the table “NoCriteria”.
  2. Add the excluded item (NoCriteria) table to Power Query – click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section.
  3. In the Power Query window, select the Transform menu and click convert to list.
  4. Under the File menu, choose Close and Load To, then choose Connection Only.

Building the Filtering via Power Query

  1. Add or create the list/table that will ultimately be filtered.
  2. Click any cell within the table that will be filtered. Add the excluded item table to Power Query (click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section).
  3. In the Power Query window, click the Add Column menu, and select Custom Column.
  4. In the window that opens, type this:

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)

  5. Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.
  6. Select the statement you have typed in and copy it (you will need to paste this formula in a following step).
  7. Click OK. You will see that the formula you typed was changed by the program and a column was added.
  8. To change the formula back, click the menu bar, highlight the entire text and then replace by pasting in the formula you copied. Hit enter.
  9. The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.
  10. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

Any time changes are made to the exclusion list, you will need to refresh the filtered table. Simply right click any cell within the filtered table, and select Refresh.

 

Bonus – Filtered table with the excluded items only (not shown in video)

You can additionally create a filtered table that only includes the terms in your NoCriteria table!  

  1. To do this, go into Power Query.  Right click on your filtered table and click Duplicate.
  2. In that new table, you will very slightly change the existing formula in your Power Query to not equal one (see orange text):

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1)

  3. Hit enter.  The table should now only filter on the items in your exclusion list, instead of including them.
  4. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

View on YouTube

FILTERING-OUT-CONDITIONAL-LIST-1-1Download

Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula

Posted on by Annie D

Download Excel Finished Files: http://bit.ly/2K9SNfn
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure.
Topics:
1. (00:07) Introduction
2. (00:50) Sort Method
3. (02:09) Excel Worksheet Formulas, including AGGREGATE and AVERAGEIFS functions.
4. (05:27) LARGE Function and New Excel Office 365 Calculation Engine
5. (08:15) Power Query M Code, including Table.Sort, List.Average and List.FirstN M Code functions
6. (12:46) Power Query Parameter Query with condition coming from Excel Worksheet
7. (14:05) Power Pivot & Power BI DAX Measure, including TOPN, CALCULATE and AVERAGE DAX Functions
8. (17:08) DAX Parameter from Excel Worksheet using VALUES DAX Function.
9. (18:43) Add new records to table and test formulas
10. (19:00) Fix Power Query Table.Sort bug using Dummy Insert Column
11. (20:08) Fix Power Query Table.Sort bug using Table.Sort function inside Table.Group Function
12. (22:12) Conclusion

View on YouTube

Using Locale in Power Query Power BI: Import & Append Text Files from Different Countries – MSPTDA 12

Posted on by Annie D

Download Excel START File: https://ift.tt/2I5RStl
Download Zipped Folder with Text Files: https://ift.tt/2pwvCRa
Download Excel FINISHED File: https://ift.tt/2Ic3LhL
Download Power BI Desktop FINISHED File: https://ift.tt/2psTK74
Download pdf Notes about Power Query: https://ift.tt/2I7y2y9

Comprehensive video about using Locale Settings so that Power Query interprets Dates and Numbers from different parts of the world correctly. In this Video learn about how to use the “Using Locale…” Feature and Regional Settings to import Text Files from Different Countries so that Dates and Numbers in Different Formats can be interpreted correct, and the multiple Text Files and be Appended into a single table. Also see how to change the Locale settings on individual columns.

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
1. (00:15) Introduction
2. (00:25) Text Files from Different Countries have Different Date and Number Formats
3. (02:40) Change Regional Settings in Power Query and Power BI Desktop
4. (04:28) Using Locale… Feature on Single Columns to interpret Dates and Numbers Correctly
5. (06:50) Convert ISO Dates to Proper Dates in Power Query
6. (08:04) Power BI Desktop: Import Multiple Text Files with Different Date and Number Formats From Folder and Append. See 1) Create Table in Power BI Desktop, 2) Build Custom Function 3) Import Text Files From Folder and Append
7. (20:30) Summary

Assigned Homework:
Download pdf file with homework description: https://ift.tt/2psyxu0
Zipped Text Files: https://ift.tt/2I9op1C
Example of Finished Homework in Excel: https://ift.tt/2pvzUZ5

View on YouTube

Which Power Query Steps Are Used in SQL Query Folding? “View Native Query” feature! – MSPTDA 11.5

Posted on by Annie D

Download Excel FINISHED File: https://ift.tt/2wZE5jF
Download Power BI Desktop FINISHED File: https://ift.tt/2O4YUkv
Download pdf Notes about Power Query: https://ift.tt/2wZVp8t

In this Video discusses the new “View Native Query” feature in Power BI Desktop Power Query and Office 365 Excel Power Query to determine which of the Applied Steps are sent back to the SQL Server Database as part of Query Folding.

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Power Query to Import from SQL Server Database in Excel or Power BI Desktop – MSPTDA 11

Posted on by Annie D

Download Excel START File: https://ift.tt/2Mfed8h
Download Excel FINISHED File: https://ift.tt/2wZE5jF
Download Power BI Desktop FINISHED File: https://ift.tt/2O4YUkv
Download pdf Notes about Power Query: https://ift.tt/2wZVp8t
Practice Problems: Assigned Homework:
Download homework file (Practice Problems) : https://ift.tt/2O5PODZ
Example of Finished Homework: https://ift.tt/2O5PODZ

In this Video learn how to connect to an SQL Server Database and extract and transform data using Power Query in Excel and Power BI Desktop.

Topics:
1. (00:16) Introduction
2. (00:32) What is an SQL Server Database
3. (02:19) The Goal of our Queries and a look at the end result reports in Excel
4. (03:04) Comparing and Contrast using 1) Using Power Query User Interface or 2) Writing SQL Code in Power Query
5. (04:46) Example 1: Use Power Query User Interface to connect to SQL Server and Extract, Transform and Load Data.
6. (11:27) Example 2: Write SQL Code to connect to SQL Server and Extract, Transform and Load Data.
7. (14:44) Example 3: Using Power BI Desktop to connect to SQL Server and Import multiple Tables.
8. (18:29) Summary

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)

Posted on by Annie D

Learn how to deal with Power Query Error: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Two solutions are presented in this video.
Download Files: Excel Start: https://ift.tt/2L7OwpO
Zipped Folder: https://ift.tt/2PShOvY
Download Excel FINISHED Files: https://ift.tt/2MsL7Hs
Download pdf Notes about Power Query: https://ift.tt/2wkNW2K
Assigned Homework – these are problems for you to practice your new M Code skills:
Download Excel File with Homework: https://ift.tt/2MmtxFf
Example of Finished Homework: https://ift.tt/2L7OxtS

Chris Webb’s blog about this topic: https://ift.tt/2NATkG3
Ken Puls blog about this topic: https://ift.tt/2PShRb8

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column – MSPTDA 10

Posted on by Annie D

Download Power BI Desktop START File: https://ift.tt/2BXmQEV
Download Power BI Desktop FINISHED File: https://ift.tt/2MZj7e5
Download pdf Notes about Power Query: https://ift.tt/2BXmSg1
Download Excel File with parallel Excel Example: https://ift.tt/2ojpNpi
Assigned Homework:
Download pdf file with homework description: https://ift.tt/2PfRSts
Example of Finished Homework in Power BI Desktop: https://ift.tt/2ojpP0o

In this Video learn Power Query M Code and Custom Functions to calculate Moving Annual Toatls.
Topics:
1. (00:15) Introduction
2. (01:10) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation.
3. (02:07) Thanks to Bill Szysz for Custom Function.
4. (02:18) Excel Example of Moving Annual Total
5. (03:30) Why Power Query and not Excel or DAX?
6. (03:43) Look at final solution and Custom Function to see what we are trying to accomplish, including a method to filter a table with in a Custom Column in Another Table and have the formula see criteria from the the Inner Table and the Outer Table.
7. (05:37) Step 1: Look at how we imported files
8. (06:07) Step 2: Extract a Sorted Unique List from the source Facet Table. Use Production Operator to get a List, then use the Table.Distinct and Table.Sort functions.
9. (07:31) Step 3: M Code to create a Crossjoin of all combinations of Months and Product Names with the steps: Extract Column, Convert to Start of Month, Extract Min and Max Dates, use List.Dates function to create range of dates, then merge using Custom Column to get all combinations of Months and dates.
10. (14:39) Step 4: Group BY Date and Product to get Monthly Totals.
11. (16:25) Step 5: Create Final Table with the steps: Merge Step 3 and Step 4, Remove Nulls, Add Custom Column to get One Year Back.
12. (20:15) Step 5: Sort and how it is different than Excel Sport.
13. (21:25) Step 5: Table.Buffer Function allows us to Buffer the Internal Table to prevent a call to the source table for every row in the table.
14. (22:22) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT).
15. (28:41) Add new data to test if everything updates
16. (29:06) Summary

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

MSPTDA 09 Power Query Complete M Code Introduction: Values, let, Lookup, Functions, Parameters, More

Posted on by Annie D

Download Excel START Files: https://ift.tt/2L7OwpO
Download Excel FINISHED Files: https://ift.tt/2MsL7Hs
Download pdf Notes about Power Query: https://ift.tt/2wkNW2K
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2MmtxFf
Example of Finished Homework: https://ift.tt/2L7OxtS

In this Video learn the basics of M Code, the computer language behind queries in Power Query.
Topics:
1. (00:15) Introduction
2. (03:46) Edit M Code: Applied Steps
3. (03:46) Edit M Code: Formula Bar
4. (03:46) Edit M Code: Advanced Editor
5. (09:50) Expressions
6. (09:50) let expressions
7. (17:34) Comments in M Code
8. (21:11) Values: Primitive, List, Record, Table, Function
9. (30:45) Lookup or Projection and Selection. Learn about Row Index Lookup and Key Match lookup
10. (42:50) Primary Keys
11. (50:20) Custom Functions
12. (57:44) Parmenter Queries
13. (01;02:27) Underscore Character _
14. (01:06:17) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

MSPTDA 08.5: Power Query Group By Unique List or Consecutive Occurrences

Posted on by Annie D

Download Excel START Files: https://ift.tt/2MqR0km
Download Excel FINISHED Files: https://ift.tt/2MfNGfr
Download pdf Notes about Power Query: https://ift.tt/2veIr4P
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2M4R2Sl
Example of Finished Homework: https://ift.tt/2OjHnob

In this Video learn how to use Power Query’s Group By feature to Group By and create a unique list with aggregate calculations or create a Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations.
Topics:
1. (00:15) Introduction
2. (00:37) What is Group By Report based on Consecutive Occurrences?
3. (01:27) Group By feature to Group By and create a unique list with aggregate calculations
4. (03:15) Learn about how Gear Icon can Disappear when you alter the M Code, which means the dialog box disappears.
5. (05:12) Learn about the difference between Duplicating a Query and Referencing a Query.
6. (05:12) Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations. Use the forth argument and GroupKind.Local
7. (07:27) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube