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?
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.
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.
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.
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.
@@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.
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.
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
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.
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
@@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
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 ?
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.
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)....😭😭😭😭
hi you are not alone who does not supply the data to practice do you expect people to understand without practicing???????????????????????????????????????????supply THE DATA
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.
Great video. So much to catch up. But thanks for sharing.
I also love how you explain the variables and the inner/outer environment
Very well explained. Thanks.
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.
Very Helpful
You are the best power query teacher on youtube!
Nice Explanation.
Goal and Steps.
Great video and very helpful . very nice way to explain
Great video! well explained and easy to follow!!
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?
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.
@@ExceedLearning looking forward to see what you come up with :) 👍
Brillante ❤thank you
Awesome..🔥🔥
wow thanks so much!!
Awesome!
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.
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.
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.
@@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.
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.
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
Please zoom in because of font is small
How can we create single step for replacing multiple values for example correction of city names
Hi, You might add those steps in a nested let expression.
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.
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
@@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
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 ?
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.
I got lost. Sir, at what point did you load Table1? Please explain further I appreciate your help in advance.
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)....😭😭😭😭
@@caballero4321 same with me!! do you get the solutions yet?
hi
you are not alone who does not supply the data to practice do you expect people to understand without practicing???????????????????????????????????????????supply THE DATA