- 175
- 355 417
BA Sensei
South Africa
Приєднався 29 лип 2019
Welcome to my UA-cam channel dedicated to helping Business Analysts and Data Analysts upskill and thrive in their careers. My videos provide comprehensive and easy-to-follow tutorials on a range of data tools and techniques that are essential for success in these roles.
If you're looking to learn how to use specific data tools such as Power Query, Power BI, DAX, Python libraries, and ETL tools, you've come to the right place. Our step-by-step tutorials will guide you through each tool's functionalities and show you how to apply them to real-world data challenges.
Our goal is to empower Business Analysts and Data Analysts to excel in their work and stay ahead of the curve in the rapidly evolving data landscape. So, whether you're a seasoned pro or just starting out in this field, our channel has something for everyone.
Don't forget to subscribe to our channel and hit the notification bell to stay up-to-date with our latest videos. Thanks for watching
If you're looking to learn how to use specific data tools such as Power Query, Power BI, DAX, Python libraries, and ETL tools, you've come to the right place. Our step-by-step tutorials will guide you through each tool's functionalities and show you how to apply them to real-world data challenges.
Our goal is to empower Business Analysts and Data Analysts to excel in their work and stay ahead of the curve in the rapidly evolving data landscape. So, whether you're a seasoned pro or just starting out in this field, our channel has something for everyone.
Don't forget to subscribe to our channel and hit the notification bell to stay up-to-date with our latest videos. Thanks for watching
Power Query - ID all stocks for season
in datasets where you have to idenitfy items that appear consistently across all months (seasons) in a year this power query pattern illustartes how you can get the answers with the help of group and list functions.
#data #dataanlytics #dataanalysis #powerbi #powerqueryeditor #mquery #excel #powerbidesktop
File Link
github.com/jbotes/powerbiTutorials/blob/main/PQ_AllSeasons_Video_2.xlsx
Functions used
- Table.Group to group the data
- List.Transform to convert a list of dates to months
- List.Distinct to remove duplicate months in list
- Date.Month to extract the month only from a date
- List.Count to count the number of months for each stock
- Table.SelectRows to filter the number of months for all seasons
Noteworthy things:
Used a paramater table to feed into the main query
#data #dataanlytics #dataanalysis #powerbi #powerqueryeditor #mquery #excel #powerbidesktop
File Link
github.com/jbotes/powerbiTutorials/blob/main/PQ_AllSeasons_Video_2.xlsx
Functions used
- Table.Group to group the data
- List.Transform to convert a list of dates to months
- List.Distinct to remove duplicate months in list
- Date.Month to extract the month only from a date
- List.Count to count the number of months for each stock
- Table.SelectRows to filter the number of months for all seasons
Noteworthy things:
Used a paramater table to feed into the main query
Переглядів: 704
Відео
Power Query - Looping through Columns
Переглядів 3 тис.28 днів тому
Looping in M-query is not as straight forward a with other scripting languages like python. In this tutorial I show you how to iterate and transform columns in a table without repeating the transformation manually. #data #dataanalysis #excel #powerquery #mquery #powerbi #tutorial #etl File Link github.com/jbotes/powerbiTutorials/blob/main/PQ_Listaccumulate_video.xlsx Function used 1 - Table.Tra...
Power Query - Identifying Frequent Values
Переглядів 1,2 тис.Місяць тому
in this video i show you some tricks using list functions to identify Frequently or non Frequently used values in a dataset. i'll be using Stock Codes in various portfolio - so you can see how many times some stocks appear in a list of portfolios. #data #dataanalysis #powerbi #powerquery #mquery #excel Following Along file github.com/jbotes/powerbiTutorials/blob/main/PQ_VALUE_ALL_COLUMNS_VIDEO....
Power Query - Create a Dynamic Grouping Index 🦒🩺
Переглядів 2,1 тис.Місяць тому
In this rather esoteric situation i show you how to use the 5th argument (comparer) of Table.Group to dynamically add a Grouping index column based on a sum of rows. #excel #dataquerying #powerquery #mquery #powerbi #tutorial #dataanalysis Follow along file: github.com/jbotes/powerbiTutorials/blob/main/PQ_Grouping_Video_2.xlsx Functions Used List.Range List.Sum Number.From Table.AddIndexColumn ...
Power Query - Dynamic Top N or Bottom N 🥶🦒
Переглядів 1,4 тис.2 місяці тому
In This tutorial i show you how to dynamically return either the Top N or Bottom N values in a group by using a parameter table. #dataquerying #powerbi #powerquery #mquery #excel #dataanlysis #data Project File github.com/jbotes/powerbiTutorials/blob/main/PQ_Dynamic_T_B_Video.xlsx Functions Used List.MaxN List.MinN Table.Transpose Table.Sort Table.AddColumn
Power Query - Unstack Double Barrel Header Dataset 🍔
Переглядів 1,1 тис.2 місяці тому
In this video i show you an alternative approach to deal with Double barrel header datasets using various list / record and table functions. #data #dataquerying #powerquery #excel #dataanalysis #powerbi Example file github.com/jbotes/powerbiTutorials/blob/main/PQ_DOUBLE_BARREL_2_Video.xlsx Functions used Table.Skip List.RemoveNulls List.Distinct Record.ToList Table.ToColumns List.Transform Tabl...
Power Query - Unpivot Double Barrel Headers
Переглядів 1,6 тис.3 місяці тому
In This Video i show you how to deal with the Double Barrel Header problem you'll encounter in your day to day life. You'll get to use some m query functions to unpivot the dreaded double barrel header and clean your data easily. #data #dataanalysis #excel #powerquery #powerbi Sample File github.com/jbotes/powerbiTutorials/blob/main/PQ_DoubleBarrel_Video.xlsx M Query Functions Used • Table.Tran...
Power Query - Filter Multiple Columns at once🦖😶🌫️
Переглядів 2 тис.3 місяці тому
In this video i show you how to use some list functions to quickly filter multiple columns in one go. #data #dataanalytics #powerbi #powerquery #excel #mquery 🦖😶🌫️ Follow along file: github.com/jbotes/powerbiTutorials/blob/main/PQ_Filter_Columns_Once_Video.xlsx Functions Used To add columns to a Table Table.AddColumn To check for specific values in lists List.ContainsAny To check for true or f...
Power Query - Adding a Conditional Index Column
Переглядів 2,9 тис.4 місяці тому
Using the power of records in power query i show you how to create a Conditional index on a simple portfolio dataset. #dataanalysis #data #powerbi #powerquery #excel #exceltips Link to Source File: github.com/jbotes/powerbiTutorials/blob/main/PQ_Counter_Column_Video.xlsx Functions used: List.Repeat Record [] List.Transform Table.Group Table.RowCount List.PositionOf Table.FromColumns
Power Query - Combine Files with inconsistent column names! 😎🍔
Переглядів 3,2 тис.5 місяців тому
In this video i show you how to cater for this common use case where you want to combine various files into one but all of the files have different column names. This video you'll learn how to define the destination columns and remove the ones you don't need.😎🍔 #data #dataconsistency #dataanalysis #dataanalytics #powerquery #powerbi #excel #mquery File used in video github.com/jbotes/powerbiTut...
Power Query - Join a Table based on a Date Range 😎😶🌫️
Переглядів 2,4 тис.5 місяців тому
In this Video is show you how to join a 2 tables based on a date range. You'll learn how to join a inner and a outer table within one Query. #OuterInnerTable #powerbi #mquery #powerbidesktop #data #dataanalytics #excel. The File github.com/jbotes/powerbiTutorials/blob/main/Trades_video.xlsx Links to Functions used table.AddColumn powerquery.how/table-addcolumn/ Table.SelectRows powerquery.how/t...
Power Query - Extracting Patterns - Dates & Hash Tags 🦖🦩
Переглядів 1,6 тис.6 місяців тому
Part 2 of Pattern Extraction in Power Query in this video i show how you can extract Hashtags and dates from a dataset. #datavalidation #dataanalysis #powerbi #powerquery #excel #mquery File github.com/jbotes/powerbiTutorials/blob/main/pattern2Video.xlsx Query Website powerquery.how/ PowerQuery Book www.amazon.com/Definitive-Guide-Power-Query-transformation-ebook/dp/B0CKRL5M3F?ref_=ast_author_m...
Power Query - Fixed Pattern Extraction 🎾
Переглядів 2,1 тис.6 місяців тому
In this Video i show you how to extract a Fixed Pattern from a Dataset 🎾 #dataanalytics #data #datatransformation #etl #powerbi #powerquery #mquery #excel #exceltips File github.com/jbotes/powerbiTutorials/blob/main/PQ_Pattern_Extraction_Video.xlsx M Query Book www.amazon.com/Definitive-Guide-Power-Query-transformation-ebook/dp/B0CKRL5M3F?ref_=ast_author_mpb Noteworthy techniques - Custom Funct...
Power Query - Iteratively Add Calculated Columns 🦩🦖
Переглядів 2,8 тис.6 місяців тому
A great use case for using the powerful m-query iterator function list.accumulate to dynamically add new calculated columns to a output dataset. #dataanalytics #data #datatransformation #etl #excel #powerbi #powerquery File Link github.com/jbotes/powerbiTutorials/blob/main/PQ_ListAccum_NewColumns_Video.xlsx Noteworthy techniques - Dynamically add columns Functions used List.Accumulate powerquer...
Power Query - Add Report Context fields as Columns🩺📖
Переглядів 2,9 тис.7 місяців тому
In this Video i show you how to add Report Context fields as columns in a dataset do some excellent transformations and unpivot the dataset and put it all in a function to run on many similar datasets. #dataanalytics #data #datatransformation #powerbi #powerquery #powerbidesktop #excel #investment Solution File with Mquery github.com/jbotes/powerbiTutorials/blob/main/PQ_ReportheaderIntoReportVi...
Power Query - Dynamically Sort Table Columns by user input
Переглядів 1,5 тис.7 місяців тому
Power Query - Dynamically Sort Table Columns by user input
Power Query - Unstack Rows with List.Alternate
Переглядів 2,9 тис.7 місяців тому
Power Query - Unstack Rows with List.Alternate
Power Query - List.Buffer to do a Million row Lookup
Переглядів 9 тис.8 місяців тому
Power Query - List.Buffer to do a Million row Lookup
Power Query - Dynamically transform column names
Переглядів 2,4 тис.8 місяців тому
Power Query - Dynamically transform column names
Power Query - Adding spaces to CamelCase & number fields with List Accumulate
Переглядів 1,2 тис.9 місяців тому
Power Query - Adding spaces to CamelCase & number fields with List Accumulate
Power Query - Advanced Transformation of Nested Tables
Переглядів 7 тис.9 місяців тому
Power Query - Advanced Transformation of Nested Tables
Power Query - Split Table Dynamically
Переглядів 4,6 тис.10 місяців тому
Power Query - Split Table Dynamically
Power Query - Dynamically Promote Double Headers 🚀📊
Переглядів 1,1 тис.11 місяців тому
Power Query - Dynamically Promote Double Headers 🚀📊
Power Query - Unstack Uneven Rows to Columns🍔
Переглядів 1,1 тис.11 місяців тому
Power Query - Unstack Uneven Rows to Columns🍔
Power Query - Unstack Rows to Columns
Переглядів 74311 місяців тому
Power Query - Unstack Rows to Columns
Power Query - Connecting and returning data from ChatGPT 🤖📞
Переглядів 98211 місяців тому
Power Query - Connecting and returning data from ChatGPT 🤖📞
Power Query - Dynamically Expand Many Columns to Rows
Переглядів 1,4 тис.Рік тому
Power Query - Dynamically Expand Many Columns to Rows
PowerBI & DAX - Lost Customers (Absolute) 🕵️♂️👥❌
Переглядів 1,9 тис.Рік тому
PowerBI & DAX - Lost Customers (Absolute) 🕵️♂️👥❌
Superb ❤
Thanks for sharing your knowledge. Used the pattern extraction and others. Great stuff! Love the modem sound during the intro as well! 56K!
Thank you very much
That is just Awesome
Thank you very much. Not sure why it took me this long to see your channel. Love your content. subcribed.
Perfect 👌
Great, you helped me so much in my work!
Loving the new data lair...
The Worm!
Trop fort !!!
glorious!
Love your style. This is just personal preference but; you added [Months]{0} to your M code in your main Table1 query, would it have been easier to right-click on Row{0} then "drill down", which would omit the need for that additional M code in your Table1 query. I tend to "Define name" the cells in an Excel worksheet and use that as your query reference.
this is a excellent tip! i'm gonna try it! this is what i love learning new ways to do things!
@basensei8699 please do try it, I do it a lot, almost every solution I build
@@paspuggie48 you can even do it in one line of code like this. = Excel.CurrentWorkbook(){[Name="Table10"]}[Content]{0}[Months]
Right click on cell in Excel, then 'Define Name' and give it a name. Right click on the cell and 'Get data from Table/Range' (your query will be named as the Defined Name) In the PQ Editor, right click on the row (which is your text on row {0}), then 'Drill Down' You can reference this query in your M code in other queries. I do this for text, numbers, dates etc. I also create Tables in Excel and when brought into the Editor, right click the whole column and 'Drill Down'. As an example, I do this for a list should I want to do a 'Filter' but for multiple words/criteria etc. It's such an effective way of exploiting this capability.🙂
great as always ❤
thanks man!
Great one👍
glorious!
Hi! 😊I can't seem to find the advanced editor code for this video - could someone please help me out? Thank you 😊😊
Will this work quickly across 10K+ rows of data? Seems slow on my end. Wonder if there is a faster way
Awesome man! List.Accumulate what a dazzling function! I've watched a couple of videos about it and got a good grasp of its mechanics, now I'm lost. 😅 I need to dig in deep into it. But using it with Table.TransformColumns made this tutorial AWESOME. 👍✌👏👏👏
Great video!!!
Thanks for your explanation.
Great content... exactly what I needed to combine several sheets with different headers into one master sheet!
This is what i want. Awesome .
Thank you so much Sensei, your videos are Awesome!
I have a huge data base in json file format. There are a lot of lists and records and many lists and records contain more list and records in it. Its a tiring and irritating task to expand more than 50 lists and records manually one by one. This also creates a lot of duplicate records. So, How to expand multiple lists and records together in power query.?
That is VERY impressive
Brother you are the most entertaining dude on this platform. I love your videos. I learn a ton and it’s so much fun along the way. Never stop cranking these out.
Great
For me learning M code from BA Sensei has been by far the easiest - Thank you so much BA Sensei!
Happy to hear that!
Showing what South Africans are made off! Well done man, you're a rock star!
glorious thanks man!
Super Awesome!
I tried it but my query is not recognizing the “InstanceCount” :(
glorious thank you man!
When I see a new BA Sensei video, I click!
Brilliant as usual. Excellent content.
Thanks for the video James. very helpful.
Great video, ... just a suggestion that you change your RANDBETWEEN formulas to hard coded values (prior to query) so that when we are trying to duplicate your results we can compare and get the same values. I thought I was doing something wrong because my percentage results were not the same as your video; turns out that randbetween was recalculating my values...
oh damn! yes i still had the randbetween on!
Excellent! What a great explanation of List.Accumulate! Many thanks!
Percentage is total for each individual cell or total of each column. So column 1 total is 100 and percentage in each cell is of the total? Right?
correct! the total for each portfolio!
We need more videos about list.accumlate to train
its one of the more tricky ones but once you get it! it's great!
Mind blowing🤪
Thanks James. I wonder how many times I'm going to have to view the video before list.accumulate sinks in!
and that's what videos are for 🙂
ye this is probably one of the more counter intuitive functions. vs. looping in python.
Awesome as always. Thanks James.
Excellent! I was look to find out some thing else out and found this, this is cool, what I was looking for is, in a new blank query is there an easy way of using a step in another query as the first step in this new blank query, I can referance that first query but it gives me the last step, I can dublicate the first query and delete the steps I don't want, but I like just to get the step I need.
Cool DAX trick
Thank you for this. Super helpful!
wau
so underscore is just like 'this' pointer to a current object used , ez but yet nice feature
You create such valuable content, and yet have such a low number of subscribers. You are really knowledgeable, maybe for some people you go to fast?!? Anyway, keep up the good work
Absolute master class. This provides so much valuable information. Thank you son much for showing this unique way of transforming tables.
You are very welcome
Brilliant
@Ba sensei can we do it without combining them?
yes indeed!
This is game changer 🎉
Not getting audio, other videos are playing fine