THANK YOU - I've been trying to find this for ages and your explanation was the only one that not only answered my problem but showed why step by step. Cannot wait to use this again and again, most helpful Power Query video I've seen yet.
Kudos to both you and the comments below. Interesting to watch your video and read the comments, It is one thing to find a solution but its another thing all together making file as light as possible whilst still getting the result.
This only works with small datasets, with large dataset it massively slows down the whole dashboard. Is there any way to do it similarly for larger datasets ?
Thank you very much, in general very clear illustration step by step 👍👍. But I am still wondering how the formula definitely related the function name 'InnerTable' to the AllData table, not the Summary one. There is no clear definition to which table that the variable name 'InnerTable' is linked to. I would appreciate if you could explain more on this. Thanks in advance. 🌹🌹
Thanks. will it occupy more space? My Query as follows, Value.Divide(List.Sum(Table.SelectRows( UserSessionLog, (InnerTable) => InnerTable[Email]=[#"Email-ID"] )[TotalTimeSpent_Sec]),60) It is taking long time to execute. any suggestion on this query.
Thank you very much, it's amazing and very helpful. One question though, it has made the loading data really really slow, I mean earlier it used to take 10-15 seconds that now takes almost 5-6 minutes. that too only for 9k rows. Is there any workaround to make data load faster?
Hey thanks for sharing this video it’s quite impressive. I just wonder how to use the inner table do you have other sample for the inner table that I can reference to
This trick is helpful. I get it that it is inefficient with large / voluminous data. But if I had to do it with a smaller set and needed sum of multiple columns - what would be the additional steps. I do intend to experiment with the group by option and then aggregate suggested Bart Titulear (thanks to Bart also), but it would be helpful to understand the steps
Thanks Chandeep But there is one question, if there are more than 2 table then how we can assign for a particular table? That time AllTable will pickup the required table??
hi chandeep..thanks for making this videro... here i have one Query i am not sure whther i am wrong or right but when you write a code for filter , then how does filter picks only "A" and location "East " however nothing is mention in code ..... you just write an equation inner table(location)=(location)... how is this working on only east location ?
Thanks for the super clear explanation! I have been searching in many videos about this issue I have, and luckily I found this video. And just clicked Subscribed :). Btw I have one issue, if the master data table has Guys A, B, C, D, E, but in the transaction table only has names A, B, C, D, then the result in Guys E will be "null". How to replace this "null" becomes 0 (zero) ?
Thanks for the great video, the formula is awesome. However, with having over 5k rows in both tables the power query is taking a very looong time to refresh. Is there another alternative that would calculate results quicker?
I seem to be having an issue with power query recognizing the "inner table" it is putting #"AllTable" around the name of the table. Could this be the issue? Not sure why it is not recognizing the inner table
I have a data set of 22k rows and this slows down my machine a lot. 22k rows is not a lot for power query. I removed all other columns expect for 3 columns with 22k+ rows. I can’t think of a reason why this is so slow to query results.. any idea?? Thanks!
It is very good explained and it works. Only one thing¨/question. For bigger dataset it takes a lot of time. Is there any other faster posibility? Thanks
is there any way to do this but rather than a sum to instead have a median, average, min, max. is there a comparable list.sum for these other functions?
Thanks for the step by step explanation!! Though I personally found that variable part quite tricky (as in the process by which you declare a variable in M.).
Hey Smit, I understand variables especially when used with functions can be slightly tricky to understand, especially due to it's tricky syntax. I too, took a while to understand and I am still exploring them :D Perhaps I can make a few vids on M language basics. Cheers!
@@GoodlyChandeep i still dont understand how simple porque assumes the variable just by adding the [Name], how does it understand the table is that column, what is there is another column with tables with a column called Name
Exactly what I was looking to, Ive been working with keys to bring up values from other tables, but this wraps it up in few lines of code. Can you give us an example with sum.ifs into nested ifs? Thank you so much.
I've found it can massively improve performance if you buffer the "inner" table first, outside of the Table.AddColumn function, and then reference that rather than the actual query itself, as that means it'll only be refreshed once (rather than once for every row of the table you're adding the column to). So for this example in the advanced editor for the step I would do something like: sumif = let buffer = Table.Buffer(AllTable) in Table.AddColumn(Source, "SUMIF", each List.Sum(Table.SelectRows(buffer, (InnerTable) => InnerTable[Names] = [Guys] and InnerTable[Location] = [Location])[Value])) Obviously buffering depends on having sufficient memory, and it'll break query folding, but where those aren't considerations it sped up my aggregations a lot.
How do I add another SUMIF Column if I want to bring in 2 value columns from another table? I tried to copy the steps but it only seems to flip back and forth from the sum of 1 column at a time.
Thank you Sir, I have applied it in my model. However, it gave me the below message "Expression.Error: The column 'value' of the table wasn't found. Details: value" can you please advide?
How can we do a recursive . I have a table with employee and manager. I am trying to get a list of all employees working for a manager. There could be multi level. Thanks in advance for your help. Your videos are awesome
Hi! I'm very fresh in Power BT, so, sorry for the stupid question... I it possible to make a column filed with "others" in this example? I.e.: bringing "east" totals and summing all others with "others" instead of the region name?
Thank you for your video and solution. I have a question. I used this method and added if statement in query but I got error message while there was no error in syntax. Query : Table.SelectRows( #"Crosstab1", (InnerTable)=> ( (each if [AAA] = "" then "" else InnerTable[FFF]) & (each if [BBB] = "" then "" else InnerTable[GGG]) & (each if [CCC] = "" then "" else InnerTable[HHH]) ) = ([AAA] & [BBB] & [CCC]) ) Error : Expression.Error: We cannot apply operator & to types Function and Function. Details: Operator=& Left=[Function] Right=[Function]
You r very Good in M code.....but the thing is you went directly there without touching the basic how to upload transform add column group by relationship tables you have to teach them very basic then move to M code 😅 if we know basic and now we learn M very easily it comes with Ai and chat gpt
Hello mister. I follow your video's quite some time and you explain very well. I learn a lot from you, thank you. But in this video I was supprised. Why come up with such a difficult solution.? You can merge the two tables by selecting one after another two collumns. and then not expand but aggregate. Here is the code (I only used the GUI, no editting 😉) , I later renamed my table1 and table2 to tbl_large and tbl_small: let Source = Table.NestedJoin(Table2, {"Guys", "Location"}, Tbl_large, {"names", "Location"}, "Table1", JoinKind.LeftOuter), #"Aggregated Table1" = Table.AggregateTableColumn(Source, "Table1", {{"Value", List.Sum, "Sum of Value"}}) in #"Aggregated Table1"
Mm from my understanding SUMIF is quite easy in Power BI but do we have anything for SUMIFS in Power BI, What I mean instead of one condition if we have multiple condition
hi, can you show how one can add two columns of data that are saved in two separate tables? for example, Table 1 contains the values 10, 20, 30. table 2 contains 100, 200, 300. I want to see the result 110, 220, 330, preferably in its own table or a as a separate column within table 1 or table 2. thanks
I got to the second lookup and it states "This Table is empty". Why is that? =Table.SelectRows( #"Prior POG", (InnerTable) => InnerTable[DIV STORE] = [DIV STORE] and InnerTable[UPC] = [UPC] )
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
THANK YOU - I've been trying to find this for ages and your explanation was the only one that not only answered my problem but showed why step by step. Cannot wait to use this again and again, most helpful Power Query video I've seen yet.
This was exactly what I needed - thank you so much! Really clear instructions and building it up to the final thing, really helps!
You are an excellent coder, teacher, and speaker. Thank you.
Thank you for your beautiful words! 💚
Outstanding! The way you teach, step-by-step, is the best way to build a solid understanding.
Glad it was helpful :)
I have watched so many videos trying to figure out how to do this, before I found this video. So well explained - thank you!!
SUPER-LOVE your video, buddy!! the best part is that it carries not just a Wham-Bam solution, but a step-by-step explanation
You are basically doing the most common part of SQL work in powerqeury! genius!
Thanks 💚
that's one of the few times, were you look something up and get exactly what is needed, thanks a lot !
Glad it helped!
Kudos to both you and the comments below.
Interesting to watch your video and read the comments, It is one thing to find a solution but its another thing all together making file as light as possible whilst still getting the result.
Dude, you're the best!! Been searching for this for like a month! Kudos my friend!
WoW!!! What a nice video with its clear explanation. Thank you so much, and thanks to God that we have your dedication.
What a beast!! great way of explaining! I mean, this looks difficult AF! but you explained it and is actually very easy!!
This is absolute genius!! Thank you so much. So powerful, so well explained, powerquery is so versatile it is amazing
This only works with small datasets, with large dataset it massively slows down the whole dashboard. Is there any way to do it similarly for larger datasets ?
Thank you for that explenation. It is a huge help for the Problem I had in PowerBI. Thank you so much.
Very clear and concise explanation! Thanks for this great explanation.
Thank you very much, in general very clear illustration step by step 👍👍. But I am still wondering how the formula definitely related the function name 'InnerTable' to the AllData table, not the Summary one. There is no clear definition to which table that the variable name 'InnerTable' is linked to. I would appreciate if you could explain more on this. Thanks in advance. 🌹🌹
Thanks!
Appreciate the Tip JB!
Such an instructive video! Really appreciated the detailed explanation with all the fundamentals. Thank you so much!
Glad you like it !
Great solution and very well presented. The downside though it is extremely slow. I work on a file with 90,000 lines and it takes forever.
You just make me smarter. Thanks for this extremely well-done and informative vid.
Glad you like it!
Really excellent stuff this is Chandeep!!
Thank you 😊
Very clear and very well explanined.
I'm gonna starting to following you.
Thanks
You my friend are a genius!!!
This guide is great , just what i needed to come further - thank you 🙂
Jesus we never think like this until we actually see it happen. Thank you chandeep!
Can this technique be used to get a value from another table instead of merge?
I'll recommend a merge, they are faster.
Thanks. will it occupy more space? My Query as follows, Value.Divide(List.Sum(Table.SelectRows(
UserSessionLog,
(InnerTable) => InnerTable[Email]=[#"Email-ID"]
)[TotalTimeSpent_Sec]),60)
It is taking long time to execute. any suggestion on this query.
same thing happened in myside, its take long time. Any suggestion ?
I have been looking for this exact thing... Thank you so much for creating this. It helped me in crunch time!
Glad it was helpful 💚
Thank you very much, it's amazing and very helpful. One question though, it has made the loading data really really slow, I mean earlier it used to take 10-15 seconds that now takes almost 5-6 minutes. that too only for 9k rows. Is there any workaround to make data load faster?
Thanks. Very well explained! I will try this on my data which has a lot more conditions but I’ll give it a go.
Hey thanks for sharing this video it’s quite impressive. I just wonder how to use the inner table do you have other sample for the inner table that I can reference to
This trick is helpful. I get it that it is inefficient with large / voluminous data. But if I had to do it with a smaller set and needed sum of multiple columns - what would be the additional steps. I do intend to experiment with the group by option and then aggregate suggested Bart Titulear (thanks to Bart also), but it would be helpful to understand the steps
Thanks Chandeep
But there is one question, if there are more than 2 table then how we can assign for a particular table? That time AllTable will pickup the required table??
Amazing solution, thank you so much. I just love the way you explain things
Glad you like this!
Wasn't there a Merge option available at that time? Using the merge and aggregation option is much faster.
How do you sum using conditions in merge option??
Brilliant- can see myself using this a lot. Very helpful, thank you
Glad it was helpful!
hi chandeep..thanks for making this videro... here i have one Query i am not sure whther i am wrong or right but when you write a code for filter , then how does filter picks only "A" and location "East " however nothing is mention in code ..... you just write an equation inner table(location)=(location)... how is this working on only east location ?
Kudos to your way of explanation 🙏
Glad you liked it
Thanks for the super clear explanation! I have been searching in many videos about this issue I have, and luckily I found this video. And just clicked Subscribed :). Btw I have one issue, if the master data table has Guys A, B, C, D, E, but in the transaction table only has names A, B, C, D, then the result in Guys E will be "null". How to replace this "null" becomes 0 (zero) ?
Excellent explanation 💯👍
Glad it was helpful Romiro!
Thanks for the great video, the formula is awesome. However, with having over 5k rows in both tables the power query is taking a very looong time to refresh. Is there another alternative that would calculate results quicker?
I too have the same question
How can i use it between 2 date
And want to know countifs too .
I seem to be having an issue with power query recognizing the "inner table" it is putting #"AllTable" around the name of the table. Could this be the issue? Not sure why it is not recognizing the inner table
I have a data set of 22k rows and this slows down my machine a lot. 22k rows is not a lot for power query. I removed all other columns expect for 3 columns with 22k+ rows. I can’t think of a reason why this is so slow to query results.. any idea?? Thanks!
I waited this for a long time..
Thanks for the video 🙏👍
It is very good explained and it works. Only one thing¨/question. For bigger dataset it takes a lot of time. Is there any other faster posibility? Thanks
Using relationships in PowerBI
is there any way to do this but rather than a sum to instead have a median, average, min, max. is there a comparable list.sum for these other functions?
Another great video! Need to get your course once I've saved up some money!
Awesome, thank you!
@@GoodlyChandeep No, thank you for the awesome content good sir! Keep it coming!
You sir, are a legend. Thank you for this
How to use sumproduct in power query
Great content buddy.. keep going!
Great examples thank you! I was working with similar scenario of part and inventory locations. much better solution than mine!
Is there any limitations of data as i am trying with 2 lakh line item working very slow please advise
Really you r so fantastic thx alot and go on with power Query
Thanks for the step by step explanation!!
Though I personally found that variable part quite tricky (as in the process by which you declare a variable in M.).
Hey Smit,
I understand variables especially when used with functions can be slightly tricky to understand, especially due to it's tricky syntax.
I too, took a while to understand and I am still exploring them :D Perhaps I can make a few vids on M language basics.
Cheers!
@@GoodlyChandeep Hi this was a very comprehensive video and the method you used is ingenious. Please do make a video on the M language basics.
@@GoodlyChandeep how to do sumif if you extract the data from a csv file.
@@GoodlyChandeep i still dont understand how simple porque assumes the variable just by adding the [Name], how does it understand the table is that column, what is there is another column with tables with a column called Name
Exactly what I was looking to, Ive been working with keys to bring up values from other tables, but this wraps it up in few lines of code. Can you give us an example with sum.ifs into nested ifs? Thank you so much.
Can it be a measure instead and result like this thamkyouuu
When I type the codes, I don't get illustrations (pop up box) , how to enable?
Sir, Inbuilt M functions are not highlighted when we try to use them, please help with this
How well does this scale? I have 2 tables with 20k and 40 k rows. I am patiently waiting for the load?
I've found it can massively improve performance if you buffer the "inner" table first, outside of the Table.AddColumn function, and then reference that rather than the actual query itself, as that means it'll only be refreshed once (rather than once for every row of the table you're adding the column to). So for this example in the advanced editor for the step I would do something like:
sumif =
let buffer = Table.Buffer(AllTable)
in Table.AddColumn(Source, "SUMIF", each List.Sum(Table.SelectRows(buffer, (InnerTable) => InnerTable[Names] = [Guys] and InnerTable[Location] = [Location])[Value]))
Obviously buffering depends on having sufficient memory, and it'll break query folding, but where those aren't considerations it sped up my aggregations a lot.
thank you for this video, i will review it again.. I am not used to creating formulas in Power Query, it's very new to me ... 😩
But thanks again
Date column suppose to calculate greater or less than in two tables sir
Hi, I know it's been a while since you posted the video, but I wondered if what I needed was just a unique value and I didn't have to sum anything?
C est expliqué si bien
Respect
Once giving variable inner table getting an error insert able wasn't recognized ?
How do I add another SUMIF Column if I want to bring in 2 value columns from another table? I tried to copy the steps but it only seems to flip back and forth from the sum of 1 column at a time.
Thank you Sir, I have applied it in my model. However, it gave me the below message "Expression.Error: The column 'value' of the table wasn't found.
Details:
value" can you please advide?
Beautiful explanation!
How to do sumif when u extract the data from a csv file brother. Please help.
Great trick!! I wonder how to apply with multiple value like value, value1, value2, ... , value n
Simple and GREAT. THANK YOU 😀
You're welcome!
How can we do a recursive . I have a table with employee and manager. I am trying to get a list of all employees working for a manager. There could be multi level. Thanks in advance for your help. Your videos are awesome
Hi! I'm very fresh in Power BT, so, sorry for the stupid question... I it possible to make a column filed with "others" in this example? I.e.: bringing "east" totals and summing all others with "others" instead of the region name?
outstanding explanation, thank you
Glad it was helpful!
How can I make it based on if cell contains text from summary table??
what is the purpose of creating variable? why cant we directly refer table instead of innertable variable?
It's very useful trick , thanks
How can I activate formurla suggestion pop-up? I'm using excel 2016
extremely helpful! thanks for sharing
awesome tutorial.. thanks
Thank you for your video and solution. I have a question. I used this method and added if statement in query but I got error message while there was no error in syntax.
Query :
Table.SelectRows(
#"Crosstab1",
(InnerTable)=>
(
(each if [AAA] = "" then "" else InnerTable[FFF]) &
(each if [BBB] = "" then "" else InnerTable[GGG]) & (each if [CCC] = "" then "" else InnerTable[HHH])
) =
([AAA] & [BBB] & [CCC])
)
Error :
Expression.Error: We cannot apply operator & to types Function and Function.
Details:
Operator=&
Left=[Function]
Right=[Function]
You r very Good in M code.....but the thing is you went directly there without touching the basic how to upload transform add column group by relationship tables you have to teach them very basic then move to M code 😅 if we know basic and now we learn M very easily it comes with Ai and chat gpt
Thank you, this is exactly what I needed. Regards.
Glad it helped!
Hello mister. I follow your video's quite some time and you explain very well. I learn a lot from you, thank you. But in this video I was supprised. Why come up with such a difficult solution.? You can merge the two tables by selecting one after another two collumns. and then not expand but aggregate. Here is the code (I only used the GUI, no editting 😉) , I later renamed my table1 and table2 to tbl_large and tbl_small:
let
Source = Table.NestedJoin(Table2, {"Guys", "Location"}, Tbl_large, {"names", "Location"}, "Table1", JoinKind.LeftOuter),
#"Aggregated Table1" = Table.AggregateTableColumn(Source, "Table1", {{"Value", List.Sum, "Sum of Value"}})
in
#"Aggregated Table1"
Hi Bart, thanks for the feedback.
In my experience merges often slow down the queries, hence that painful formula :-|
How do you do to see those options pop when you are writing? I have to write the complete code without options, is there something to activate it?
Update your Excel
Get Data > Query Options > Power Query Editor > Formula, check box for Enable M Intellisense...
Thank you so much! It was very helpful and this is exactly what I needed.
Be cautious of the performance! It tends to be very slow on Large Data
What if it’s not returning the matching value, but instead returning another one?
Hi Bro, this was a great video. However, when I try to select the column of the first table I get an error of syntaxis... but it is right spelt..
Mm from my understanding SUMIF is quite easy in Power BI but do we have anything for SUMIFS in Power BI, What I mean instead of one condition if we have multiple condition
voilà Chandeep 😎 👏
hi,
can you show how one can add two columns of data that are saved in two separate tables?
for example, Table 1 contains the values 10, 20, 30.
table 2 contains 100, 200, 300.
I want to see the result 110, 220, 330, preferably in its own table or a as a separate column within table 1 or table 2.
thanks
awesome power query trick
why did you declare a variable called inner function? why not reference the last step?
It doesnt work if you get the grab the data from Sharepoint. Please help.
Cool way of explanation.. Can we do all formula's and functions with in power query?
Sir how to add date criteria adding another date table
This is just crazy cool, dude!
Thank you! :)
😊😊💚
Custom Column was so cool....
I got to the second lookup and it states "This Table is empty". Why is that?
=Table.SelectRows(
#"Prior POG",
(InnerTable) => InnerTable[DIV STORE] = [DIV STORE] and
InnerTable[UPC] = [UPC]
)