Power query magic смотреть последние обновления за сегодня на .
Welcome to Power Query Magic ⏬ More information below ⏬ I'm Wyn Hopkins, Microsoft MVP, Power Query enthusiast, Excel and Power BI expert. On this channel I will be meeting with the people who are leaders in sharing their knowledge about Power Query. These are the people whose blogs, books, video and presentations I've been absorbing for many years. I'm excited to find out what makes them tick, how did they arrive at their current situation, and what advice and tips can they impart on us. I want this to be fun, and I want to raise the profile of Power Query by sharing stories from the array of M Code magicians out there. This is Power Query Magic Where to find out more: Podcast Version: Apple Podcast, Spotify and 🤍 Twitter: 🤍 LinkedIn: 🤍 About Me: My Other channel: 🤍 MVP: 🤍 LinkedIn: 🤍
In this video, you will learn about - What is a Record in Power Query - Extracting Records from a Table - Creating Records from Scratch also, there are two practical examples of using Records that might be helpful in creating dynamic solutions. = ONLINE COURSES = ✔️ Mastering DAX in Power BI - 🤍 ✔️ Power Query Course- 🤍 ✔️ Master Excel Step by Step- 🤍 ✔️ Business Intelligence Dashboards- 🤍 = LINKS 🔗 = Blog 📰 - 🤍 Corporate Training 👨🏫 - 🤍 Need my help on a Project 💻- 🤍 Download File - 🤍 = CONTACT 🌐 = Twitter - 🤍 LinkedIn - 🤍 Email - goodly.wordpress🤍gmail.com = CHAPTERS = 0:00 Intro 0:43 Understanding Records in Power Query 1:51 Extracting a Record from a Table 3:01 Creating a Record from Scratch 4:19 Example 1 - Summing existing columns to Create a New Column 6:54 Example 2 - Extract Steps from Power Query 9:37 My Courses = WHO AM I? = A lot of people think that my name is Goodly, it's NOT ;) My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI. Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! - - - - - Music By: "After The Fall" Track Name: "Tears Of Gaia" Published by: Chill Out Records - Source: 🤍 Official After The Fall YouTube Channel Below 🤍 License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) Full license here: 🤍
We thought that DAX was the thing in Power BI. Alex proves the M-M-Magic is really with Power Query! If you wanted to be blown away but what Power Query can do, this is the video for you! Connect with Alex: 🤍 🤍 Power Query folding 🤍 30 Day Challenge: Query Folding 🤍 Power Query M function reference 🤍 📢 Become a member: 🤍 * Want to take your Power BI skills to the next level? We have training courses available to help you with your journey. 🎓 Guy in a Cube courses: 🤍 * LET'S CONNECT! * 🤍 🤍 🤍 🤍 🤍 🤍 *Gear* 🛠 Check out my Tools page - 🤍 #PowerBI #PowerQueryEverything #GuyInACube
In this video, you'll learn about -What is a List -Identifying Lists -Extract & creating a List -Feeding or Plugging in a List = ONLINE COURSES = ✔️ Mastering DAX in Power BI - 🤍 ✔️ Power Query Course- 🤍 ✔️ Master Excel Step by Step- 🤍 ✔️ Business Intelligence Dashboards- 🤍 = LINKS 🔗 = Blog 📰 - 🤍 Corporate Training 👨🏫 - 🤍 Need my help on a Project 💻- 🤍 Download File - 🤍 = CONTACT 🌐 = Twitter - 🤍 LinkedIn - 🤍 Email - goodly.wordpress🤍gmail.com = CHAPTERS = 0:00 Intro 0:32 What is a List? 1:44 Explanation of Example 1 3:09 Example 1 - Working with the Column Headers of a Table 6:49 Explanation of Example 2 8:49 Example 2 - Removing Errors from a Table 12:50 3 Key Learnings 13:43 My Courses = WHO AM I? = A lot of people think that my name is Goodly, it's NOT ;) My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI. Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! - - - - - Music By: "After The Fall" Track Name: "Tears Of Gaia" Published by: Chill Out Records - Source: 🤍 Official After The Fall YouTube Channel Below 🤍 License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) Full license here: 🤍
Lists are a feature or output type in Power Query that doesn't get utilized nearly enough. I recently wrote three articles on my Excel Unplugged blog about the use of Lists to solve big tasks (links below). But with nearly all of those use the List.Contains function. But in this video, we will tackle the age-old problem. How to filter a list of dates to only working days. This is what HR needs for calculating days of leave. We will be utilizing two new Power Query Functions, List.Intersect and List.Count. This tutorial was made using Excel 365, but all methods work with Excel 2010 and above. Keep in mind that for Excel 2010 and 2013 Power Query is a separate AddInn you have to install. Original Excel files used in the Video if you want to follow along: Start File: 🤍 End File: 🤍 Links to other videos and blog posts that are relevant: Filtering a Table to a Dynamic List of Values using Power Query and Power BI - 🤍 FIltering a Table to a Dynamic List of Values - Blog Post: 🤍 Filtering a Table to Top 10 items - Blog Post: 🤍 Dynamic List of Columns to Keep - Blog Post: 🤍 Chapters: 0:00 Excel Olympics Animation 0:08 Introduction to the Video 0:40 Introduction to the problem we will solve 2:52 Importing Tables to Power Query 3:44 Creating the full list of dates 4:06 Basic List Creation Syntax 5:21 Dealing with Errors with "try otherwise" 6:30 List.Intersect in Action 8:10 List.Count 8:35 "try otherwise" again 10:16 Conclusion 12:38 Outro Gear Used on my end: US Links Camera: Canon EOS R6 - 🤍 Microphone: AKG C3000 (Analog Mic) - 🤍 Audio converter: Zoom UAC-2 - 🤍 Laptop: Dell XPS 15 - 🤍 Lights: Elgato Key Light - 🤍 EU Links Camera: Canon EOS R6 - 🤍 Microphone: AKG C3000 (Analog Mic) - 🤍 Audio converter: Zoom UAC-2 - 🤍 Laptop: Dell XPS 15 - 🤍 Lights: Elgato Key Light - 🤍 Green Screen (visible in bloopers): 🤍 Recorded With: Camtasia - 🤍 Subscribe for more Excel tutorials on the Excel Olympics YouTube Channel: 🤍 Subscribe for the Excel Olympics Newsletter: 🤍 Disclaimer: Some links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission with NO additional charge to you! I appreciate your support! #Excel #ExcelOlympics #gasperkamensek
Download Files: Start File: 🤍 🤍 Finish File: 🤍 Download File: 🤍 Goal: Unpivot and Clean Cross Tabulated Tables in multiple Excel Workbook Files and Create a Single Proper Data Set. Create a Power Query Function to repeatedly perform the Clean & Transform Task across many Excel Files. Lean how to use the new Invoke Custom Function option for a Custom Column. Learn many powerful features in Power Query (Get & Transform): 1. (00:16) Introduction: Overview of whole process, including looking at the files that we need to import, clean, transform and consolidate 2. (02:00) Build Custom Power Query Function with the steps listed below (3 – 12) 3. (03:47) Transpose Table (to deal with the fact that there are two column headers with conditions that need to be Unpivoted) 4. (04:30) Promote Headers 5. (04:48) UnPivot Other Columns based on the two columns Date and Fair 6. (05:40) Rename Columns 7. (06:05) Close and Load to “Create Connection Only” 8. (06:34) Duplicate Query 9. (06:52) Look at M Code and how it is set up and automatically written when you use the User Interface in Power Query 10. (08:27) Add lines of M Code to convert duplicated query to a Custom Power Query Function 11. (09:42) Import Files From Folder: Import Multiple Excel Files with Cross Tabulated Tables that need to be cleaned and Unpivoted. 12. (10:34) Transform extension column to lowercase letters and then Filter for only Excel Files with extension “.xlsx” 13. (11:25) Add Custom Column with Excel.Workbook Function to get Excel Objects, such as Sheets. 14. (12:58) Expand Custom Column to show objects, and to expose sheets with the Cross Tabulated Tables. 15. (13:37) Filter to import only Sheet Objects. 16. (14:09) Filter Out Sheet Tabs that contain the word “Sheet” (Sheet Tabs that do not have a Sales Rep Name. 17. (14:30) Remove Other Columns (Not Data or Name) 18. (14:42) Invoke Custom Power Query Function 19. (14:34) Remove Data Column 20. (14:39) Expand Columns 21. (15:55) Set Data Types and rename columns 22. (16:34) Load to Excel Worksheet (Table, Only Create Connection or Data Model) 23. (17:36) Test Updating by dropping new files in our folder 24. (18:20) Summary
Download Files: Start File: 🤍 Finish File: 🤍 See how to use Power Query (Get & Transform) with Input Variables from Excel Sheet to Extract Records and create a Revenue Report. Here are the steps: 1. (00:15) Introduction including Preview of Finished Solution and Preview of steps necessary to accomplish this goal. 2. (02:00) Import and transform Data Set, including removing Columns, Filter For Records and adding an extra Column to calculate Net Revenue using the Number.Round Power Query Function. 3. (06:38) Load Report to Sheet 4. (07:09) Import each Parameter/Criteria Table. 5. (07:36) Convert each Criteria Table to a single Text Item using the Record.Field Power Query Function. 6. (11:38) Declare and Insert Variables into M Code for Report 7. (14:10) Test Reporting System 8. (14:30) Create Recorded Macro To Refresh All and assign it to a Form button 9. (16:40) Test Macro and Reporting System 10. (17:25) Summary
In this tutorial I want to show you some of the magic of Power Query…I’ll be importing from a website a list of Countries and their capitals, Clean and transform this list. Then send it back to Excel where I will be creating in one click a new sheet for each country (244 Sheets) and with a simple function extract the Capital of each Country at the top of each sheet. In this amazing tutorial I’ll be using: • Power Query • A Pivot Table hidden trick • And 2 simple Functions You can download the Start & Finish files by clicking on the link here below: 🤍
Download Excel Files: Start File: 🤍 Finish File: 🤍 Home page: 🤍 See how to create a unique sorted list with Power Query.
In this video, I'll show, how can you use the optional input parameter MisingField.Ignore to avoid breaking your queries in Power Query. = ONLINE COURSES = ✔️ Mastering DAX in Power BI - 🤍 ✔️ Power Query Course- 🤍 ✔️ Master Excel Step by Step- 🤍 ✔️ Business Intelligence Dashboards- 🤍 = LINKS 🔗 = Blog 📰 - 🤍 Corporate Training 👨🏫 - 🤍 Need my help on a Project 💻- 🤍 = CONTACT 🌐 = Twitter - 🤍 LinkedIn - 🤍 Email - goodly.wordpress🤍gmail.com = CHAPTERS = 0:00 Intro 0:36 MissingField.Ignore while Removing Columns 2:00 MissingField.Ignore while Renaming Columns 3:18 MissingField.Ignore while Reordering Columns 5:14 My Online Courses = WHO AM I? = A lot of people think that my name is Goodly, it's NOT ;) My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI. Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! - - - - - Music By: "After The Fall" Track Name: "Tears Of Gaia" Published by: Chill Out Records - Source: 🤍 Official After The Fall YouTube Channel Below 🤍 License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) Full license here: 🤍
Download Excel Files: 🤍 🤍 🤍 🤍 🤍 3 methods to append / Combine Tables in Excel Power Query: 1) Power Query Append Feature, 2) Excel.CurrentWorkbook function to combine all Excel Tables in current workbook, 3) Append csv files from a folder using From Folder feature. Topics: 1. (00:00) Introduction. 2. (00:52) Excel Tables to use Power Query. 3. (01:10) Method1: Power Query Append Feature 4. (03:56) Method2: Excel.CurrentWorkbook function to combine all Excel Tables in current workbook 5. (08:43) Method 3: Append csv files from a folder using From Folder feature. 6. (12:12) Summary, Closing and Video Links
Download Excel File #1: 🤍 Download Excel File #2: 🤍 Download Zipped Folder: 🤍 Entire page with all Excel Files for All Videos: 🤍 Buy excelisfun products: 🤍 Learn about how Topics: 1. (00:16) Introduction 2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016 3. (03:51) Example #1: Clean Data and create Excel PivotTable Report, then add new data and refresh report! 4. (17:06) Example #2: Transform Cross Tabulated Table into Proper Data Set 5. (21:50) Example #3: Import 1.2 Million Rows of Data from Text Files into Power Pivot and create a Data Model PivotTable, then add new data and refresh our report. 6. (22:34) Diagram to understand Data Model and Power Pivot 7. (46:46) Unhanded Exception Error. 8. (48:02) Summary Reference Video: Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model 🤍
Tip comes from: Bemint at YouTube 🤍
Download Excel File: 🤍 Download Text File: 🤍 Learn how convert a column of records into a table using Power Query with a User Interface Method and an M Code Method. Topics: 1. (00:00) Introduction. 2. (00:20) Coffee Introduction 3. (00:34) Teammates. 4. (00:54) Why use Power Query? 5. (01:03) User Interface Method. 6. (03:12) Edit a query. We need to fix data types. 7. (03:42) M Code Function Method using List.Split and Table.FromRows functions. 8. (06:24) Summary 9. (06:41) Closing and Video Links
COUNTIFS($A$1:A1,A1) Formula in Power Query? - Excel Magic Trick 1588 Download Excel File: 🤍 Learn how to create a Power Query Custom Column Formula that has an expandable range, counts based on the row condition and is equivalent to the Excel Worksheet Formula COUNTIFS($A$1:A1,A1). See how to use the Power Query Functions Table.AddColumn and Table.SelectRows. Also see how to create Two Custom Functions so we can access the Outside Table and Inside Table in the same formula.
Download Excel Start Files: 🤍 Entire page with all Excel Files for All Videos: 🤍 In this video learn how to use the Power Query Group By feature to simulate the COUNTIFS function. See how to count with one condition, See how to Count with Two Conditions
Download Excel File: 🤍 Learn how to Create a Running Count for Product Names using the Grouping and Add Index Feature. Learn how to edit your M Code. See the Power Query functions: Table.AddColumn, Table.Group, Table.AddIndexColumn and Table.Combine. This trick is a method that can calculate more quickly than the method seen in Excel Magic Trick 1588. This trick comes from our YouTube Teammates Vida and Bill Szysz.
Download Files: Start File: 🤍 Finish File: 🤍 🤍 See how to use Import 10 Text Files and Append (combine) then into a single Proper Data Set before making a PivotTable Report. Compare and Contrast whether we should use Connection Only or Data Model to store the data. 1. (00:18) Introduction & Look at Text Files that Contain 7 Million Transactional Records 2. (01:43) Power Query (Get & Transform) Import From Folder to append (combine) 10 Text Files that contain 7 Millions transactional records. 3. (05:07) Load Data as Connection Only and Make PivotTable 4. (08:17) Load Data into Data Model and Make PivotTable. 5. (10:46) Summary
Download Excel Files: 🤍 Download Solution: 🤍 Learn how to calculate Moving Annual Total using Power Query. See multiple user Interface Steps and a Custom M Code Function. Learn how to calculate Moving Annual Total in Excel worksheet with Power Query using User Interafce and M Code and a Custom Functions. Advanced Power Query Video. Topics: 1. (00:00) Introduction to task at hand. 2. (00:49) excelisfun intro videos. 3. (01:09) Overview of steps in Query and Advanced M Code. 4. (01:53) (1) Steps to create Crossjoin Between Product and End Of Month. 5. (07:11) (2) Steps to Calculate Month Sales. 6. (07:49) (3) Steps to Merge or Left Outer Join Cross Join Table and Monthly Sales table. 7. (09:44) (4) Steps to create Custom Function. 8. (15:03) Load Table to worksheet. 9. (15:13) Summary, Closing and Video Links Related Videos: Moving Annual Total with SUMIFS Function. Excel Magic Trick 1724 Moving Annual Total with Power Query M Code Custom Function. Excel Magic Trick 1725 Moving Annual Total with DAX Measure. Excel Magic Trick 1726.
Download Excel File: 🤍 In this video learn how to take an invoice table, list the unique combinations of Invoice & Vendor and then list the items (references) horizontally. Learn how to use Power Query to create a solution. Learn how to edit the M Code in the Power Query Solution. Learn about the M Code Power Query Functions: List.Distinct, List.Transfrom, Table.Group and Text.Combine. Related Video: Unique Invoice / Vendor List with Items Listed Horizontally - Excel Dynamic Array Formula- EMT 1575
Download Excel Start File: 🤍 Download Excel Finished File: 🤍 Entire page with all Excel Files for All Videos: 🤍 In this video learn how to create a Running Total (Cumulative Totals) in a Column using Excel Worksheet Formulas, Power Query M Code or a DAX Calculated Column in Power BI or Excel Power Pivot Data Model. Learn how to create Overall Running Total and Running Totals by Month. Learn the important skill of creating Expandable Ranges in a Worksheet Formula, DAX Formulas and Power Query Formulas. Topics: 1. (00:07) Introduction 2. (01:20) Excel Worksheet Formula solution using SUMIFS and EOMONTH functions with an Expandable Range. Two formulas: Overall Running Total and Running Totals by Month. 3. (04:32) Look at expandable range in a Worksheet Formula 4. (05:18) Import Table for both Power Query Solution and DAX Solution using Power Query and the Reference feature. 5. (06:50) Power Query Custom Column with Table.AddColumn Function, Table.SelectRows Function and Two M Code Custom Functions to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column. 6. (08:15) Lesson about how Custom Columns and M Code Custom Functions work, including the how the keyword “each” is a substitute for an explicitly defined Custom Function. 7. (13:33) Look at expandable range in a Power Query Formula. 8. (14:25) Field Access Operator for Looking up: 1) A Full Column or 2) A Single Item from Each Row. 9. (20:01) DAX Calculated Column with the functions CALCULATE, SUM, FILTER, EARLIER, EOMONTH and a Var Variable to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column. 10. (26:46) How to use a Variable in DAX. See Var = Mon Return syntax for a Variable in DAX. 11. (30:26) Summary
Download Files: Start File: 🤍 Finish File: 🤍 If you try to make X-Y Scatter Chart from PivotTable is will NOT work!!!!! See how to use Power Query to create the same output as a PivotTable in order to make an X-Y Scatter Chart: 1. (00:15) Introduction 2. (00:47) PivotTable to Adding Units by Day. But when we try to create an X Y Scatter Chart from PivotTable, we will get the error “You can’t create this chart type with data inside a PivotTable. Please select a different chart type, or copy the data outside the PivotTable”. 3. (02:35) Use Power Query to build PivotTable output, and then the X Y Scatter Chart will work perfectly!! 4. (05:30) Build X-Y Scatter Chart with Proper Labels. 5. (07:14) Summary
Download all files to follow along with video and do homework is zipped folder here: 🤍 To download individual files, visit class web site here: 🤍 This video teaches about the basics of how to use Power Query to import, clean, transform and load data. This class : Data Analysis & Business Intelligence Made Easy with Excel Power Tools - Excel Data Analysis Basics = E-DAB Class – Sponsored by YouTube and taught by Mike Girvin, Highline College Instructor, Microsoft Excel MVP and founder of the excelisfun channel at YouTube. This is a free educational resource for people how want to learn about the Basics of Data Analysis and Business Intelligence using Microsoft Power Tools such as, PivotTables, Power Query, Power Pivot, Power BI Desktop and more. Topics: 1. (00:12) Introduction 2. (01:14) Power Query Icon as Magic 3. (02:19) Files to download and unzip folder 4. (03:12) What is Power Query? What does Power Query do? 5. (05:19) Example 1: Import Text File to create report and chart. Then get a new Text File next month and everything will update. 6. (07:11) A complete introduction to Power Query. 7. (12:59) Two methods to update report when you get a new Text file: 1) Edit Query, or 2) Duplicate Query. 8. (15:02) Example 2: Clean Bad Data in an Excel Sheet, Load to Excel Sheet, Build PivotTable. Then add new data and refresh the Query and the PivotTable Cache. 9. (20:42) Example 3: Import multiple Text Files from Folder and Append into single Proper Data Set. 10. (20:30) Summary The Power Query logo used in this video is copyright of and used with the express permission of 🤍ng Thanks to Ken Puls and Miguel Escobar for letting me use their logo!!!!
Download File: Start File: 🤍 Finished File: 🤍 Entire page with all Excel Files for All Videos: 🤍 See how to connect to an SQL Database, Append an Excel Table to the SQL Imported Data, Group By to add Total Units for each Product, then Sort, Grouping, Indexes and Custom Columns to Rank Total Product Units: 1. (00:14) Introduction of whole project 2. (01:29) Connect to an SQL Database that contains over 7 million records 3. (02:47) Import Excel Table 4. (03:25) Append an Excel Table to the SQL Imported Data 5. (04:06) Group By feature to add Total Units for each Product 6. (05:01) Sort, Grouping, Indexes and Custom Columns to Rank Total Product Units 7. (11:54) Change Data and Refresh 8. (12:30) Summary Related Videos: Excel Magic Trick 1417: Excel Functions Ranking For Unit & Total Profit for Products within Manufacturer Excel Magic Trick 1418: PivotTables Ranking For Unit & Total Profit for Products within Manufacturer Excel Magic Trick 1419: RANKX DAX Function & More: Ranking Profit for Products within Manufacturer Excel Magic Trick 1420: Ranking in Power Query: Sort, Grouping, Indexes and Custom Columns
In this short clip, I will show you how to use Power Query to check records in a data set for inconsistencies. This process is highly flexible and repeatable and allows for reconciliations that Excel cannot perform. Interested in more? Visit 🤍datasavvier.com
Download Files: Start File: 🤍 Finish File: 🤍 See how to create a Power Query (Get & Transform) Custom Function with a parameter in order to create five Sub Tables, one for each product, each on a new sheet, from a Master Table: 1. (00:17) Introduction. See finished Power Query Function to Extract Records to A Sheet Based on Criteria / Condition / Parameter. 2. (02:23) Import Excel Master Table into Query Editor. 3. (03:19) Filter Records and Add Custom Column to Calculate Revenue. See the Function Number.Round. 4. (07:10) Look at M Code and learn about some of the conventions for M Code. 5. (09:57) Load Query to Sheet to Test where the M Code works. 6. (10:29) Build Power Query Custom Function. Create Parameter / Input Variable / Changeable Criteria in M Code. 7. (12:58) Invoke Function to Create Sub Tables 8. (14:32) Test whether the sub tables 9. (15:57) Learn how to edit the Parameterized Custom Function so that all five invoked functions and the five Sub Tables update perfectly! Learn how to use the Table.RemoveColumns function. 10. (20:36) Summary Power Query (Get & Transform) Custom Function Power Query (Get & Transform) Code with an Input Variable Power Query (Get & Transform) with Input Criteria Power Query (Get & Transform) Parameterized Custom Function Build Re-Usable M Code in Power Query (Get & Transform) Create Power Query (Get & Transform) Function
In this video I show you how to do dynamically remove empty columns in Power BI. The first option I show is easy to implement and requires no manual formula writing, but is not ideal for larger data sets. The second option requires a bit of M magic, but is better when you have a larger data set. Enjoy the video! Download link: 🤍 📊 TRAININGS 📊 - Learning Path 🤍 For Custom Trainings and Consulting email directly support🤍datatraining.io - ⏱️ TIMESTAMPS ⏱️ - 0:00 Intro 0:57 Removing empty columns - option 1 2:21 Removing empty columns - option 2 08:45 End - 😍 JOIN 😍 Subscribe: 🤍 Website: 🤍 Facebook: 🤍 LinkedIn: 🤍 Insta: 🤍 Twitter: 🤍 - 👇 CHECK THIS OUT! 👇 - 💻 My gear 🤍 📚 Power BI books MUST READ! 🤍 💡 General books I recommend 🤍 🎶 Music for my videos 🤍 🚀 For growing on YouTube: 🤍 🏄 Stuff I use daily 🤍 * Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos! Thanks for being a part of this channel and all your support! 💪 🙏 #HowToPowerBI #PowerBI #DataTraining #powerbidesktop #powerbitraining #powerbideveloper
Download Excel File: 🤍 Learn how to filter a table based on NOT Contains Criteria. See a single formula solution using the functions Splitter.SplitByAnyDelimiter, List.Count and Table.SelectRows. Amazing formula solution from Power Query Poet, Bill Szysz.
This video helps to learn how to merge/combine multiple excel sheets or excel workbooks to one single excel sheet/file. #Combine Excel worksheets #Combine Excel workbooks #Merge Excel Worksheets #powerquery #merge sheets #power query magic trick #how to merge two files #how to combine multiple files #how to merge two files #how to merge multiple files #power query #power query in excel #excel #excel tutorial #combine data from multiple worksheets into a single worksheet in excel #how to combine multiple excel workbooks into one workbook #how to merge data in excel
Download Files: Start File: 🤍 Finish File: 🤍 Using Power Query (Get & Transform), see how to Import & Merge Text & Excel Files, Clean Data, Add Helper Columns for Company Name and Revenue, and add a Total Row & Slicer for Power Query Report: 1. (00:16) Introduction and View of Final Report 2. (02:13) Import Text Files with from Folder option 3. (05:40) Clean Data with Split Feature 4. (06:20) Create Custom Columns (Helper Columns) for Company Name (if then else Function) 5. (08:50) Import Excel Table 6. (10:00) Merge Units Table and Price Table using Left Outer Join (like VLOOKUP in Excel or Relationships in Power Pivot) 7. (12:17) Create Custom Columns (Helper Columns) for Revenue (Number.Round function) 8. (13:55) Edit Query when a Field name is misspelled 9. (15:17) Group By Product & Company Name to get Total Revenue 10. (16:52) Load to Excel Sheet 11. (17:55) Add Totals Row 12. (18:34) Add Slicer 13. (19:26) Why we use Power Query 14. (19:48) Add new Text Files to Folder and Update (Magic of Power Query) 15. (20:53) Summary
Download Files: Start File: 🤍 Finish File: 🤍 See how to : Import Tables from SQL Server Database into Power Pivot or Power BI Data Model using Power Query.
Liven up your Power BI reports with spatial analytics. Which visuals fit best for different map types with a deep dive of more advanced mapping themes. Speaker: James Dales 🤍 Speaker BIO: James is a Microsoft Data Platform MVP and the Azure Data Platform and Visualisation Practice Director at Altius Consulting. Author of the Icon Map Power BI custom visual. 🤍
In this Excel meetup, we heard from Gašper Kamenšek and the 'Magic of Lists in Power Query'. You can download the files used to follow along here - 🤍 Gašper Kamenšek is a BI Professional and an MVP for Excel from Slovenia. He provides training and speaks at conferences all over the world. Gašper is also the organiser of the Excel Olympics Excel conference in Slovenia. I am delighted to welcome Gašper to the London Excel Meetup where we can all learn some awesome tips. Find out about all future London Excel Meetups events and book onto the Live Zoom call here - 🤍 Find more great free tutorials at; 🤍 * Online Excel Courses * The Ultimate Excel Course – Learn Everything ► 🤍 Excel VBA for Beginners ► 🤍 Advanced Excel Tricks ► 🤍 Excel Formulas Made Easy ► 🤍 Creating Sports League Tables and Tournaments in Excel ► 🤍 Connect with us! LinkedIn ► 🤍 Instagram ► 🤍 Twitter ► 🤍
Download Excel File: 🤍 Learn how to filter imported data using Distinct NOT Conditions from List. See a single M Code Power Query Formula using the functions. Table.SelectRows, Text.Split, List.Intersect and List.IsEmpty. This amazing solution comes from Bill Szysz.
Download Excel Start File: 🤍 Download Excel Finished File: 🤍 Learn how to append two columns (append two columns) in Excel using a Worksheet Formula and Power Query. See the functions, IF, ROWS and INDEX. Learn about Number Incrementor Formulas that contain Expandable Ranges. Learn about Power Query and the Append Feature. 1. (00:01) Introduction. 2. (00:18) Worksheet Formula Solution. 3. (04:28) Power Query Solution. 4. (05:43) Bonus Worksheet Formula. 5. (06:13) Summary and End Video Links.
Download Excel Start File: 🤍 Download File: 🤍 From An Excel Table that stores Invoice data with line item data for invoices (duplicate invoice numbers in the Invoice Field (Column), learn how to use Power Query to create a Unique List of Invoice Numbers with Balances. See that the output from Power Query is dynamic and so updating the list when new invoices is dynamic. Also see these videos: Excel Magic Trick 1249: Build Database with Excel 2016 Table feature & VLOOKUP to Get Invoice Detail Excel Magic Trick 1251: SUMIFS & Table Formula Nomenclature (Structured References) to Add Invoices
Download Excel Start Files: 🤍 Download Excel Finished Files: 🤍 In this video compare five methods to count how many products are in a column. Topic: (00:05) Introduction 1. (01:25) Excel Spreadsheet Function: COUNTIFS 2. (03:23) Excel Standard PivotTable 3. (04:47) Power Query: Group By feature 4. (07:23) DAX Functional Language of Excel Power Pivot & Power BI DAX: COUNTROWS 5. (11:08) Office 365 Dynamic Array Functions: UNIQUE & COUNTIFS (14:40) Summary Entire page with all Excel Files for All Videos: 🤍 Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Speaker: Cristian Prifti Session Date: Thu, Feb 24th 2022 Language: English = ✅ Session files: 🤍 = Description: The session is meant to introduce people to what a custom function is, how they can save us time during the development and how we can actually create functions by ourselves. During the session we will also go through live examples of custom functions that I personally used and found helpful. As Power Query is part of Excel too the session is applicable for both Power BI and Excel! = Speakers: Cristian Prifti I am for sure a lazy data fanatic, who started spending his days within Excel, then started learning VBA, but afterwards discovered Power Query and Power BI and became a huge supporter! For the past 3 years this has been my job, leading a team of 4 people and helping various clients in their Power BI journey. 🤍
Download Excel Files: 🤍 Download File: 🤍 Learn how to Score Myers-Briggs Assessment (Test) in Excel with Two Formulas based on a proper lookup table created with Power Query (Excel 2016 Get and Transform): 1. (00:12) Problem Introduction 2. (00:50) Use Power Query (Get and Transform in Excel 2016) to unpivot a rectangular lookup table into a proper lookup table with a unique identifier (unique list) in the first column. We will see how to take a Myers-Briggs Rectangular Score Table and unpivot it to create a proper lookup table for VLOOKUP. 3. (04:30) VLOOKUP lookup formula 4. (06:20) COUNTIFS formula to get final score.