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
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.
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.
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.
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
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 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.
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.
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.
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
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
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!
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 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.
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!
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?
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.
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!
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
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.
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
