Table.TransformColumns in Power Query | Super Function ⚡

Подписаться 89 тыс.

Просмотров 55 тыс.

50% 1

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 RU-vid Channel Below
ru-vid.com/show-UCGQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

Наука

Опубликовано:

1 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 157

@GoodlyChandeep 7 месяцев назад

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

@entertainmentgalaxy971 2 года назад

The way you explained function is splendid. i would love to look forward to learn more functions with same explanation. Thanks for sharing.

@Tito_1982 3 месяца назад

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!

@AHMEDALDAFAAE1 Год назад

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

@jimmy3862 2 года назад

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. :)

@GoodlyChandeep 2 года назад

Thanks for your beautiful words Jimmy! 😍

@jmlipari 7 месяцев назад

Crack, te pasaste ameo. . With this superfast video you put in clear very power query concepts. Congrats!

@mightydrew1970 4 месяца назад

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.

@matthewpage4706 2 года назад

Really helpful. thank you. Nice to be able to compress the number of steps showing

@syedaneesdurez7197 2 года назад

Another mind blowing video to watch. Great tips

@mohamednour4874 2 года назад

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

@GoodlyChandeep 2 года назад

Great to hear! I'll try to make a video on Query Diagnostics.

@user-ji5uk8tq8u 7 месяцев назад

Thank you so much! Great Tutorials! You are awesome!

@sbn4862 2 года назад

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

@venkatareddykummitha5863 2 года назад

Thanks for valuable information.

@hamvasq 11 месяцев назад

Thank you for the great video! Exactly what I was looking for.

@matthewpage4706 Год назад

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

@fajnefajne4339 2 года назад

Excellent lesson. Thank you very much. :-)

@martyc5674 2 года назад

Brilliant Chandeep, thanks so much.

@viveksharma4193 2 года назад

Cool learning new concepts in pq day by by your videos 👌

@telclissonperes Год назад

Thank you so much for your availability!

@kennethstephani692 Месяц назад

Great video, as always!

@egbertdejong6377 2 года назад

Very useful - thanks!

@sbn4862 2 года назад

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 🙏🙏🙏

@BrainyBrunetteBarbie 2 года назад

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

@GoodlyChandeep 2 года назад

Thank you for your nice words J. S. B.! 💚

@jorstube 2 года назад

I can spend all day watching the applications of the functions you explain, very good material, thanks

@GoodlyChandeep 2 года назад

Glad you like them!

@wiadropiachu4328 2 года назад

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!

@GoodlyChandeep 2 года назад

Glad you found it interesting! 💚

@GosCee 2 года назад

Thank you very much, Chandeep. This explanation of the syntax was extremely helpful. Well done!

@GoodlyChandeep 2 года назад

Glad it was helpful!

@eslamfahmy87 3 дня назад

Awsome as usual good job

@smartwork4768 2 года назад

Great…got the amazing insights

@rrestituti Год назад

Amazing. Thank you for teaching in a so Very easy way.

@GoodlyChandeep Год назад

Glad you like it!

@patrickharilantoraherinjat2994 2 года назад

A big thumbs up. Thank you bro!

@tiwarirr 2 года назад

Simply Gold!!!

@GoodlyChandeep 2 года назад

Thank you! 💚

@orleanCosta Год назад

It was amazing! Thank you for shared.

@GoodlyChandeep Год назад

Glad you like this Orlean!

@keagankemp6275 2 года назад

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.

@GoodlyChandeep 2 года назад

Thanks Brother!

@sue_bayes 2 года назад

Brilliant, thank you

@cindyc6703 Год назад

I love it and give a good explanation about the M function. Thank you very much 👍👍👍👍👍👍

@GoodlyChandeep Год назад

Glad you liked it!

@rickyho152 Год назад

I am very appreciate your video!!! Professonal example and mind set!!!

@GoodlyChandeep Год назад

Glad you like it!

@RaviGupta-mo1nf 2 года назад

Thanks Chandeep. Clear & Interesting ...as always!!

@GoodlyChandeep 2 года назад

So nice of you

@KumarAnalytic 7 месяцев назад

Amazing sir😊

@KirillBezzubkine 2 года назад

Thx man. You are awesome

@wayneedmondson1065 2 года назад

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

@GoodlyChandeep 2 года назад

Frankly, I didn't check the query performance. I am going to be doing a video soon on Query Diagnostics, that should help :)

@karimallahwala7022 2 года назад

Very Gold explained.Thank very much, so helpful

@GoodlyChandeep 2 года назад

Glad you like it! 😊

@dirkstaszak4838 2 года назад

Very cool. Thanks for sharing 👍

@GoodlyChandeep 2 года назад

Thanks for watching!

@user-lm5wb8vi1x 6 месяцев назад

Finished watching

@Pavel64reg Год назад

many thanks🙏

@StefanoVerugi Год назад

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!

@GoodlyChandeep Год назад

Thanks for your inspiring words !

@isidrebague3659 2 года назад

Great, great and great. You make it look easy, even though it's not. Thanks for sharing Chandeep !

@GoodlyChandeep 2 года назад

Thanks a lot 😊

@gennarocimmino Год назад

Congratulations! You are really good!

@GoodlyChandeep Год назад

Thank you so much 😀

@juliane1mota Год назад

Keep helping us with great Power Query tricks.

@GoodlyChandeep Год назад

Stay Tuned 😊

@rajeshbadola4728 2 года назад

How could you think out of box !!! ??? Just great, what else could be said. Please make some more vedieos on power query.

