Table.TransformColumns in Power Query | Super Function ⚡
Вставка
- Опубліковано 1 лип 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
Table.TransformColumns function in Power Query has quite a lot of hidden super powers if you decide it customize it. In this video I'll show you 3 interesting applications of Table.TransformColumns. Enjoy
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
1:42 Table.TransformColumns Syntax Basics
4:05 Example 1 - Working with IF
7:20 Example 2 - Working with IF + Data Types on 2 columns
13:32 Table.TransformColumns Hidden Feature for handling errors
14:49 Example 3 - Working with Multiple Excel Files
17:53 - My Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall UA-cam Channel Below
ua-cam.com/channels/GQE.html...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses - Наука та технологія
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Crack, te pasaste ameo. . With this superfast video you put in clear very power query concepts. Congrats!
Another mind blowing video to watch. Great tips
Really helpful. thank you. Nice to be able to compress the number of steps showing
The way you explained function is splendid. i would love to look forward to learn more functions with same explanation. Thanks for sharing.
you're a wizard! I'm relatively new to PQ but light years ahead of others just by watching your videos. Keep up the great work!
I want to thank you very much for your videos, I'm not exaggerating but you're leveling up my power query skills. I have used your tips in my day to day tasks and it has affected me greatly so thank you for the information you're offering and please continue 👏👏 if you can talk about performance optimization that would be great
Great to hear! I'll try to make a video on Query Diagnostics.
Thank you for the great video! Exactly what I was looking for.
Thank you so much! Great Tutorials! You are awesome!
Thanks for valuable information.
Brilliant Chandeep, thanks so much.
Thank you so much for your availability!
I have been using Power Query for many years but I haven't a chance to take this function into this fascinating next step. Thank you so much!!!!
Cool learning new concepts in pq day by by your videos 👌
Excellent lesson. Thank you very much. :-)
I’m glad for all the cleanup of your messy data! I’ve just started learning PQ and your videos are great! Plus your son is adorable in the DAX short. Lol
Thank you for your nice words J. S. B.! 💚
Very nice 👍
I,ve been wondered when i saw Text. Replace.
I put functions into each row with changing of arguments Text. Replace and It was awesome
Your level of teaching are far far beyond normal... I mean it's incredibly amazing how you demonstrate, talk and walk through examples. I wish you keep up this great great work to world community. Thanks a lot again. :)
Thanks for your beautiful words Jimmy! 😍
Very useful - thanks!
Thanks to this training, and your other videos, I was revisiting a query I'd built a while ago. I had a column that needed padding "0" added to the column. My current code was:
1. Add new Custom Column (PhoneNumberPadded) with the additional padding
2. Removed the original column (PhoneNumber)
3. Renamed the PhoneNumberPadded to PhoneNumber
Now... it's 1 TransformColumns step. Cleaner, easier to understand, less likely to break. Awesome! Many thanks.
Thank you very much, Chandeep. This explanation of the syntax was extremely helpful. Well done!
Glad it was helpful!
You're doing a great job. I found this channel by accident, because I checked out a few others. There is a lot of interesting content here. Thanks!
Glad you found it interesting! 💚
Brilliant, thank you
Great video, as always!
Great…got the amazing insights
In case anyone is trying to do multiple transformations on the same column (i.e. nested functions) you just put the _ underscore & first transformation inside brackets as per normal.
Which isn't to take away from a really helpful video which helped me figure it out when the official guide left me without a clue. Thanks Chandeep
Finished watching
Amazing. Thank you for teaching in a so Very easy way.
Glad you like it!
Simply Gold!!!
Thank you! 💚
Thanks Chandeep. Clear & Interesting ...as always!!
So nice of you
It was amazing! Thank you for shared.
Glad you like this Orlean!
I am very appreciate your video!!! Professonal example and mind set!!!
Glad you like it!
I can spend all day watching the applications of the functions you explain, very good material, thanks
Glad you like them!
I love it and give a good explanation about the M function. Thank you very much 👍👍👍👍👍👍
Glad you liked it!
A big thumbs up. Thank you bro!
We are teaching with videos and kind of books, one day we realize that we must practice every day not to forget. But how, with what? in this moment i’ll see goodly. Its very useful to me, thank you very much 🙏🙏🙏
Very Gold explained.Thank very much, so helpful
Glad you like it! 😊
Another awesome lesson Chandeep! With each of your videos, you widen my knowledge and understanding of the M language. I was able to take this technique and update some of my queries that previously relied on adding a custom column. Now, using this technique, I can do the transformations directly to the column in question without the need of adding a new column. Brilliant!! I'm curious if this method enhances query performance at all. With fewer applied steps, you might think so. But, maybe the difference is too slight to notice. Still, a great technique to know and use. Many thanks for all the great tutorials and learning resources at Goodly :)) Thumbs up!!
Frankly, I didn't check the query performance.
I am going to be doing a video soon on Query Diagnostics, that should help :)
Amazing sir😊
Very cool. Thanks for sharing 👍
Thanks for watching!
many thanks🙏
Good teacher using first principles.
Glad you think so!
Great, great and great. You make it look easy, even though it's not. Thanks for sharing Chandeep !
Thanks a lot 😊
I think all your other users said enough I agree with all of them, so I will just say thank you and that we appreciate you pal.
Thanks Brother!
Awsome as usual good job
Thx man. You are awesome
Brilliant function and presentation of examples. Thnak You.
Can you write how the use of the T.TC function affects the speed of data processing?
Can this function speed up the PQ performance in any case?
Congratulations!
You are really good!
Thank you so much 😀
The last part was the most awesome 👌
Thanks !
How could you think out of box !!! ??? Just great, what else could be said. Please make some more vedieos on power query.
Keep helping us with great Power Query tricks.
Stay Tuned 😊
Good learning & trick.
How to transform column by filter the data from other table
obrigado!
Wow, that last example. I'm having a vision of being able to pull a whole folder of Excel files, go through them one by one and dynamically get a different worksheet from each one based on some conditional logic. Haven't come accross this requirement before, but I know it's possible now.
Thank you! Hope it Will be helpful 💚
Thank you in advanced....
Please inform me, is your power query course cover such detail and advance topic like this, chandeep?
Last wala 10 seconds bahut surprising tha sach me😮😊😊
Absolute leader in m power query
Thank you 💚
Great combi of videos!! Question, in a terms of efficiency/process speed of query and loading of data, is this last "trick" good in performance once you loaded FROM FOLDER with like 100k of files, many workbooks and so on? I mean, refreshing can be annoying sometimes...
hi The Problem is you are good in explaining, one thing I like to see is to supply example files not in this one but when you use bigger data another thing this is a good video not in terms of explaining but the subject choosen as I said you vave a very good skill in teaching which is a must for any one who want to teach not every one has but I admit you have it, just keep up on good choosen important subjects both in power query and DAX
Thanks!
Thanks Chris, it means a lot :)
your videos give me the feeling of a box of cookies that self refills overnight, tasting better and better any time you eat them, thanks!
Thanks for your inspiring words
!
Can you similar for table.add for multiple columns?
Thankyou very much ❤
Always welcome
wow chandeep so easily you describe and keep engage me all the time ... I didn't get bore for a single second and keep continue for more 20 minutes. hats off dear .. please share you power query journey. as you shared dax 5 books. for power query which course and books you are following
Thank you so much 😀
I'll make a video on Power Query resources too!
If you can figure out how to use Table.Transform to merge data from one column into another existing one (while leaving that other column as-is), I'd be interested to see how it's done. From what I can tell, you can only really work with each row value.
Yes correct. But there is another replacer function that can do it. I am going to do a video on it
@@GoodlyChandeep
Good idea! You probably have another way to do it, but I got it work like this:
= Table.ReplaceValue(T1,each _[Col3],each _[Col1] & _[Col3],Replacer.ReplaceText,{"Col3"})
One suggestion for the blog post, the link is to be directly to the topic instead of the front page. Thanks.
Golden!!
Thanks! 😊
Chandeep, thanks for the easy-to-relate examples and the clear walkthrough. In my scenario, I am trying to take values from another column and apply it to the current column - Say, I have a column called Role and another column called Role Corrections. If there is a value in Role Corrections, I want to take it and apply it to the Role column. When I tried TransformColumn function, I get an error about Field Access on Text, which I interpret to mean that the context for _ is the current column value, whereas I need the row value to get value for the other column - how do I do this? As of now, I added another column and then deleted the original Role column once the update is column and then rename the new column to Role. But it is quite long.
Question: at 14:36 when you add the comma to add more to the code, what does "nullable" (i.e., nullable function") mean..? i.e., What is the significance of that part of the code (null or nullable show up in a number of commands)
how did u trumped-up that, it is so cool
Chandeep, another outstanding video. This is the kind of advanced M programming that is hard to find, but so much needed. I have a question about the transformation in your first example, where you basically change the "Age" column to "TRUE" if the age is greater than 10. What if instead of changing the "Age" column, you wanted to change the "FavCol" column, but still based on the age in the "Age" column. So, if "Age" is > 10, then change "FavCol" to "Purple" else leave "FavCol" alone. I have this come up many times and I always have to create a new column, then delete the existing column and rename to new column. Thanks.
Hi, there is a drawback using Table.TransformColumns which is it cannot refer to another column but only the one the you defined.
, {
{“ColumnA”, /**here is only possible to refer to ColumnA**/ Date.From, type date}
}
Excellent explanation, thank you very much.
Do you have a demo that shows how to make the Append of several excel files when the column names are not the same in all files?
ua-cam.com/video/mZbD8aduIJU/v-deo.html
See this
This is great, I have a lot of functions similar to your examples in my dataflow, but have a few questions....
1. can these query fold or if I use this method will it break query folding?
2. In example two when your writing functions against two columns, if the first function breaks will this cause the second one to break? We can control for this error handling but just thought I'd ask.
3. MissingField.Ignore is a new one for me and I will be using it immediately.
Not sure about Query Folding.. didn't test for that.
Yes the query will break unless you've used Missing field.ignore
Hi Is it possible to use this function in a conditional form? Like from Your example: Table.TransformColumns(sometable,"DOJ", if [FavCol] = "Pink" or [FavCol] = "Red" then [DOJ]=FALSE else [DOJ])
Can we transform column using conditions and values from other column. Like Filling all the nulls in column B with the corresponding values of Column A.
Awesome Chandeep 🤩. I've just a short a question : We put the colomn names manually(Hardcoded), How can we make it dynamic ? Thank you so much
See this - ua-cam.com/video/1fn8fXYw6M4/v-deo.html
That was really interesting ,, really liked the last bit on accessing the binary files,
One thing, Date.From(_) , I've been using
Text.Combine(
List.Distinct(
List.Transform( [All Rows] [Dates], Text.From) ) ,", " )
and my initial inclination was to use Text.From(_) , which did not work, puzzled.
lastly guitar do you have? and like and play?
List.From(_) will work if you combine it with the "each" keyword. So something like this
= List.Transform(
AList,
each Text.From(_)
)
It's an old guitar my father bought me when I was in school. I play horribly sometimes :D
What are some other Super Functions which you recommend? Thanks
I have manual entry database,
Each row has its own data type.. How can we do make changes row level data in a single column..
Help me on that.... Thank you
Hello , can you please help me in BOM explosion using DAX?
Hi Chandeep, I have a question How to change value in other column Based on a condition of current column..
👍👍👍👍👍
💯👍
Fabulous! I cannot believe I wasted a year thinking the Table.TransformColumns function was extremely difficult to use writing M code. Thank you so much Chandeep!
Chandeep : I have a single column with 4 rows. I need to pick only row 1and 2 in one column and row 3 and 4 in another column. The 4 rows are Origiin,FHR,Destination,SC. Thanks for your help
Paste this query in the advanced editor of a blank query and take a look at the steps. Hopefully this should help.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col = _t]),
#"2ColTables" = Table.Transpose(Table.FromList(List.Split(Source[Col], 2), Splitter.SplitByNothing())),
#"Added Custom" = Table.AddColumn(#"2ColTables", "Custom", each Table.FromColumns(Record.ToList(_))),
#"Removed Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", Table.ColumnNames(#"Removed Columns"[Custom]{0}))
in
#"Expanded Custom"
There are situations where we have to do some transformation on a specific column by creating a custom column.
Now, If we are using Table.TransformColumns and our transformations are dependent on other columns then this function falls into an error as soon as we refer to other columns. I think this function doesn't support these transformations that are dependent on other columns. I would like to know if there is a way to transform without creating a custom Column.
Sir,
Please Upload some more videos on power query
Will Do!
Hello goodly, quick question if I buy your dax course, will i get both the old and the updated one or just the most recent ?
Hi Anthony,
You'll get access to the new one too as soon as it is out!
@@GoodlyChandeep thanks 😊
Wow
Thanks!
Great explanation! Just a question: is it possible when you transform column a refer to column b? I mean: {“a”, each if [
_]
Unfortunately not! I don't think you can refer to a different column.
@@GoodlyChandeep Not directly with TrasnformColuns but it´s possible withTransformRows:
= Table.FromRecords(
Table.TransformRows(
TransCol,
(r)=>Record.TransformFields(r,
{"a", each if
r[b]="13205"
then "ES" else _})
))
Thanks so much for all the effort with your videos and tutorials !!!!! :-)
Of course I´m not the ownwer of the answer I found in stackoverflow here´s bellow the link .. :-)
Good one
I have a query for u how can I reach out to u?
респект 🚩
Hi how to have training session 1-1 with you
What if I want to bring the name of the column I'm going to perform the transformation on automatically? As it is being created automatically on a list of columns....I mean pick the first column from that list, then the second etc...but without having to name it?
Watch the advanced solution in this video - ua-cam.com/video/t_PDveh-3Fg/v-deo.html
This is very cool, but can you do the same for Table.ReplaceValue? I have column where I need to replace multiple characters but end up repeating Table.ReplaceValue several times.
Let me try to make a video on this!
Hi. Question here pls. How can i push one column value from one table to another column not of same name in other table. Using append in power query it just combines two tables with same column name. I would like to map which column value to go to into another column in anothet table. In simple words mapping of source column to destination column and then append
Can you send me some sample data and explain your problem clearly - goodly.wordpress@gmail.com
Hello chandeep sir,
I try to get data from folder in power bi but there is error showing Dataformaterror: table is not in expected format. All excel files are in 97-2003 excel workbook. Is there any way I can get all data from 97-2003 workbook to in power bi?
Thank you sir
I'm one of the employees in our company that are using the Power query in excel, however I'm not that familiar with the M Code. So lucky that I accidentally browse on your video. I was trying to look for M code that can Count number of Rows in certain column that has string or number. By the way, How can I activate those pop up list of M Code in excel power query? I noticed that every time you type in a certain M code, there was a pop up list of code that you can choose. If I can activate that in my Excel Power query, it will be helpful/easy to recognize the code. Hopefully you can provide me this Tip. Thank you so much.
Just update your Excel ! It should show up
hI. May I use Table.ExpandTableColumn with Table.TransformColumns to expand multiple combined queries in a previous step
See this, it is similar to what you're asking - ua-cam.com/video/t_PDveh-3Fg/v-deo.html
@@GoodlyChandeep Hi. Thanks for your replay, but I have tables no lists. I have been able to combine two or more queries in a single step with the following instruction : Table.AddColumn(PreviousStep, "NewColumn", each Table.NestedJoin(Table.NestedJoin (PreviousColumnName, {"ColumnID "}, QueryName1, {"ColumnID"}, "NewColumn1", JoinKind.LeftOuter), {" ColumnID "}, QueryName2, {" ColumnID "}, " NewColumn2", JoinKind.LeftOuter). The combination and expansion depends on the number of the month, the previous instruction is for the months of January and February and it works for me for more months.
I am missing is to be able in a single step to expand the columns contained in QueryName1 and QueryName2 that are inside the columns "NewColumn1" and "NewColumn2" as tables. Could you help me or tell me how to do it. Thanks
Continue in new power query function