I really struggled with the underscore concept when I first started using Power Query but your explanations have been a massive help with this. It all sort of makes sense now. Thanks for another great video Chandeep
In my organisation we receive a dataset of over 70 columns and originally I would apply some trim & clean to those I knew that needed it. Over time that caused an issue because some other columns would require the same. I managed to use a similar technique to transform & clean every column before importing the data. It revolutionised the way I work now Goodly.
I think a key concept that makes this easier to understand is that the underscore is used twice but each instance is a different context resulting in it "pointing" to different items at different levels of the list within a list structure. You just did a video on each and underscore. It might be a good follow-up video to explore this notion that each and underscore can be utilized more than once in the same expression but where they are seeing different contexts.
Wow, thank you. I can't believe how much a time crunch can blind you to such a simple oversight. ---------------------------------------------------------------------- Well I thought I understood, it all seems very straightforward. I created the ColList function for my source and have the right List of column names, I used it in the Column Reording function and it worked perfectly. But when I enter the exact command that you have at 12:37 which is: = Table.TransformColumns(Source, List.Transform(ColList, each { _, each Text.Lower(_), type text} ) ) it displays the following Error: Expression.Error: We cannot convert the value "column name" to type Function. Details: Value=column name Type=[Type] And it appears to have lower-cased the Column Name instead of the Column Values. What am I missing?
You are an excellent teacher! Thanks again! Could you do a non-edited video where you are just solving a problem on the spot so we can see your thought process and approach to solving a problem? I think it will your community see how you deal with something unexpected when building your queries.
Flipping heck. A list within a list within a list sounds more like Inception! But yet you still explained it very well. Maybe you should try and explain the plot of Inception also!
Hi Chandeep. My first attemp was to use another technique previously learned from you to create the list of lists (below). I like your more advanced method using List.Transform. Always something new to learn. Thanks for the great examples! Thumbs up!! let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Custom1 = Table.ColumnNames(Source), #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Column1], "Col")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Lower), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each type text), #"Transposed Table" = Table.Transpose(#"Added Custom1"), Custom2 = Table.ToColumns(#"Transposed Table") in Custom2
My dataset has many date and date/time columns. I want to use "change type with locale" en-us on all of them together instead of one by one. Also if a new columns with date or date/time is added, then automatically apply this transformation to it.
Chandeep.... my goodness, this is simply M-indblowing. Gotta ask: How is it that you're not MVP yet? Kudos mate. Well done!!! Honored to be talking with you on the DNA Enterprise Summit!
As you have same function it was easy get the column name (without hardcoding) but help me if we have different function for each column, will this work? Please advise.
Thanks a lot for your detailed videos. You are a great teacher. I have a very unique issue. Through SAP I generate one report which has some redundant columns which needs to be added and transformed in a separate column. This needs to be done for some group of columns dynamically every month. I have used List.SelectFields and List.FieldValues function. But the query is calculating each value in the columns instead of Column Operation. Please suggest an alternative solution.
Can you put the logic in the collist query into the function instead of creating another query? Thanks for great explanation of how to interpret M code!
Wish I'd seen your list.transform 6 months ago after spending some time trying to use Text.From(_) , without putting it inside list.transform as a fucntion, I've said this before , it's great that you use small and simple date as an example as it makes it so much easer to see what's happening, also it's easy to make something up to follow along . Oh , just come across Tricks beyond the User Interface SSBI Central, which I'm looking forward to watching.
Hello, I have 2 excel files Jan-22 & Feb-22, each file contain 5 sheets sales, product , region, date & category, when I get 1 file and transform it all 5 sheets showing 5 queries separately, how can I combine feb-22 file in it and make it dynamically. Thanx
Can you please make a video demonstrating how we can apply RLS on a ranking scorecard table such that when any user logs in, they can see their actual overall ranking based on their scores without seeing any other users' data. I have tried making this but after applying RLS each user sees their own rank as 1.
Uff... great video and a difficult topic, thank You. I have a question: when I type: = Table.TransformColumns(Source, List.Transform( ColList , each { _, Text.Lower, type text} ) ) I get the correct results... can You comment on that?
You're right! You can simply use the function without the underscore if the operation isn't complex. But you'd need an underscore if you are trying to extract the first 3 letters after doing the lower case.
I have one unsolved query .could you please help me out .if the column contains the data as names and Gmail IDs and #twitter IDs ,and some other like diffent delimeters data .how to seperate the only names to one column and all Gmail IDs ,#twiiter IDs and delimeter data in to other column
Hi Chandeep Thanks for your fantastic videos. Using this video, I'm trying to do something similar, but I'm transforming the columns by multiplying the various columns, with a value. I can achieve a result where the multiplier is a constant but I'm wanting to get the multiplier values from another column of the table. (The multipliers vary, depending upon the row.) The column name list is ColNames and I've created a list of the multipliers and called it Percentages. I feel there must be a way to do this but I can't figure out how.) This is the code I've used which works with the constant. Source = Table.TransformColumns(Table1, List.Transform(ColNames, each {_, each _ * 10, type number})). Any suggestions would be greatly appreciated.
Debbie, I have tried this before. I don't think you can multiply values of 2 columns using table.transformcolumns. Adding a new column would give you that flexibility. Cheers
Extremely usefull and clear! Question on Table.TransformColumn: can I apply a transofrmation (on single or multiple columns) but based on the value of another column? My code here doesn't work: Table.TransformColumns(#"source",{{"FieldToTransform1",each (_*[OtherNumericField]), type number},{"FieldToTransform2",each (_*[OtherNumericField]), type number}}). I get this error: Expression.Error: We cannot apply field access to the type Number. Details: Value=203,22 Key=OtherNumericField PS I know I could use ReplaceValue, but I hate the type of the transformed column is being changed to Text, and I have to do it column by column and not sure about performance.
This was very useful I used Table.ColumnsOfType(Source,{type nullable text}) to get the text columns in my source table, so I could then apply my text transform to all the text columns My text transform was Text.Clean(_??"") I needed to use the coalesce operator ?? because I sometimes had nulls in my text columns so I needed to feed the function a blank value instead.
Thanks great video… is there a possibility to be able to update a date field based on another query that has been merged. So if I have a CustomerOrderDate and a CustomerOrderDateUpdate I would like to update the CustomerOrderDate with the CustomerOrderDateUpdate if one exists against each CustomerID
I am more and more amazed by your capabilities. It is impressive!
I really struggled with the underscore concept when I first started using Power Query but your explanations have been a massive help with this. It all sort of makes sense now. Thanks for another great video Chandeep
Glad it helped!
In my organisation we receive a dataset of over 70 columns and originally I would apply some trim & clean to those I knew that needed it. Over time that caused an issue because some other columns would require the same. I managed to use a similar technique to transform & clean every column before importing the data. It revolutionised the way I work now Goodly.
Cool! Glad it helped
I think a key concept that makes this easier to understand is that the underscore is used twice but each instance is a different context resulting in it "pointing" to different items at different levels of the list within a list structure.
You just did a video on each and underscore. It might be a good follow-up video to explore this notion that each and underscore can be utilized more than once in the same expression but where they are seeing different contexts.
I agree, but also I would like to see a video where in each example of each and _ you include the function version as well using () =>
Wow, thank you. I can't believe how much a time crunch can blind you to such a simple oversight.
----------------------------------------------------------------------
Well I thought I understood, it all seems very straightforward. I created the ColList function for my source and have the right List of column names, I used it in the Column Reording function and it worked perfectly. But when I enter the exact command that you have at 12:37 which is:
= Table.TransformColumns(Source,
List.Transform(ColList,
each { _, each Text.Lower(_), type text} ) )
it displays the following Error:
Expression.Error: We cannot convert the value "column name" to type Function.
Details:
Value=column name
Type=[Type]
And it appears to have lower-cased the Column Name instead of the Column Values. What am I missing?
wow, amazing ! and it's just what i was looking for !
You are an excellent teacher! Thanks again!
Could you do a non-edited video where you are just solving a problem on the spot so we can see your thought process and approach to solving a problem? I think it will your community see how you deal with something unexpected when building your queries.
Wow! This was very advanced and tricky! I can see you are well versed in M as well as Dax. Great solution. 👏🏾
Thanks! 😃
I am a beginner and I am 74years old. You have inspired me to continue learning.
Great to hear !
Flipping heck. A list within a list within a list sounds more like Inception! But yet you still explained it very well. Maybe you should try and explain the plot of Inception also!
You have succeeded in demystifying the each _ syntactic sugar. Excellent video.
Thank you!
Hi Chandeep. My first attemp was to use another technique previously learned from you to create the list of lists (below). I like your more advanced method using List.Transform. Always something new to learn. Thanks for the great examples! Thumbs up!!
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.ColumnNames(Source),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Column1], "Col")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Lower),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each type text),
#"Transposed Table" = Table.Transpose(#"Added Custom1"),
Custom2 = Table.ToColumns(#"Transposed Table")
in
Custom2
You are the best when it comes to teach M language to anyone 👏👏👏
Thank you!
My dataset has many date and date/time columns. I want to use "change type with locale" en-us on all of them together instead of one by one. Also if a new columns with date or date/time is added, then automatically apply this transformation to it.
Excellent logic! Thank you Chandeep!
Glad you like it !
Chandeep.... my goodness, this is simply M-indblowing.
Gotta ask: How is it that you're not MVP yet? Kudos mate. Well done!!!
Honored to be talking with you on the DNA Enterprise Summit!
Hey Carlos. Glad you found it helpful.
About the MVP thing - I don't know why 😂!
Great video! The breakdown of the process was easy to understand. Thanks for doing that.
Glad you enjoyed it!
@@GoodlyChandeep I had a similar scenario today and rewatched this video. Still just as good the second time around.
I think this method is such as better than any other, but what happen if the column names doesn't are equals?. Thanks Chandeep, you're the best!!
mind blowing and so well delivered ...thank you
Glad you liked it!
Thank you, Chandeep. Great tips!
Glad you like them!
Love your videos. Very informative, and your way of explaining works wonders for me.
Thank you again.
Great to hear!
Hey Goodly, love your video - really easy to understand.
Glad you love it!
As you have same function it was easy get the column name (without hardcoding) but help me if we have different function for each column, will this work? Please advise.
Awesome! I have a question; how can I apply a split column by delimiter in several columns at once?
You always amazed us with your every new videos.
Thank You !
Thanks a lot for your detailed videos. You are a great teacher.
I have a very unique issue.
Through SAP I generate one report which has some redundant columns which needs to be added and transformed in a separate column. This needs to be done for some group of columns dynamically every month.
I have used List.SelectFields and List.FieldValues function. But the query is calculating each value in the columns instead of Column Operation.
Please suggest an alternative solution.
Another fantastic video Chandeep !!👏👏
Thanks!
And this solutions save me . Thank you
Glad It was helpful!
Is there something similar for adding columns? All would refer to source table.
Wow nice vid! Is there a way to get the names of the columns based on data types instead?
Very Good
Thank you!
Chandeep, could you do a part 2 of the M-code basics covering underscore and "each," please? Those are immensely confusing...
Can use this solution for function table.SplitColumn?
Can you put the logic in the collist query into the function instead of creating another query?
Thanks for great explanation of how to interpret M code!
Yes, that's what I did when I implemented this solution.
Wish I'd seen your list.transform 6 months ago after spending some time trying to use Text.From(_) , without putting it inside list.transform as a fucntion, I've said this before , it's great that you use small and simple date as an example as it makes it so much easer to see what's happening, also it's easy to make something up to follow along . Oh , just come across Tricks beyond the User Interface SSBI Central, which I'm looking forward to watching.
Hello, Instead of Text, I wish to convert number, what should be mentioned in Transform function
Hello,
I have 2 excel files Jan-22 & Feb-22, each file contain 5 sheets sales, product , region, date & category, when I get 1 file and transform it all 5 sheets showing 5 queries separately, how can I combine feb-22 file in it and make it dynamically. Thanx
Hi Somebody knows how can access to other column using Table.TransformColumns?
Superb.
Thanks!
Can you please make a video demonstrating how we can apply RLS on a ranking scorecard table such that when any user logs in, they can see their actual overall ranking based on their scores without seeing any other users' data. I have tried making this but after applying RLS each user sees their own rank as 1.
Magic ❤
Thanks!
Uff... great video and a difficult topic, thank You.
I have a question: when I type:
= Table.TransformColumns(Source, List.Transform( ColList , each { _, Text.Lower, type text} ) )
I get the correct results... can You comment on that?
You're right!
You can simply use the function without the underscore if the operation isn't complex. But you'd need an underscore if you are trying to extract the first 3 letters after doing the lower case.
Thank you
Welcome!
I have one unsolved query .could you please help me out .if the column contains the data as names and Gmail IDs and #twitter IDs ,and some other like diffent delimeters data .how to seperate the only names to one column and all Gmail IDs ,#twiiter IDs and delimeter data in to other column
Hi Chandeep
Thanks for your fantastic videos. Using this video, I'm trying to do something similar, but I'm transforming the columns by multiplying the various columns, with a value. I can achieve a result where the multiplier is a constant but I'm wanting to get the multiplier values from another column of the table. (The multipliers vary, depending upon the row.)
The column name list is ColNames and I've created a list of the multipliers and called it Percentages. I feel there must be a way to do this but I can't figure out how.)
This is the code I've used which works with the constant.
Source = Table.TransformColumns(Table1, List.Transform(ColNames, each {_, each _ * 10, type number})).
Any suggestions would be greatly appreciated.
Debbie, I have tried this before. I don't think you can multiply values of 2 columns using table.transformcolumns.
Adding a new column would give you that flexibility.
Cheers
@@GoodlyChandeep Thanks for your very fast reply. I really appreciate it. I’ll use the method you suggest.
😍😍😍😍😍😍
💯👍
i randomly saw this and understood the concept.
Awesome !
👍
Extremely usefull and clear! Question on Table.TransformColumn: can I apply a transofrmation (on single or multiple columns) but based on the value of another column?
My code here doesn't work:
Table.TransformColumns(#"source",{{"FieldToTransform1",each (_*[OtherNumericField]), type number},{"FieldToTransform2",each (_*[OtherNumericField]), type number}}).
I get this error: Expression.Error: We cannot apply field access to the type Number. Details:
Value=203,22
Key=OtherNumericField
PS I know I could use ReplaceValue, but I hate the type of the transformed column is being changed to Text, and I have to do it column by column and not sure about performance.
unfortunately you cannot :(
that needs to be added as a new column
@@GoodlyChandeep that's unfortunate. Thanks for your quick answer!
i find a simply solution. "Table.TransformColumns(Source,{"value",each _},Text.Lower)" it solve everything . no matter how many cols appear
Amazing @jianlinchen7978 👌🏽
am I the only one play your videos at 0.75 playback speed?
Please help me out
This was very useful
I used
Table.ColumnsOfType(Source,{type nullable text}) to get the text columns in my source table, so I could then apply my text transform to all the text columns
My text transform was Text.Clean(_??"")
I needed to use the coalesce operator ?? because I sometimes had nulls in my text columns so I needed to feed the function a blank value instead.
Thanks great video… is there a possibility to be able to update a date field based on another query that has been merged. So if I have a CustomerOrderDate and a CustomerOrderDateUpdate I would like to update the CustomerOrderDate with the CustomerOrderDateUpdate if one exists against each CustomerID
Please solve this complex problem with power query
hackerrank/challenges/15-days-of-learning-sql/problem