Using M to dynamically change column names in Power Query

Рет қаралды 13,072

Күн бұрын

Learn how to use M language to dynamically change column names in Power Query! This is used mainly to clean and transform (e.g. trim, uppercase, etc.) an unpredictable number of messy column names.
Link to the blog on this topic: exceed.hr/blog/using-m-to-dyn...
Check out our new Power Query fundamentals course!
exceed.hr/en/academy/powerque...
#powerquery #powerbi #etltools #data #dataanalysis

Пікірлер: 35

@controlsgirl Жыл бұрын

Another great application walk through! I love how you split the video to show the smaller table and how to transform its contents into lists.

@gospelmoto2833 Жыл бұрын

Great video. So much to catch up. But thanks for sharing.

@controlsgirl Жыл бұрын

I also love how you explain the variables and the inner/outer environment

@udayteja6595 10 ай бұрын

Very Helpful

@anatulyalmaimany Жыл бұрын

You are the best power query teacher on youtube!

@aatsw Жыл бұрын

Very well explained. Thanks.

@larmondoflairallen4705 3 жыл бұрын

I am blown away! You covered several situations that have always bugged me, things I knew were possible but I didn't know how to write the code for.

@umeshkjadhav 3 жыл бұрын

Nice Explanation. Goal and Steps.

@mariaalcala5159 3 жыл бұрын

Great video! well explained and easy to follow!!

@amitk1208 2 жыл бұрын

Great video and very helpful . very nice way to explain

@zahir585 5 ай бұрын

Brillante ❤thank you

@gopichand5017 3 жыл бұрын

Awesome..🔥🔥

@zaydarendse2812 3 жыл бұрын

wow thanks so much!!

@ddp2049 6 ай бұрын

i still don't get how to create dynamic columns so when the data comes in from the file it is automatically updated. it was a great video. I just stuck.

@user-bh2lg4xw5n 2 жыл бұрын

Awesome!

@extremeautomationwithajayp2131 4 ай бұрын

hi there. You know so much about M code. Congratulations for this. I want to ask you one thing which is bothering me so much. In table add column function, i am trying to create a code using list generate and i like to join two columns and this to be continued till i am not done with all columns. I have a challenge in using this table add column function , i want to create a new header everytime it joins the two columns. i see the new column which we write in this function as a name, cannot be a variable. so as a result, it has the same column name and not allowing to join the third and fourth column. Do you have any idea how we can make new column name dynamic so table add column fx should keep creating the new columns without duplicate error

@SamehRSameh 2 жыл бұрын

Please zoom in because of font is small

@malchicken 3 жыл бұрын

Great video, well done. A possibility for another video segment: the different ways to stitch two lists together. I found most interesting the step of the custom column combining the two columns as lists into a combined list. It did make me wonder though, what are the different ways to combine lists together, like I’ve heard of a ‘zip’ function that can alternate values, but this is doing the same thing if those lists were expanded to rows?

@ExceedLearning 3 жыл бұрын

Thank you for your idea, Hendrick! There is more than one possibility of combining lists in Power Query. One of them is the List.Zip() function that takes lists as arguments and returns a list of lists combining items at the same position.

@malchicken 3 жыл бұрын

@@ExceedLearning looking forward to see what you come up with :) 👍

@TY-zl1vw 3 жыл бұрын

Thank you for the video. I'm really new to the M language, this video really show there are concepts I need to pick up, for the whole Power Query in the matter of speaking. I found this video when seeking a possible way of automictically adding an attribute column using table's own name. I have query that already combined several tables scrapped from website. The table name would have something like '2017/2018', '2018/2019'... but content of each table themselves only include months, not the years. In the output I really want something like Jul 2017, Aug 2017....Jan 2018... Dec 2018... for further analysis. I did it manually so far, since that sample was only about 4 years worth individual months. But couldn't help wondering if there is a automatic way of doing it when data size gets large. I hope I'm making sense.

@ExceedLearning 3 жыл бұрын

Hi, There is currently no way of referencing the table name inside of the query/table. To automatically add a year, there must be information at the website (or at least in the URL string) that points you to the year observed, which you could later use to add a custom column containing the year.

@TY-zl1vw 3 жыл бұрын

Hi, Thanks for the reply. Yes, checking the website where tables come from, it’s possible to tell manually. I can load each tables into separate sheets using Power Query(so sheet names would reflect the table names). Then, with further steps, I can combine them into a summary sheet. I was just hoping a single query can do the job though.

@ExceedLearning 3 жыл бұрын

@@TY-zl1vw Yes, it is possible to process the data of a similar structure in a single query. This can be achieved by creating a custom M function and using it upon the data. You can check our newest video to find out more about custom M functions.

@MrDhunpagla 3 жыл бұрын

Hi, I am facing expression error with the last column name in a file in which additional columns added recently. Whenever I refreshing the file in power query getting error since the column size increased by one. Please suggest how I can fix it ?

@ExceedLearning 3 жыл бұрын

Hi, we have tested this solution and it should work regardless of the number of columns in the query. Be sure to check other steps you added since they might raise an error.

@nehakinitya57 3 жыл бұрын

How can we create single step for replacing multiple values for example correction of city names

@ExceedLearning 3 жыл бұрын

Hi, You might add those steps in a nested let expression.

@SpiritualMotto 3 жыл бұрын

I need help on changing column name on daily to filter, when i first did my filter on my source column name is [xxx 25/10/2020] and filtered with "5" next day my column name is changed to [xxx 26/10/2020] when i refresh, it shows error like column is not found. Note: i dont want it to rename, i need source header as it is in output as well. I tried using table.columnnames(source){1} to filter based on column index instead of static column name as [xxx 25/10/2020] however, after refresh, it shows table is empty. There is no error but data is not getting populated. I need support to fix this.

@ExceedLearning 3 жыл бұрын

Hi, I can think of 2 possible solutions which may help you: 1. Use unpivot to get column name in the Attribute column and values in the Value column. Then, filter the Value column and pivot the Attribute column with the values from the Value column. 2. Use Table.ColumnNames(Source) to get the column name of the selected column (in this case, the column you want to filter must always be the second column since you are accessing it with {1} (row index at PQ starts with 0). After this, filter the selected column with the help of Record.FieldName() function. The M code would be: ColName = Table.ColumnNames(Source){2}, Filter = Table.SelectRows( Source, each Record.Field( _ , ColName ) = "5" ) in Filter

@SpiritualMotto 3 жыл бұрын

@@ExceedLearning You are Proved that your Mastered. You got new subscriber today. Its works very well. Also make video about this. Because i could find it across all platform

@caballero4321 2 жыл бұрын

I got lost. Sir, at what point did you load Table1? Please explain further I appreciate your help in advance.

@caballero4321 2 жыл бұрын

Well I did load a copy of my original table to use my new function but got an error as "Query expression error. We expected a Rename operations value. Details (List)....😭😭😭😭

@amenurasyid2849 2 жыл бұрын

@@caballero4321 same with me!! do you get the solutions yet?

@fadilyassin4597 Жыл бұрын

hi you are not alone who does not supply the data to practice do you expect people to understand without practicing???????????????????????????????????????????supply THE DATA