@zdzislawkes Год назад

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?

@sands7779 Год назад

Good teacher using first principles.

@GoodlyChandeep Год назад

Glad you think so!

@haboobeeroo5444 11 месяцев назад

obrigado!

@navisalomi Год назад

The last part was the most awesome 👌

@GoodlyChandeep Год назад

Thanks !

@fadilyassin4597 Год назад

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

@MartinKuek 2 года назад

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.

@GoodlyChandeep 2 года назад

Thank you! Hope it Will be helpful 💚

@KumarAnalytic 7 месяцев назад

Last wala 10 seconds bahut surprising tha sach me😮😊😊

@dedyhidayat163 Год назад

Good learning & trick. How to transform column by filter the data from other table

@KgasS Год назад

One suggestion for the blog post, the link is to be directly to the topic instead of the front page. Thanks.

@christophertreadwell4726 11 месяцев назад

Thanks!

@GoodlyChandeep 11 месяцев назад

Thanks Chris, it means a lot :)

@kennethstephani692 2 года назад

Golden!!

@GoodlyChandeep 2 года назад

Thanks! 😊

@mirrrvelll5164 2 года назад

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

@abdulrehman56 2 года назад

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

@GoodlyChandeep 2 года назад

Thank you so much 😀 I'll make a video on Power Query resources too!

@Seftehandle Год назад

Absolute leader in m power query

@GoodlyChandeep Год назад

Thank you 💚

@KawanExcel 2 года назад

Thank you in advanced.... Please inform me, is your power query course cover such detail and advance topic like this, chandeep?

@iftikharsaeed Год назад

how did u trumped-up that, it is so cool

@gurjar5708 Год назад

Thankyou very much ❤

@GoodlyChandeep Год назад

Always welcome

@heishoi 3 месяца назад

👍👍👍👍👍

@rrrraaaacccc80 Год назад

💯👍

@mailsiraj Месяц назад

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.

@billgillaspey9036 Год назад

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)

@stevefaulkner692 10 месяцев назад

Can you similar for table.add for multiple columns?

@JSUG4219 Год назад

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.

@willianthalles Год назад

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} }

@bagnon Год назад

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.

@GoodlyChandeep Год назад

Yes correct. But there is another replacer function that can do it. I am going to do a video on it

@bagnon Год назад

@@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"})

@joaorataoo 2 года назад

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?

@GoodlyChandeep 2 года назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mZbD8aduIJU.html See this

@user-yo2nb4ry7i 2 года назад

респект 🚩

@kennethpaige7036 Год назад

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!

@arokiarajan1230 2 года назад

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

@nishantkumar9570 Год назад

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.

@radon8452 Год назад

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.

@davidlopez-fe2lb 2 года назад

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.

@GoodlyChandeep 2 года назад

Not sure about Query Folding.. didn't test for that. Yes the query will break unless you've used Missing field.ignore

@modernboutique1389 Год назад

Wow

@GoodlyChandeep Год назад

Thanks!

@williamarthur4801 2 года назад

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?

@GoodlyChandeep 2 года назад

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

@KuldeepSingh-nq1vi 8 месяцев назад

Hi Chandeep, I have a question How to change value in other column Based on a condition of current column..

@kamil4177 10 месяцев назад

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])

@jafarhussain6606 Год назад

Hello , can you please help me in BOM explosion using DAX?

@txreal2 10 месяцев назад

What are some other Super Functions which you recommend? Thanks

@vacilando86 Год назад

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

@GoodlyChandeep Год назад

See this - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-1fn8fXYw6M4.html

@tlsrinivasan 2 года назад

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

@GoodlyChandeep 2 года назад

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"

@ExcelWorkshop. Год назад

Sir, Please Upload some more videos on power query

@GoodlyChandeep Год назад

Will Do!

@sajadabdulcader5337 Год назад

Good one I have a query for u how can I reach out to u?

@Lyriks_ 2 года назад

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 ?

@GoodlyChandeep 2 года назад

Hi Anthony, You'll get access to the new one too as soon as it is out!

@Lyriks_ 2 года назад

@@GoodlyChandeep thanks 😊

@alessandrodaniel1541 2 года назад

Great explanation! Just a question: is it possible when you transform column a refer to column b? I mean: {“a”, each if [ _]

@GoodlyChandeep 2 года назад

Unfortunately not! I don't think you can refer to a different column.

@ginesc 2 года назад

@@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 .. :-)

@riahon 2 года назад

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.

@GoodlyChandeep 2 года назад

Let me try to make a video on this!

@abhijeetshetye8785 Год назад

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

@mayurt4628 10 месяцев назад

Hi how to have training session 1-1 with you

@Jujubeterraba 2 года назад

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?

@GoodlyChandeep 2 года назад

Watch the advanced solution in this video - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-t_PDveh-3Fg.html

@edsilnazareno1951 2 года назад

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.

@GoodlyChandeep 2 года назад

Just update your Excel ! It should show up

@naveen200848 2 года назад

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

@GoodlyChandeep 2 года назад

Can you send me some sample data and explain your problem clearly - goodly.wordpress@gmail.com

@jorgeluliogarciaarango1820 Год назад

hI. May I use Table.ExpandTableColumn with Table.TransformColumns to expand multiple combined queries in a previous step

@GoodlyChandeep Год назад

See this, it is similar to what you're asking - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-t_PDveh-3Fg.html

@jorgeluliogarciaarango1820 Год назад

@@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