- 43
- 31 867
Power-M-Query
Australia
Приєднався 18 сер 2024
Welcome to Power-M-Query- channel for mastering data transformation and automation in Excel and Power BI! Whether you're a beginner just getting started with Power Query or an experienced user looking to refine your skills, this channel has something for everyone.
Here, you’ll find step-by-step tutorials, tips and tricks, and in-depth guides on using Power Query’s powerful M language to clean, reshape, and analyze your data like a pro. From practical use cases to advanced techniques, we cover it all to help you unlock the full potential of your data.
Subscribe now to stay updated with the latest videos and take your data skills to the next level!
Here, you’ll find step-by-step tutorials, tips and tricks, and in-depth guides on using Power Query’s powerful M language to clean, reshape, and analyze your data like a pro. From practical use cases to advanced techniques, we cover it all to help you unlock the full potential of your data.
Subscribe now to stay updated with the latest videos and take your data skills to the next level!
Mixing Power Query Results and Manually created Excel Column
Discover how to merge an Excel column with a Power Query result table by incorporating a comment column directly into the query output. This tutorial walks you through the process of integrating tasks from another source into a unified and dynamic table. Ideal for enhancing collaboration and improving data management workflows!
Переглядів: 94
Відео
Building a Power Query Load Tracker and preserves historical data, Super Practical tip
Переглядів 5297 годин тому
Learn how to create a Power Query Load Tracker that preserves historical data and appends new data during each refresh. This step-by-step tutorial demonstrates how to efficiently track Bitcoin prices, ensuring that past records remain intact while seamlessly integrating the latest data. Perfect for those looking to build robust data tracking solutions with Power Query!
Fill Down and Up by Group in Power Query - Challenge 141
Переглядів 24514 годин тому
In this video, we solve Challenge 141: Filling blank cells within groups in Power Query! Learn how to replace null values by filling down from the nearest row with the same ID or filling up when no upper value exists. This challenge highlights advanced techniques for handling grouped data in Power Query!
Advanced Power Query Tip: Split Column Based on Repeating Text - Challenge 136
Переглядів 38021 годину тому
In this video, we solve Challenge 136: Splitting a column where a character is repeated consecutively in Power Query. Learn how to break down values like "HNNM" into separate parts where repeating characters occur. This advanced tip is perfect for handling complex text splits and improving your data transformation skills in Power Query!
Hidden Feature of Custom Sorting in Power Query with Table.Sort, Sort Based on Multiple Columns
Переглядів 309День тому
In this video, we solve Challenge 112: Ranking products using custom sorting logic in Power Query's Table.Sort function. Learn how to rank products based on the difference in sales between 2023 and 2022 in descending order. Perfect for mastering custom ranking and sorting techniques in Power Query!
Advance Custom Indexing in Power Query - Challenge 117 & 127
Переглядів 50614 днів тому
In this video, we tackle Challenge 117: Adding custom index columns in Power Query! Learn how to create separate counters for each stock in your dataset. This tutorial is perfect for mastering grouped indexing and enhancing data organization in Power Query.
Power Query Challenge 44: Combine Tables with Different Column Titles
Переглядів 76221 день тому
In this video, we tackle Challenge 44: Combining tables with different column titles using Power Query. Learn how to consolidate product sales data from multiple tables, each representing a different month of spring, and calculate the total sales per product in each region for the entire season. This practical example will help you master data transformation and aggregation in Power Query! 00:0...
Advanced Power Query Problem, Extract Transactions in Last 7 Days of Month - Challenge 134
Переглядів 58721 день тому
In this video, we solve Challenge 134: Filtering transactions that occurred in the final 7 days of each month using Power Query. Learn how to apply advanced date logic to isolate and analyze end-of-month data. Perfect for enhancing your Power Query filtering skills!
Extract the Last Value for Months, Using Advanced Features of Adding Custom Column in Power Query
Переглядів 60321 день тому
In this video, we explore Challenge 95: Extracting the last recorded inventory level for each product using advanced features in Power Query. Discover how to add new columns effectively and extract the final inventory value from monthly data. Perfect for mastering advanced column transformations in Power Query! Two different solutions are proposed for this challenge.
Using Advanced Feature of Table.Pivot with Custom Aggregation to solve Power Query Challenge 91
Переглядів 615Місяць тому
In this video, we solve Challenge 91: Transforming data with Table.Pivot and custom aggregation in Power Query and also used check mark in Power Query. Watch as we convert a question table containing manager names across departments and branches into a structured result table. This tutorial is perfect for learning advanced data transformation techniques in Power Query!
Table.Buffer for Consistent Results in Power Query and Fixing its Wired Behavior for sorting
Переглядів 893Місяць тому
In this video, we discuss a Wired behavior of Power Query related to sorting rows and removing duplicates. Learn why it's crucial to use Table.Buffer after sorting to ensure consistent results. We also demonstrate how this technique can be used to extract the last value over different months. A must-watch for anyone looking to avoid data inconsistencies in Power Query!
How to Handle Merged and Multi-Row Headers in Power Query
Переглядів 948Місяць тому
Working with merged or multi-row headers can complicate your data transformation process in Power Query. In this video, we’ll guide you through handling both types of headers, helping you clean up and organize your data efficiently for analysis. What you'll learn: The common challenges of merged and multi-row headers in Excel How to unmerge and transform headers for cleaner data Techniques for ...
Power Query Challenge 16: Transform Long Table into Wide Table Structure - Advanced Use of Pivoting
Переглядів 791Місяць тому
In this video, we take on Power Query Challenge 16, where we transform a long table into a wide table structure using advanced pivoting techniques. This challenge is essential for anyone looking to optimize data presentation and analysis in Excel. What you'll learn: Overview of Challenge 16 and its objectives How to identify the right columns for pivoting Step-by-step guide to using the pivotin...
Power Query Challenge 39: Extract All Unique Product IDs Using Table.ToRows Function
Переглядів 610Місяць тому
In this video, we take on Power Query Challenge 39, where we extract all unique product IDs from a dataset using the Table.ToRows function. This challenge is essential for anyone looking to streamline their data analysis and ensure accurate product tracking in Excel and Power Query. What you'll learn: Overview of Challenge 39 and its objectives How to utilize the Table.ToRows function effective...
Making Column Splitting Dynamic in Power Query
Переглядів 580Місяць тому
In this video, we dive into the process of making column splitting dynamic in Power Query, enabling you to adjust how you split your data effortlessly based on changing on the source data. This approach allows for greater flexibility and efficiency in data transformation. What you'll learn: Introduction to dynamic column splitting and its advantages Setting up your data for dynamic column split...
Power Query Split Column: By Delimiter & Non-Digit to Digit
Переглядів 310Місяць тому
Power Query Split Column: By Delimiter & Non-Digit to Digit
Power Query Challenge 14: Identify All-Season Products - Solving with List.Difference
Переглядів 527Місяць тому
Power Query Challenge 14: Identify All-Season Products - Solving with List.Difference
Load All Data from a PDF into Power Query at Once
Переглядів 452Місяць тому
Load All Data from a PDF into Power Query at Once
Power Query Challenge 20: Transform Hierarchy Table Structure into Standard Structure
Переглядів 764Місяць тому
Power Query Challenge 20: Transform Hierarchy Table Structure into Standard Structure
Loads all the tables in Power Query by Excel.CurrentWorkbook function
Переглядів 580Місяць тому
Loads all the tables in Power Query by Excel.CurrentWorkbook function
Power Query Challenge 3: Extracting All Combinations in Power Query - Solving with List.Accumulate
Переглядів 5292 місяці тому
Power Query Challenge 3: Extracting All Combinations in Power Query - Solving with List.Accumulate
Split a Column in Power Query Using Multiple Delimiters
Переглядів 7392 місяці тому
Split a Column in Power Query Using Multiple Delimiters
Creating Cartesian Products in Power Query by List.Generate, List.TransformMany, and List.Transform
Переглядів 5722 місяці тому
Creating Cartesian Products in Power Query by List.Generate, List.TransformMany, and List.Transform
Classify Customers Using the K-Means Algorithm in Power Query with R
Переглядів 3852 місяці тому
Classify Customers Using the K-Means Algorithm in Power Query with R
Power Query Tutorial for Beginners: Master Essential Tools in Home tab!
Переглядів 4002 місяці тому
Power Query Tutorial for Beginners: Master Essential Tools in Home tab!
Convert Numbers to Words and Words to Numbers in Power Query Using Python
Переглядів 3842 місяці тому
Convert Numbers to Words and Words to Numbers in Power Query Using Python
Mastering List.Transform in Power Query: Transform Lists Like a Pro!
Переглядів 1,4 тис.2 місяці тому
Mastering List.Transform in Power Query: Transform Lists Like a Pro!
Power Query Column Merging Tips: Merging Columns vs. Adding Columns
Переглядів 3652 місяці тому
Power Query Column Merging Tips: Merging Columns vs. Adding Columns
Power Query Challenge 11: Identifying Frequent Codes - Solving with List.Transform
Переглядів 6952 місяці тому
Power Query Challenge 11: Identifying Frequent Codes - Solving with List.Transform
Effortless Running Total Calculation in Power Query: Step-by-Step Guide
Переглядів 4782 місяці тому
Effortless Running Total Calculation in Power Query: Step-by-Step Guide
I always used to create helper tables for comments-but not anymore! Thank you so much. You're a Power Query genius!
Excellent as always👍. Thanks and look forward to more videos from you❤
Superb content! Thank you!
Another amazing video! Thank you!
Well done, Omid. Always a pleasure to see you solve these cases. Another application of Merge Queries. Thanks for sharing.
Awesome video! Thank you!
It’s so amazing. Thank you for another valuable lesson!
Awesome! Thank you!
@@mnowako you are welcome
“Hello, this question came to my mind: What if the latest price remains as a link instead of being on the page, and we write the merged data to the sheet?”
Hi, yes you can do the same
@@PowerMquery thanks Omid
@@burhancesur Welcome
Great solution, thank you! ;)
Thanks for the comment
Brilliant as always.Thanks Omid and merry Christmas ✨
Thank you
Happy Christmas 🌲 🎉❤
Merry Christmas!!!
Excellent tip. Thank you, Omid. Merry Christmas!!!
Thanks and Merry Christmas!!!
Great!!! 💯👍
Amazing content! Please keep these videos/lessons coming-they're incredibly valuable for the PQ community. Thank you so much for sharing!
You are welcome, and thanks for your support.
Great solution!! Alternative: =LET( a, C3:C13, b, D3:D13, r, ROWS(a), s, SEQUENCE(r), SCAN( 1, s, LAMBDA(v, i, LET( x, INDEX(a, i), y, INDEX(b, i), f, FILTER(s, (a = x) * b), IF(y, y, IFERROR(INDEX(b, XLOOKUP(i, f, f, TAKE(f, 1), -1)), "")) ) ) ) )
Great combination of Table.FillUp and Table.FillDown. Thanks for sharing your solution, Omid.
Awesome
Great video!! Alternative: This can be very easy transformed in a TEXTSPLIT problem that can be solved in 100 ways =REDUCE("",SEQUENCE(MAX(LEN(B3:B8))),LAMBDA(v,i,LET(x,RIGHT(v,1),y,MID(B3:B8,i,1),IF(x=y,v&"·"&y,v&y)))) One way is (if "a" is the previous result array) =TEXTSPLIT(TEXTJOIN("|",,a),"·","|",,,"")
You are welcome, and thanks for your support.
Excellent solution. Thank you, Omid!!!
You are welcome, and thanks for your support.
Super 👍👍
You are welcome, and thanks for your support.
Awesome! Thank you!
You are welcome
Amazing lesson! Thank you!
Welcome
Amazing! Thank you!
Excellent logic. I love the multiple resolutions that Power Query supports. Thanks for explaining it so well, Omid.
You are welcome
Super necessary for power query family.... Thanks 😊
Welcome
I had a doubt , might be a very basic one but......when you are defining the function, how to interpret that in x[Index] <= _[Index] "x" is referring to all rows of the table and "_" is accessing the current row of the table
Loved It! Thank You!
Welcome
I love how we can create sub-list with functions in there and plug it in Table.TransformColumns instead of hard coding them. Power Query is amazing
Omid, thank you for organising these challenges and this wonderful Channel. In the very end of the video I noticed that row 7 in PQ has a value of 3 while I expected 2?
While I appreciate the creativity of using functions in this manner, the hardest part for me, is relating it back to a real world example rather than just a theoretical problem - your skills in creating functions is unsurpassed to any other channel that I have come across sharing their solutions
@@BobbySox-i9q I am happy you like it
👍❤🌹 Thanks Omid
@@kebincui welcome
Master-solution, Omid. Thank you for sharing it!
You are welcome
Great Video!! Great PQ solution !! Formula alternative: =LET(a,B3:B13,b,C3:C13, REDUCE(,SEQUENCE(ROWS(a)), LAMBDA(v,i, LET(x,XMATCH(INDEX(a,i),TAKE(a,i-1),,-1), VSTACK(v,IFNA(IF(INDEX(b,i)<INDEX(b,x),1,INDEX(v,x)+1),1))))))
Thanks for sharing your solution
This is a tough one, Thanks for sharing
You are welcome
I love Power Query
This is an amazing tool
Very compact solution, Omid. Thanks for sharing.
You are welcome
Great video!! Great PQ solution !! As alternative, this is how simple it is if we have in our portfolio unpivot lambdas: =LET(a,VSTACK(UP(C3:F6),UP(C9:G12),UP(C15:F18)),PIVOTBY(INDEX(a,,1),INDEX(a,,2),INDEX(a,,3),SUM,,0,,0)) where UP (UnPivot) : =LAMBDA(a,LET(w,ROWS(a)-1,l,COLUMNS(a)-1,s,SEQUENCE(w*l),HSTACK(INDEX(DROP(TAKE(a,,1),1),QUOTIENT(s-1,l)+1),INDEX(DROP(TAKE(a,1),,1),MOD(s-1,l)+1),TOCOL(DROP(a,1,1)))))
Thanks for sharing your solution
Very useful thanks for sharing this valueable tips
You are welcome
Nice video,Does Value.Metadata referencing to intermediate steps reduce query loading time?
Not actually, Power Query does not run the step in the defined order and pick the optimum order and neglect those are not requieres
First comment 😂🎉🎉🎉
Thanks
Hi Omid. I have a query which combines 6 different tables from web. 3 of them change once in month and 3 change everyday. Is there a way where I can make PQ skip loading static tables again and again?
How come at the 1:03 mark, the end of month function returns the first day of the month?
Thanks for mentioning, I didnt check that, it is because the Date column is in type DateTime format, it just need to convert it into date first then apply Date.EndofMonth
Good one as always, it would be interested to put the files
You are welcome
Good one! Thank you!
You are welcome
Excellent Omid. Both methods are very brilliants. Thank you!!!
You are welcome!
Is it possible to create phonetic matches in power query with contain condition... Can you please guide.
What do you means phonetic match? Can you give me an example?
@PowerMquery Sir, apologize for late response....Search by Using phonics...like in Arabic... Ali can be Aly... Qasim can be write as Kasim...Muhammad can be written as Mohammad or Mohammed.... So while merge the query I only get the result of extract match where Ali=Ali..result despite Aly is in the list result show only single match
Wonderful simple and clear explanation in words
@@thebesttechnical3608 Thank you.
Isn't it typical COALESCE case? Something like one liner: [Last Value] = [May] ?? [Apr] ??[ Mar] etc...
It solve the problem in this case but it is a kind of hard coded and not efficient if you faced with forexample 12 columns and also consider the columns of new monthes added later, it needs modification every month or using all the month names once might lead to error.
@@PowerMquery Agree. :)
Excellent tip :)
You are welcome