Download the file ⬇ - goodly.co.in/dynamic-column-creation-power-query-list-accumulate List.Accumulate in Power Query with Practical Examples - th-cam.com/video/RIo4OrPixco/w-d-xo.htmlsi=VVxF51DSux0-3Wo4 Tackle even the most challenging data cleaning problems. Check out the M Language course and push beyond the user interface. ↗- rb.gy/a2zsnn
Every body is praising chandeep for it's excel skill (which he deserves), but i can't help to outline, the quality of the video production, it looks like hollywood for excel geeks 🤓 Good job man ! All the best for your course !
The way you explained the video, first presenting the problem; then, the method, the function and, finally, the solution, was very good, very educational. I learn a lot from your videos! Success! Hi from Brasil 🇧🇷! God bless you!
I love your videos bro. Most other people I see covering DAX or Power M have literally no idea what they are talking about. You are covering fundamental programming concepts & explaining them very well!!
There are a lot of junk/useless tutorials on TH-cam at the moment... with terrible use-cases and examples. I'm a new subscriber because you are providing INCREDIBLE content and actually useful examples of how to get the most out of PowerQuery/M - fantastic, well done, well presented!
As always, I really like your videos. Thanks! In real life, I still prefer to use a DAX solution for solving this specific issue. The way I use List.Acumulate is to search a text column for a specific range of projectcodes. Problem stays that Text.Contains in combination with List.Acumulate is still a terrible slow solution. If you ever have a solution for searching a specific list of codes in a text column full of other text around the codes, let me know 😊.
Wow. Power Query is amazing for data prep. How did you know about that Record.Field function? I am now comfortable with the fact that I will never know everything about Power Query. I'll always be learning something new in it. Unbelievable lesson.
Saw a video from a while back when he used another function. I think it was List.Last(_ etc.. and got the value field.. Found Record.Field much more easier to execute this..Chandeep keeps evolving 🔥
I have an inquiry regarding setting up a logic in Power BI that allows users to select colours based on Excel input data, affecting the colour of column headers in a dynamic fashion. The idea is for the system to recognise numerical inputs - with '1' representing green, '2' signifying blue, and '3' indicating red - and to modify the column header colour accordingly. The dataset is updated on a monthly basis, which calls for a logic where changes made in Excel are seamlessly integrated and mirrored in Power BI. My focus is solely on columns, as my column tables don't include rows. Despite the unique nature of this user requirement, which has kept me puzzled for nearly a week, I'm reaching out to see if you could provide guidance or solution. Thanks
Hi Chandeep. By any chance if you can reply if there is a way through which I can dynamicallh make sure to get latest 2 year of data in Incremental Refresh... In short how to make the parameters dynamic In power Query?
This is so awesome. But how would you go about if you want to subtract the new column from the previous column? For example; instead of COGS, HR, etc. you have: balance jan, balance feb, balance march, etc and you want to calculate the change that happened within each month?
Hi Chandeep. I have a column having 4 different values. I would like to replace any of the 4 different values with a value "FAST". Can you please suggest the best solution in power query?
Chandeep, using this could you insert a column directly into the excel table and expect it to work, or would you have to add the column in Power Query?
Goede uitleg hoor! Ik ben hier wel eens mee bezig geweest en wilde verschillende datatypes toe te voegen. Maar dat is me niet gelukt. Heb je daar misschien een suggestie voor?
I think you really like List.Accer, as do I, also anything to do with records is really useful, BTW, i don't know if you saw message re dynamic data types and this construction, Value.ReplaceType( Table_non_correctTypes, Value.Type( DataTypes) ) Where DataTypes is a table or previous step with the correct types, still not sur how it works.
Great Video... can i set the number formats dynamically using mcode..instance, used percent.type in video, got the percent but not the % symbol with it...should i need to concatenate with this.. thanks in advance your guidance
Awsom Sir, Kindly make a video for below. If we have nested Table in a single column (while Grouping) and each Table has multiple column inside it. like as below. 1 Table has 10 column 2 Table has 12 column 3 Table has 8 column Then how can we make equal all number of column for All Tables. (Like 12 column for each table by inserting new null column).
Hi Chandeep, great video. One question though..for this to take into effect in pbi (dynamic column addition), this would require a schema update which could only be achieved by having the refresh done on pbi desktop rite? It will not work when we are doing the refresh via pbi online, rite?
I knew it and i was using it, but in my case a was passing separate instructions to each column. Unfortunately i had to hardcode it, as i have no clue how to pass dynamically these instructions..
Hello, thank you very much for your videos, they are very interesting. I've been trying to search your channel for something that helps me calculate what I'm trying to calculate for days. In my case I have something like this, in the same column: Row A1 = (POWER(1+Forward_Speed,-1)) Row A2 = A1 * (POWER(1+Forward_Speed,-1)) Row A3 = A2 * (POWER(1+Forward_Speed,-1)) Row A4 = A3 * (POWER(1+Forward_Speed,-1)) …and so on in the same column that has 930 records. I've been trying to replicate this in Power BI for days, creating several variables, making conditionals with them, etc., but I'm not able to reach the same result that I calculated by doing this in Excel. Do you have a video that can explain more or less what I'm trying to do? Or could you make one that shows how to do it? Thank you so much
Debugging this code using function, along with List.Generate, can be a real challenge without a proper code editor and dubegguer. Unfortunately, it seems Microsoft isn't paying attention to the frustrations of M coders.
1. Can we execute such scenario using DAX? 2. Can you please use inventory projection or rolling forward scenario either in DAX or M code? No one explaining such real world problems.
Download the file ⬇ - goodly.co.in/dynamic-column-creation-power-query-list-accumulate
List.Accumulate in Power Query with Practical Examples - th-cam.com/video/RIo4OrPixco/w-d-xo.htmlsi=VVxF51DSux0-3Wo4
Tackle even the most challenging data cleaning problems. Check out the M Language course and push beyond the user interface. ↗- rb.gy/a2zsnn
= Table.Group(表,"列1",{"分组",each _},0,(x,y)=>Number.From( List.Sum(List.Range(表[列1],List.PositionOf(表[列1],x),List.PositionOf(表[列1],y)-List.PositionOf(表[列1],x))) >9) )
find out the mistake,please
Every body is praising chandeep for it's excel skill (which he deserves), but i can't help to outline, the quality of the video production, it looks like hollywood for excel geeks 🤓
Good job man ! All the best for your course !
Editor ... where are you buddy 🎉
The way you explained the video, first presenting the problem; then, the method, the function and, finally, the solution, was very good, very educational. I learn a lot from your videos! Success! Hi from Brasil 🇧🇷! God bless you!
Dude you just saved me a week's worth of back and forth
I love your videos bro. Most other people I see covering DAX or Power M have literally no idea what they are talking about. You are covering fundamental programming concepts & explaining them very well!!
What a marvellous lesson! Thank you!
Fantastic topic clearly explained! Just the type of thing I've been trying to do lately, not having any hard-coded column names anywhere in my code.
Great video! Would have loved to find this a couple of weeks ago, but really glad to have the validation of the method! Great stuff in this channel!🎉
Incredible demonstration Chandeep. Thank you very much.
Such a great way to sharing concept...yet not untill you do it by self does we realise how complicated it can be...learned another trick today...
There are a lot of junk/useless tutorials on TH-cam at the moment... with terrible use-cases and examples. I'm a new subscriber because you are providing INCREDIBLE content and actually useful examples of how to get the most out of PowerQuery/M - fantastic, well done, well presented!
Your video is very helpful and understandable, you're a great tutor!
This is so cool, Chandeep. You are the best teacher as always, thank you.
Brilliant! Thank you so much for sharing!
As always, I really like your videos. Thanks! In real life, I still prefer to use a DAX solution for solving this specific issue. The way I use List.Acumulate is to search a text column for a specific range of projectcodes. Problem stays that Text.Contains in combination with List.Acumulate is still a terrible slow solution. If you ever have a solution for searching a specific list of codes in a text column full of other text around the codes, let me know 😊.
Is it slow because of List.Accumulate or Text.Contains?
Mind blown 🤯
you are real champ. Much appreciate your way of explanation.
Wow. Power Query is amazing for data prep. How did you know about that Record.Field function? I am now comfortable with the fact that I will never know everything about Power Query. I'll always be learning something new in it. Unbelievable lesson.
Saw a video from a while back when he used another function. I think it was List.Last(_ etc.. and got the value field.. Found Record.Field much more easier to execute this..Chandeep keeps evolving 🔥
Useful but difficult. I must see many times to understand. Thank you
Amazing lesson as usual!
🙂Thnak you Chandeep Paaji for teaching us interesting topic
You are awesome Bro.. 🎉🎉🎉
Fantastic and Highly helpful. Thank you.
Amazing knowledge pal. Thanks for sharing
well done, Mr.
Thank you very much for sharing ... this is super useful¡¡¡¡¡
Wow. Very useful!
I have an inquiry regarding setting up a logic in Power BI that allows users to select colours based on Excel input data, affecting the colour of column headers in a dynamic fashion. The idea is for the system to recognise numerical inputs - with '1' representing green, '2' signifying blue, and '3' indicating red - and to modify the column header colour accordingly. The dataset is updated on a monthly basis, which calls for a logic where changes made in Excel are seamlessly integrated and mirrored in Power BI. My focus is solely on columns, as my column tables don't include rows. Despite the unique nature of this user requirement, which has kept me puzzled for nearly a week, I'm reaching out to see if you could provide guidance or solution. Thanks
Very nice tutorial
Great video bro!
Great 💯👍
Congratulations! Thank you.
Super Awesome !!!
Absolute cinema!
Hi Chandeep. By any chance if you can reply if there is a way through which I can dynamicallh make sure to get latest 2 year of data in Incremental Refresh... In short how to make the parameters dynamic In power Query?
High speed lecture!
This is so awesome. But how would you go about if you want to subtract the new column from the previous column? For example; instead of COGS, HR, etc. you have: balance jan, balance feb, balance march, etc and you want to calculate the change that happened within each month?
I would unpivot month columns. Then add two index col.first starting from 1 the other from 0. Split query and then join them based on Index numbers.
This awesome function, I have make Crossjoin with multiple table using this
7:10 But what if more columns are added to the front - meaning that the number of columns which you have to skip would change?
Hi Chandeep. I have a column having 4 different values. I would like to replace any of the 4 different values with a value "FAST". Can you please suggest the best solution in power query?
wow this is awesome,
Thanks. 3 "accumulated" times awesome
Chandeep, using this could you insert a column directly into the excel table and expect it to work, or would you have to add the column in Power Query?
Awesome 👍
Goede uitleg hoor! Ik ben hier wel eens mee bezig geweest en wilde verschillende datatypes toe te voegen. Maar dat is me niet gelukt. Heb je daar misschien een suggestie voor?
amazing!
Vers helpful!
I think you really like List.Accer, as do I, also anything to do with records is really useful,
BTW, i don't know if you saw message re dynamic data types and this construction,
Value.ReplaceType( Table_non_correctTypes,
Value.Type( DataTypes) )
Where DataTypes is a table or previous step with the correct types, still not sur how it works.
Great Video... can i set the number formats dynamically using mcode..instance, used percent.type in video, got the percent but not the % symbol with it...should i need to concatenate with this.. thanks in advance your guidance
Percentage doesn't apply in PQ. I think it is a bug.
Спасибо за видео. Хороший кейс. Чего, отопление вырубили?
Awsom Sir, Kindly make a video for below.
If we have nested Table in a single column (while Grouping) and each Table has multiple column inside it. like as below.
1 Table has 10 column
2 Table has 12 column
3 Table has 8 column
Then how can we make equal all number of column for All Tables. (Like 12 column for each table by inserting new null column).
Use table.combine
Yip is is AWESOME!!
Can i use that method to insert multiple rows using Table.InsertRows?!
Hi Chandeep, great video. One question though..for this to take into effect in pbi (dynamic column addition), this would require a schema update which could only be achieved by having the refresh done on pbi desktop rite? It will not work when we are doing the refresh via pbi online, rite?
I knew it and i was using it, but in my case a was passing separate instructions to each column. Unfortunately i had to hardcode it, as i have no clue how to pass dynamically these instructions..
What is the a for if we are only using current ??
Can we do upsert in power query
How can i substract value from first row and remaining value substract from second row
Great
I think now you can make tutorials about video fx.😊
Awesome
Hello, thank you very much for your videos, they are very interesting. I've been trying to search your channel for something that helps me calculate what I'm trying to calculate for days.
In my case I have something like this, in the same column:
Row A1 = (POWER(1+Forward_Speed,-1))
Row A2 = A1 * (POWER(1+Forward_Speed,-1))
Row A3 = A2 * (POWER(1+Forward_Speed,-1))
Row A4 = A3 * (POWER(1+Forward_Speed,-1))
…and so on in the same column that has 930 records.
I've been trying to replicate this in Power BI for days, creating several variables, making conditionals with them, etc., but I'm not able to reach the same result that I calculated by doing this in Excel.
Do you have a video that can explain more or less what I'm trying to do? Or could you make one that shows how to do it?
Thank you so much
Debugging this code using function, along with List.Generate, can be a real challenge without a proper code editor and dubegguer. Unfortunately, it seems Microsoft isn't paying attention to the frustrations of M coders.
Just revised List.Accumulate Function through a new case study.
1. Can we execute such scenario using DAX?
2. Can you please use inventory projection or rolling forward scenario either in DAX or M code?
No one explaining such real world problems.
List.Accumulate confuses me - but this example is close to turning on the light bulb for me
I can't understand the logic of this fourmala even i saw the recomended video which highlited could you repeat on another video
What if the DummyExpense column had a list of numbers like 20, 46, 54 etc,
You just assumed all the values were 20 😅
Briljant
Well explained but way too complex for me to understand or grasp it😪🥲