You lead RU-vid in amount of information retained per second. All of your videos pack so much information in such a short amount of time. Thank you so much for the content.
Thanks, I had a hunch that the editor could be leveraged in this way but I just started using PowerBI and did not have the language and syntax knowledge yet. Great jumpstart -Thank you!
Great video. Just one suggestion that please consider making your speaking window smaller or cropping it to only show your body, so that audiences get to see more screen. I found a few videos of yours that you were deleting/moving steps that we can’t actually see what’s been deleted and moved. Nevertheless, Thanks for making such wonderful and educational videos for free to us all here.
This solution couldn't come in a better time, the concatenation suggestion solved the main issue I had with some data transformation. Thank you Chandeep. First video that I watched on your channel and definitely not the last one. Thank you once again.
Great work, man! I'm just silent! I've searched this solution for weeks, but unsuccessfully when somebody didnt suggest me do this with next step. But you did it with one professional mode! :)
At 4:16, the function creates a column that holds tables at each row. What confuses me is how the each function results in Tables since it is used in the context of a table. In your excellent video on the each function, the one thing I got was each would behave based on the context of what object its used in. Here its used in the context of a table and results in a table. Is it because a bunch of rows are the result and a bunch of rows = table?
Hi @Goodly What happens when there is a tie ? for example if two years have the same max value ? I am currently learning sql so in sql there are a windows functions called Row and Rank to deal with this level of complexity but I am not sure if we have any for M code.
Hi, this tick with text.combine is really great. Is there a possibility to combine also numbers (with out converting to text)? Currently I need two steps, one step in group_by: {"Single_Qty", each [Qty]}, and a 2nd step to extract the values: Extract Values from List = Table.TransformColumns(GroupedRows, {"Single_Qty", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text}) Do you have any approach to combine it in one group_by step? Thank and Cheers.
Thanks Chandeep for sharing this. This is a very important topic you touched. I work more with Power Query than DAX and having the ability to do such amazing transformations helps a lot. Will play around with this.
Awesome video!! I have being looking for this future for a while, is it possible to create two list? Let's say that I want to summary the total of pallets list.count I guess and then all of the different quantity, it worked with list.distinct, I want to get a new column that summarizes the total amount of pallets per material nr and the distribution of cuantity , similar to what you did. Output should be " 2 pallets* 400 Qty, 1 pallet*250units . I can send you the information so you can see the data
Hi Chandeep, need your expertise please, I have a datasets where there are 5 different amount fields with corresponding 5 different payment date for each payment , I want to get the amount paid for each payment date by year. Any thought on how I could get it done? Thank you kindly for your expertise! Yami
Hi Chandeep as usal awesome vedio. I have one doubt what if i need to get the sales for the particular year like 2004, or if need filter for one text in a column
You are really good. Thanks for your content. I am interested in filtering and grouping each table, and then combining. I can't seem to find a video in which you do that. Can you direct me or create something new?
Sir, plz make video for below. If 1 table has 9 column, 2 table has 10 and 3 table has 8 . Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.
Hi, I have below data with me. Column-A Abhay Abhay Abhay Ravikant Ravikant Ravikant Column-B January-2024 February-2024 March-2024 January-2024 February-2024 March-2024 Column-C (Sales) 1000 2000 3000 1200 1400 1600 I want 1) In Column A the row A1 & A2 should only Abhay & Ravikant. 2) Column B Should be January-2024, Column C as February-2024 & Column D as March-2024. 3) Column E should have Sales numbers each month. Please help
I have two columns in power query. One is a worker name and the other a column formatted as time where he records his time on different stages of a project. I need to group per worker name and then get total duration for his project. Any ideas? Tried everything.
It looks like This: Name Time Name1 14:00:00 Name1 14:10:00 Name1 14:15:00 Name2 8:00:00 Name2 8:10:00 Name2 8:15:00 Name2 8:20:00 I then need to group by the name and get the duration for each name
Hi Chandeep, what shall I say. It is boring to leave always the same comment 🤣. But I do have to questions concerning this. I have open item lists at various dates. I want to see which invoice is when in the OP list (OP report date) and what the invoice value is (Step invoice). I get the desired result, but would like to apply your solution in one step. Thanks for your advice. GrpRows_OPReportDate = Table.Group(ChgType_OPReportDate, {"Invoice No (MBP/GSP)"}, { {"OP Report Date", each Text.Combine(_[OP_Date],", "), type nullable text },
I really appreciate how you go through writing the M code step by step, coz its allowed me to break down the steps and know exactly where went wrong i.e. if i pulled a list instead of a table first for a function for tables. Thanks a lot!!
This has came at the PERFECT time! I've been asking myself these questions with advanced transformations for nested Tables, Lists and Records. I still have many questions - but you have again shined a bright guiding light in a world full of data darkness! Thank you bhaai-jaan! PS: Also amazed that you InteliSense works 🤣 - and so rapidly! What is this wizardry!? 🧙🏽♂️
You can also use the Group By and Sum the column you want to concatenate. For sure it returns an error first. But then in the formula replace List.Sum by Text.Combine and remember to complete it with the delimiter in the right place.
True, thanks for completing my answer as such avoding miscomprehension. I only wanted to point out the UI helps you discover and understand the logic behind M more then you'd realize. I often use the UI to give me a basic code that I can afterwards finetune.
Hello Sir, I have a small doubt.. What is the difference between group function and aggregate function? In real time, based on which scernio we will decide to use for group by or aggregate function ?
Thats an amazing video! Thank you for sharing! I was actually trying to do the opposite i.e transform a field with multiple value into mutiple lines with one value only per each line, any guidance?
Sir, How we can do the function similar to Indirect function in Excel, in power BI. For Ex. I am having a slicer with the name of all Indian states, If I don't select any thing from slicer, then in the table for ROW value , name of state should come and their population. If I select any state from slicer , then in the table, respective district list should appear and there population. Thanks in advance.
Can I make group by bin in power query, I mean if I have a table with some items and each one has its quantity and price and there is a running total column ,can I create group of items (1,2,3...)where each group doesn't have running total more than of 1000$?
This is a very advanced question, I think nobody asked before. If I have like 50 columns, and I want to group by only 2 of them, how can I agreggate automatically the other 48? 😈
Hello, I need yours assistance for one thing - I have two different table - Table1 & Table2 Both tables has records with n number of columns, one column is common in both table which is Country. table2 has another field called Status=Active, Inactive. so I want a one output Matrix Table (similarly mentioned below), where I want to get the Count of both tables based on Country Condition: Table1: no condition (count) Table2 : based on Status = Active (countif) Country Count from Table1 Count from Table2 ------------------------------------------------------------------------------------------------------------------- India 23 39 Canada 44 48 Brazil 51 7 United States 17 73 how we can get it in Power Bi, i have made but second table count coming sum of table2 in each row.. please help
Amaizing video, thanks, with wich keys you can give format to the function in the formula bar? Like indent code or pull a row of code into the next line? thanks
This is basically what I have been trying to do. Except, I want to create visualization so when you click on a title/movie, all the cast members pop up. Is that something you can explain?
I don't understand, isn't that a simple filter on the movie column that shows all the cast members? I am not sure where and why are you getting stuck :(
Excellent. Have you got a video which would point me to the right direction for mapping two lists and filtering on condition? Say we have a one row table with col1 [a, b, c], col 2 [1, 0, 1], col3 (output) [a, c]
If you have a table just filter it on the second column containing 1. If you have lists. download this file. goodly.co.in/wp-content/uploads/2024/06/For-Rokas.xlsx
@@GoodlyChandeep appreciate your answer, but what I meant is that each row of a column contains a list. In this example, col C1 row 1 would be a list [a, b, c], col C2 row 1 would also be a list [1, 0, 1] and the output col C3 should then be a list of letters from the list in C1 corresponding to 1s from the list in C2, in this case [a, c]. I'll give it another try. Thanks for your help anyway. Your videos are indispensable. Edit: solved by creating a temp column with tables from lists in C1 and C2, then filtering that table by selecting rows that match the condition, then selecting column with letters, and turning it back to list and text combining it. Convoluted, but works. Not sure if it will scale for millions of rows though :)
