Hi Celia. Great way to start the new year! An awesome and useful lesson. A variation I employed, instead of the List.Select() construct you used to isolate the last 3 column names for the OldNames step, I used List.LastN as follows, = List.LastN(Table.ColumnNames(Base), 3). Assuming you are always after the last 3 column names, then this construct eliminates the need to trap for the old column names with Text.Contains() or the method you used in example 2 by changing the data type and filtering out the errors. Using List.LastN() works for both examples, regardless of the starting column names. List.Zip() is a great find. Thanks for demonstrating and providing the inspiration to learn something new. Looking forward to more in 2022 :)) Thumbs up!!
Happy New Year, Wayne! Thank you, for your feedback and sharing your solution. The idea was to leave the solution the most flexible posible so that more people can use it. The columns to isolate can be in any position, not necessarily at the end of the list.
@@CeliaAlvesSolveExcel Thanks Celia and good point. List.LastN() works only if trapping for the last column names. Love to know multiple ways to solve. Thanks for your useful examples and solutions! Thumbs up!!
Thank you for introducing this approach to renaming the columns and to List.Zip function! I don't know what the refresh performance difference would be, but my approach involves unpivoting and pivoting back: (after the 'Base' step) UnpivotedOtherColumns = Table.UnpivotOtherColumns( Base, {"Brand Item", "On Hand", "Allocations", "Available to Promise", "On Order", "PR On Hand"}, "Attribute", "Value" ), AddedIndex = Table.AddIndexColumn(UnpivotedOtherColumns, "Index", 0, 1, Int64.Type), CalculatedModulo = Table.TransformColumns(AddedIndex, {{"Index", each Number.Mod(_, 3), type number}}), ReplacedValues = Table.ReplaceValue( CalculatedModulo, each [Index], each if [Index] = 0 then "Last Week Sale" else if [Index] = 1 then "Two Weeks Ago" else "Three Weeks Ago", Replacer.ReplaceValue,{"Index"} ), RemovedColumns = Table.RemoveColumns(ReplacedValues,{"Attribute"}), PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Index]), "Index", "Value", List.Sum) Most of these steps can be performed with UI except for "ReplacedValues" step. Again, no idea how this performs compared to your solution, but just wanted to share my 2 cents...
That's a clever one, Hachi! Thank you for sharing. Not involving a lot of coding is an advantage for many users. It would be interesting to test performance of one method against the other. Thanks!
Celia, this video was very informative: I like the way you dissected the problems into little pieces and troubleshoot the last part of the solution. will practice those 3 functions in M code. Thaks for sharing. Great examples
Happy New year to you as well. You really put great (maybe real) examples in your videos. This one appears to be a real-life example from a warehouse or something similar. I use your videos as a reference for data manipulation and overall ETL at work
Great video very helpful. I have a dumb ? What if you wanted to retain the column names as they are and have them change with the source? Not sure that is even possible. Just wondering as I have a vendor file i pull in every week. Changing the name helped so I can automate but wondering if I'll lose track of the time period
Hi, Jay. I am not sure I fully understand your question, but I have the feeling that this video might help: ruclips.net/video/xulaPRyp5EE/видео.htmlsi=bn9sDOSdlnnYA9cj
hi Celia@@CeliaAlvesSolveExcel , how about if we have dynamic file name? in case the file needs to have a date and I'm Get Data from Excel Workbook, do you have any solution?
Hi, Edwin. This lesson might have what your need. ruclips.net/user/livecUyx7CuXqQc?si=_5Tl6oylHqkAd1lm If the data is in OneDrive or SharePoint, there's more to it.
Another option is to relate the rename table (with old and new names) to the data table (with old names) in the data model if the new names are used in pivot tables
I didn't test this, but I don't think it would work. Relationships in the data model allow you to relate columns and cross information between items values existing in columns of different tables. The table with old and new names refer to the column headers - not the item values belonging to one column.
Hello Célia If you unpivot, your headers will belong to one column and it works. In my case my source don’t change but I just want to be able to display new names When needed
Celia you were absolutely right. It does not work (the results were wrong). My problem is that my headers are unpivoted in one column So I need to rename rows because if I pivot the process is so slow (hundred of thousands of rows)
Thanks for sharing. It is amazing. You explained it very well. I have another issue hope you can help. Issue: What would be the solution if Column name and posting of the column keep changing. Ex: Column A: Name, Column B: E-mail. Next time I get the data in this following format Column A: E-mail, Column B: Full Name This is the big issue I experienced. Do you have any solution for this
I have to change two months in the same Sheet Jan and Feb, How do I create Oldname and Newname for both with the List.Select function. If i do both separately the end result is not correct?
Excellent solution to a problem that pops up in so many scenarios.
Thank you, Gráinne! I hope it helps people out there. 😉
Hi Celia. Great way to start the new year! An awesome and useful lesson. A variation I employed, instead of the List.Select() construct you used to isolate the last 3 column names for the OldNames step, I used List.LastN as follows, = List.LastN(Table.ColumnNames(Base), 3). Assuming you are always after the last 3 column names, then this construct eliminates the need to trap for the old column names with Text.Contains() or the method you used in example 2 by changing the data type and filtering out the errors. Using List.LastN() works for both examples, regardless of the starting column names. List.Zip() is a great find. Thanks for demonstrating and providing the inspiration to learn something new. Looking forward to more in 2022 :)) Thumbs up!!
Happy New Year, Wayne!
Thank you, for your feedback and sharing your solution.
The idea was to leave the solution the most flexible posible so that more people can use it. The columns to isolate can be in any position, not necessarily at the end of the list.
@@CeliaAlvesSolveExcel Thanks Celia and good point. List.LastN() works only if trapping for the last column names. Love to know multiple ways to solve. Thanks for your useful examples and solutions! Thumbs up!!
@@wayneedmondson1065 you're very welcome! ;-)
Awesome, Celia 👏👏👏 You have explained so well. 😊
Thank you, Krishan. Glad that it helped.
Nice solution - it's always exciting to find useful functions in the Power Query documentation :-)
Indeed! It is like going hunting for treasures! 😃
Another great post! This is one of the most common problems I encounter. Nice, flexible solution.
Thank you for your feedback! I am glad that you find it helpful.
Thank you for introducing this approach to renaming the columns and to List.Zip function!
I don't know what the refresh performance difference would be, but my approach involves unpivoting and pivoting back:
(after the 'Base' step)
UnpivotedOtherColumns =
Table.UnpivotOtherColumns(
Base,
{"Brand Item", "On Hand", "Allocations", "Available to Promise", "On Order", "PR On Hand"},
"Attribute", "Value"
),
AddedIndex = Table.AddIndexColumn(UnpivotedOtherColumns, "Index", 0, 1, Int64.Type),
CalculatedModulo = Table.TransformColumns(AddedIndex, {{"Index", each Number.Mod(_, 3), type number}}),
ReplacedValues =
Table.ReplaceValue(
CalculatedModulo, each [Index], each
if [Index] = 0 then "Last Week Sale"
else if [Index] = 1 then "Two Weeks Ago"
else "Three Weeks Ago",
Replacer.ReplaceValue,{"Index"}
),
RemovedColumns = Table.RemoveColumns(ReplacedValues,{"Attribute"}),
PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Index]), "Index", "Value", List.Sum)
Most of these steps can be performed with UI except for "ReplacedValues" step. Again, no idea how this performs compared to your solution, but just wanted to share my 2 cents...
That's a clever one, Hachi! Thank you for sharing. Not involving a lot of coding is an advantage for many users. It would be interesting to test performance of one method against the other. Thanks!
Thank you very much Celia. Very informative presentation 👍👍🌹🌹
Thank you, Kebin. Glad that you found it valuable.
You are always giving very nice alternative solutions. Thank you.
Thank you, Gopi. :) hope it helps!
That was a great example. Thanks Celia
You're welcome, Matt!
Fantastic!!!!! It took me quite a while to find this answer. Thank you. Great content
My pleasure, Ross. Glad that you found it and that it was helpful. 🔥
Brilliant! Great techniques there - many thanks, Celia.
You're welcome, Ian! :) thank you for watching and offering feedback.
Thanks. You make power query look easy and interesting
That's great to hear! Keep up with learning. :)
Celia, this video was very informative: I like the way you dissected the problems into little pieces and troubleshoot the last part of the solution. will practice those 3 functions in M code. Thaks for sharing. Great examples
You're very welcome! Enjoy the exercises. Glad that it was helpful. :) Happy New Year!
Happy New year to you as well. You really put great (maybe real) examples in your videos. This one appears to be a real-life example from a warehouse or something similar. I use your videos as a reference for data manipulation and overall ETL at work
@@jazzista1967 yes, this example was inspired on a real business case.
Terrific Video!!!!
Awesome! Glad that you enjoyed it, Kenneth.
Great tips!
Thank you, Chahine. Glad that it helped.
Very informative. Thanks for sharing ❤
You're very welcome, Frank!
Great video, Celia,
Thank you for watching, Kenneth. I am glad it was useful to you.
Excellent
Great! Thank you for watching and leaving your feedback. :)
Thank you so much! You’re a life saver …🙏
Awesome! Glad that this helped.
This was very helpful. Thank you :)
Great video very helpful. I have a dumb ? What if you wanted to retain the column names as they are and have them change with the source? Not sure that is even possible. Just wondering as I have a vendor file i pull in every week. Changing the name helped so I can automate but wondering if I'll lose track of the time period
Hi, Jay. I am not sure I fully understand your question, but I have the feeling that this video might help: ruclips.net/video/xulaPRyp5EE/видео.htmlsi=bn9sDOSdlnnYA9cj
thankyou so muchhhhhhhh for the tutorial
You are very welcommmmme 😄
hi Celia@@CeliaAlvesSolveExcel , how about if we have dynamic file name? in case the file needs to have a date and I'm Get Data from Excel Workbook, do you have any solution?
Hi, Edwin. This lesson might have what your need.
ruclips.net/user/livecUyx7CuXqQc?si=_5Tl6oylHqkAd1lm
If the data is in OneDrive or SharePoint, there's more to it.
Another option is to relate the rename table (with old and new names) to the data table (with old names) in the data model if the new names are used in pivot tables
I didn't test this, but I don't think it would work. Relationships in the data model allow you to relate columns and cross information between items values existing in columns of different tables.
The table with old and new names refer to the column headers - not the item values belonging to one column.
Hello Célia
If you unpivot, your headers will belong to one column and it works. In my case my source don’t change but I just want to be able to display new names When needed
Celia you were absolutely right. It does not work (the results were wrong).
My problem is that my headers are unpivoted in one column So I need to rename rows because if I pivot the process is so slow (hundred of thousands of rows)
Beatiful
Glad that you appreciate the beauty in it.
Thanks for sharing. It is amazing. You explained it very well.
I have another issue hope you can help.
Issue:
What would be the solution if Column name and posting of the column keep changing.
Ex:
Column A: Name, Column B: E-mail.
Next time I get the data in this following format
Column A: E-mail, Column B: Full Name
This is the big issue I experienced. Do you have any solution for this
Hi, Vipin. Please check this other video and see if it helps: ruclips.net/video/xulaPRyp5EE/видео.html
I have to change two months in the same Sheet Jan and Feb, How do I create Oldname and Newname for both with the List.Select function. If i do both separately the end result is not correct?
What are the old names you have, and which new names you want to replace the old names?
@@CeliaAlvesSolveExcel The old names are Jan and Feb and I want to replace these with MTD - Thank you!
@@alanbotten357 do you want to rename Jan as MTD and Feb as MTD as well?
@@CeliaAlvesSolveExcel Yes
@@CeliaAlvesSolveExcel Jan and Feb are part of a Heading e,g. Net PCR Actual for Agy REM Feb 2022 mtd , and, Validation Required at Jan 2022
Do I hear a Portuguese accent? 🙂
Yes. I can't hide it, he? (The "he" is Canadian.) 😁
@@CeliaAlvesSolveExcel Easy to spot, watching the video from the other side of the Atlantic. 🙂 This tutorial is great stuff BTW. Thanks.