1st Question - "Where did you learn your Excel skills?" Response - "From LGU" 2nd Question - Where? Response - "Leila Gharani University" 3rd Question - "Is it hard?" Response - "Yes ... very hard ... but the instructor is great"
OMG thank you! I I was struggling with this just last night and just abandoned my effort. I manually changed column headers in my data to get the append working. You are amazing, Excel is amazing and PQ is amazing! Your videos are doing a lot to foster people's love of excel so thank you for making them.
Ok! The most complicated formula I’ve ever seen in excel. I thought I was done with excel and DAX! Meanwhile this also depends on the fact that the column names not matching must be consistent and meaningful! Unlike the scattered column names my colleagues used to send to me. Well done Ma’am.
I’m desperately trying to teach myself excel in a night to get an amazing job- it’s a nightmare and you’re keeping me going right now. Thank you for your videos!!!
This is brilliant I shall be considering the Power Query Course to purchase at the end of the month with using some of my salary payment - thanks again
Thank you for another great video I was curious to see if I could create a simpler approach for this specific problem. So, in the interests of sharing... Rather than the very useful but complex formula at 12:30 = Table.TransformColumnNames(TSalary_Table, each List.Accumulate(Table.ToRecords(Mapping) ... etc etc etc Suggested formula = Table.RenameColumns(TSalary_Table, List.Zip({Mapping[Before],Mapping[After]}), MissingField.Ignore) Translation The second parameter of Table.RenameColumns is a list of lists, for example {{"NOMBRE", "Name"}, {"DEPARTMENTO", "Department"}, {"SALARIO", "Salary"}} So, how to turn the mapping table into a list of lists Any of these options will do the job List.Zip({Mapping[Before],Mapping[After]}), or Table.ToRows(Table.SelectColumns(Mapping,{"Before", "After"})), or Table.ToRows(Mapping) Add the extra parameter MissingField.Ignore to ignore issues with attempting to rename columns that don't exist in specific files
Sir, thank you for taking the time to write this comment. You simply gave answer to my whole problem with renaming multiple columns at once, I'm delighted! Huge thank you.
Hmmm, honestly this video is little overhead for me first time, so going to watch again, pausing and revising and I am sure I will get it! 😁But many thanks for this useful video, as always! 😊👍
Leila, I love your honesty. Now I do not feel bad for not catching this explanation the first time. Thank you for all that you do for us and for Microsoft Excel, Leila.
This example is very well explained and quite insightful. I'd like to highlight one issue when utilizing the Text.Replace function. The outcomes might be unexpected, as it could inadvertently replace segments of text within a specific column. This complexity increases further when dealing with source files that aren't in the .xlsx format, such as .CSV files
Excellent video, this lesson is quiet advanced because it has some functions not easy to explain at first besides List.Accumulate (each, underscore, the record function), but you covered perfectly all this topícs in your Power Query course. If somebody reads this comment, take Leila's course, it's worth and mind blowing.
same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data.what need to do to catch both sheet data
This is a big challenge for me. Thanks for the tutorial. It's given me a good headstart at resolving this challenge of combining data of same sizes but different field labels
I'm happy that I found you ... thanks for sharing your knowledge... means a lot for me for doing my fundamental analysis...your doing really a great work ...
Thank you for the video, it really helped me understand some of the tools available. I'm still not sure how to solve my specific situation though. If you, or anyone reading this, knows how, please let me know! -I receive multiple excel workbooks from different clients every day. -I want to combine them into one sheet in one workbook. -They are NOT formatted as excel tables. -All of them merge and center the first row above their column "headers", and add text, sometimes different text between the clients. -I do not need this first row at all, but it must be there when the client sends it. -I do not want to manually remove the top row from each workbook. -Some of the "headers" match, some are different but mean the same thing, and some are unique and need to be incorporated, with blank spaces for the worksheets that don't have data in that column. -Some worksheets have a merged and color-filled bottom row, sometimes with text, sometimes the bottom two rows are merged (individually, not with each other). -I do not need any of the information in these merged cells. -Two of the columns are "DATE" and "TIME", and I need the entire end product to be sorted in ascending order for both. -The sheet names are always inconsistent.
@@LeilaGharani I tried and I found something wrong with it. My two sources have "POLICY" and "POLICY_NO" both mapped to "POLICY NO". Query reflects only data from "POLICY_NO" and column head is changed to "POLICY NO NO". Could you please help on this or suggest where I can learn from?
Hi Leila, I love all the content from your channel and also how didactically your explain everything. :) I started to use Power Query recently and this issue of having different headers hunted me from the beginning. To solve that I adopted a different approach though, from the one you explained in this video. I load the files with different language only as connections, duplicate the columns, and rename them to the English terms. Afterwards, I am able to append all the sheets. I think that the 2 approaches work well. The choice for the right method is probably personnel and depends on how many columns, how many sheets, and user familiarity with Power Query. Elaine
I just append all and then merge and rename the columns. If a new column appears on refresh I can go edit the merge step. But I'll try this method soon because I'd rather learn it before I actually NEED it. Then I'll eagerly await the appropriate messy data opportunity.
This is very complicated formula to transfer the name format. I hope there is a better and simplier method to fix the name format difference. Thank you!
Hi! I’ve found an more direct function which is list.zip() ! It needs only {reftoconversion_table[before], reftoconversion_table[after]} and the final argument for missing elements is also very nice! Thank you Leila 😉
same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data .what need to do to catch both sheet data?
Thanks for inspiring videos. I was stuggling to get my head around similar kind of problem. After some trial and error found alternative option to reach similar outcome with merge function. On the "Transform Sample File", when the initial table is open, first transpose whole table. Now the original column headers are in Column1. Then the Column1 is merged with file that contains mapping information (columns OldName and NewName). After merging NewName is expanded to visible and moved to lefthand side to first column. Then Column1 is deleted, table is transposed back to original layout and headers promoted. Then just append all files based on the sample file :)
This is impressive, but honestly I feel like a macro could accomplish this with less effort. Maybe it's a lazy way of thinking from my end, but it's not just powerquery that is used but some very advanced pq functions. Maybe I just need to throw myself into work that is best done in powerquery so it becomes more second nature to me and then I can iterate to something as advanced as this but this just seems so complex. Definitely impressive, but seems a bit of over engineering. Thank you for opening our eyes to the options out there though.
I hope you can help :) If anyone can ---- it's you. :) I've watched all your videos now countless times. I've posted this question in stackable, but you said in your class that UA-cam was your discord so here is my question: What I'm trying to create is a series that repeats every 8 rows, that spills down until the calculated value equals a referenced cell. // **L15** This is a **$** amount. **My Example is as follows.** I need the top starting value, which is in row F, to calculate three variables as it spills down each row. **Cell F17 first calculation** and equals the dollar created from the following variables. // B17= % such as 34% // L17= Dollar amount used (which is dynamic via a data validator) // D17= Number of people to multiply As each row drops in the series... // B17 becomes smaller, 34%,21%,13%,8%,5%,3%,2%,1% // D17 grows larger by ^2= 10,20, 40, 80,100... The idea is for the top number and each next variable to use the sum 8 series range: // `D17:D24` which equals 2,550 Yes, I have created a much simpler process to ascertain what the value SHOULD be by simply using the following formula: =SUM(D$17:D$24)*(B17*$L$17) However, why I had to change my approach is listed below. The series or an array formula series, that repeats every 8 rows, however, as the repeating pattern occurs in column B B17: 34%,21%,13%,8%,5%,3%,2%,1% The growth patterer compounds in // F17 = the amount of compounded Value // D17 = the ^2 amount used by an expositional of 2 // L17 = the increase of $ amount used each cycle. // L15 = Target Number for the series to stop** The reason for this elaborate formulation is to predict changes via several input variables to find the STOP CONDITION dependant on F column is // >= L15 To achieve the correct top-level calculation my function is `={B17*$L$17}*{sum(offset(D17,0,0,8))}` This adds all the future cells for an 8 range series then multiplies them, providing the correct calculation. THE PROBLEM IS ONCE it SPILLS, the offset calculates the numbers in the NEXT repeated series, making the only correct calculation - the first row. I tried `={B17*$L$17}*{sum(offset(D17,0,0,$8))}`` But I can't lock an offset height value. I did find where someone wrote about using a // row(F1)-1 but I'm not sure how to implement that. THE PROBLEM CONTINUES Once I solve this spill issue The STOP CONDITION may happen at several different rows depending on the referenced cells, that contain the target number for the multiple inputs and variables that adust growth. Meaning that once the Value in the initial Value column **F** hits the target of the referenced cell // L15 = dollar amount The pattern stops. I hope this is understandable. Thanks for the help if anyone out there sees this. Thank you.
Hi Leila, your tutorials are amazing ! Thank you Could you please do a tutorial on the underscore operator and the "each" keyword? I find these, especially the underscore, very confusing as I can't figure out which one to use. Thanks
Hi Leila, thank you for the very useful videos. I have a question. Is it possible to, instead of combining and transforming multiple files in a folder, transform first then combine the resulting tables? How to do it? Thanks.
As usual, a great video and great explanations. It'll take me time, though, to fully understand. But, even if I'm also working with your Udemy courses, these videos are a great help.
Dear Leila I love your video tutorials, Although, this time, I don't seem to understand the basis of your video. I understood that to create a mapping table I must first manually list the headers ("BEFORE" column) of all the tables I am working on and then create a second column ("AFTER") with the names I want to transform the "Before" Headers column into. How can I handle this procedure if my tables are many? Won't I waste too much time collecting all the headers? What did I miss/not understand about your instructions? Thank you if you may reply
Wow, your mastery of Excel and Power Query is outstanding! When you said, "...I understand this is a little geeky...." My thought was, "hmm., this is more like nerdy. Did she ever spend any time playing with other kids? She should try to solve the Riemann Hypothesis! I'm sure she would be able to solve it." :)
@@LeilaGharani Thank you for sharing that! My brain was spinning while trying to understand that function. It also made me wonder about my mental capacity to learn, and I was wishing for better weather to ride my motorcycle instead. It’s encouraging to hear that even you struggled with it. I will persevere and watch this video a few more times so that it sticks to my brain. I have no doubt about its usefulness. Thank you sharing your knowledge, and have a good day! :)
This again highlights just how weak the PQ development environment is - it is more or less impossible to debug List.Accumulate operations. How did you do it Leila?
Wonderful video Leila. It's difficult to digest. One really need to practice this functions many times before getting a dab hand on it. Thanks. Can you also do a video on excel 365 solution to this different header problem?
Very well explained. However, we would like to follow your tutorial step by step, as you have explained that may be easier if you can provide a link to download excel files under reference. Thanks.
Hi, thank you very much for this video! I have a follow up question. What if the other files have different number of columns and values in different formats? For example in my case, "May- 2024 Sales" file contains all columns from previous months but also additional columns that were added to it and wouldn't be present in say "Apr-2024 Sales" file. Ideally I want all older files to display Null in this new column but I am not sure how to do it. I've been stuck on this problem for a while, I'll really appreciate it if you can help me out.
Love your videos. Simple to follow & real scenarios. So if I have a text/ csv file, downloaded from the web (with garbage data as well) and I want output in a certain format in excel, what would be better - Power Query or Macro? Thxs
Thank you for sharing! I have followed every step but it still not working in my case, i am not sure if it is because my data file is not a table. There are too many files that i cannot set them to table format one by one.
Power Query is a new animal I've been diving into at work lately. This is fascinating. I am already amazed at what you can do with just excel but this really has me ready to dive into the course and learn Power Query. Is Power Pivot similar to this or is that a different process all together?
This is going to save me 5 millions months of arranging data files from our regions. Plus I need not to work my system generated report any longer. Just download into a folder and that's it.
This is very useful. Now add this challenge. The data can be different too. Like for example (Elbow, Elb, Elb., ELBOW, EBOW). Can we use list.accumulate to modify the data under a specific column as well?
That's easier to do. You could use Merge with a transformation table to map. If you need to map multiple words in the same field, then List.Accumulate will do the job.
It is a very useful thing but in a case when you have "almost perfect" environment of data, or very close to that. But reality is of course different, where you are dealing with a jungle of data, which gives hard time to transform properly.
This seemed to be perfect for what I was looking for...multiple spreadsheets with inconsistent columns. Unfortunately I was not able to repeat the functionality in the spreadsheets I have. For starters, when importing the data, it is not finding the table I defined as a parameter. That is TSalary in your example. In addition, when I import the actual data, Excel is ignoring the header in the source document and creating new header names, Column1, Column 2, etc.
This video is amazing, thank you so much for sharing Leila! I have taken your course on mastering power query but i must have missed the part about List.Accumulate so i will need to go back through the course again but what great idea!
Amazing Explanation Leila , i tried to reproduce the above example and it worked :) with a slight hiccup between the Column1 and Column11 as my before is just a list of Columns with a number (Column1, Column2 etc) for some reason the Current and Sate in the text.replace function recognizes Column11 as Column1 and therefore applies the after of Column1 instead of Column11 , is there a way to force the Text.Replace to look for the exact string and not an approximate :) ? Much obliged once again for the Amazing Video
Thanks for this video Leila ! Could you please help me with the following challenge ? Columns: JAN 2021, FEB 2021, MAR 2021, APRIL 2021….. DEC 2021 Based on current month (DEC 2021), I want to calculate the average of previous three months (SEP, OCT, NOV) for each row. I am unable to figure out how this can be done. Please help. Thanks !
Is it possible to manipulate data tables with headers and sub headers? For example let’s say you have a list of 10 items in column A. Then columns B and C are sub headers for units sold and $ sold, respectively, for Store #1, the header. Then you have columns D and E as units sold and $ sold (sub headers) for Store #2 (header), and repeat for as many stores as you have. Is it possible to use this type of data table
Great presentation, as usual! How to append from several tables (not files) loaded in my workbook with different column headings and numbers? Very much appreciate your response. Good Luck!
Thank you for this! I have multiple tabs (worksheets) within a single workbook. I'm getting confused because it adds a "Promoted Headers" step. Anyone know how to handle that?
This was such a handy tip. One issue I ran into though is similar column names. I decided to remap a series of columns that started with "Column" and the Text.Replace ended up replacing part of other column names. (Column1,Column2,Column3,...Column10,Column11,Column13) A few quick remedies but not necessarily ideal 1. add entries at the bottom of the mapping table for the weird column names 2. sort the mapping table in reverse order It's almost like setting up a switch statement so you really need to pay attention to when the condition is triggered. Is there something in List.Accumulate that joins instead of replaces? or replaces whole words?
Hey, whenever i start typing the formula I cannot see the drop down of the formulas which is used during the video. Can you help me out with the same??
Thanks for this video. It took me some time to understand the function...But finally I did. However, I faced this problem. I wanted to change my headers from ...Date1, Date2, Date3... to DATE. I got this result = DATE1, DATE2, DATE3. The function Text.Replace changed only Date in the BEFORE . As far as I am concerned, Date was a partial text in the AFTER column. Any thoughts as to how PQ can make this change. In Excel we would use search & replace using match entire cell contents. Do we have such a feature?
Leila I would like to see the solution using List.Accumulate. There is no Text.ReplaceValue function to take care of substrings. I solved the headers replacements using 2 other methods & am happy by doing so I have a better understanding of PQ.
Excellent video! I loved it!!! But, I have a question: What if the data is coming as range (not as table) and is having 2 unwanted rows on top of every single file?
I had the same problem. I was able to get it to work by adding a line before the Table.TransformColumnNames. This is my M script: let Source = Excel.Workbook(#"Parameter (3)", null, true), Navigation = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], #"Removed top rows" = Table.Skip(Navigation, 2), #"Promoted headers" = Table.PromoteHeaders(#"Removed top rows", [PromoteAllScalars = true]), Custom = #"Promoted headers", TransformColumnHeader = Table.TransformColumnNames(Custom, each List.Accumulate(Table.ToRecords(COLUMN_MAPPING), _ , (state, current)=> Text.Replace(state,current[BEFORE],current[AFTER]))) in TransformColumnHeader
@@chantellevandalen5806 Hey, I am facing a problem that whenever I start typing the formula as mentioned in the video. I'm not able to see the dropdown formulas to use. Can you help me out with the same?
Good evening, I'd like to ask 2 questions: 1) once we get the Power Query processing in relation to the settings we created and create a "table", is there any way to automatically export it as an XLXS file so that it is no longer related to the Query? 2) Related topic: if we have a file consisting of multiple columns, can we provide with a "command" the saving of as many files as there are "row" fields of a given column? Thanks in advance
Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
Ok. I was completely LOST. God Bless your advanced IT skill
1st Question - "Where did you learn your Excel skills?"
Response - "From LGU"
2nd Question - Where?
Response - "Leila Gharani University"
3rd Question - "Is it hard?"
Response - "Yes ... very hard ... but the instructor is great"
OMG thank you! I I was struggling with this just last night and just abandoned my effort. I manually changed column headers in my data to get the append working. You are amazing, Excel is amazing and PQ is amazing! Your videos are doing a lot to foster people's love of excel so thank you for making them.
Excellent! This makes me happy. Thank you.
Concept of mapping is brilliant because people send all sorts of messed up data and to perform ETL operations this is game changer.
By far one of the best (if not the best) excel content creator on youtube. Simply amazing Leila
Wow, thank you!
Ok!
The most complicated formula I’ve ever seen in excel.
I thought I was done with excel and DAX!
Meanwhile this also depends on the fact that the column names not matching must be consistent and meaningful!
Unlike the scattered column names my colleagues used to send to me.
Well done Ma’am.
I’m desperately trying to teach myself excel in a night to get an amazing job- it’s a nightmare and you’re keeping me going right now. Thank you for your videos!!!
This is brilliant I shall be considering the Power Query Course to purchase at the end of the month with using some of my salary payment - thanks again
Thank you for another great video
I was curious to see if I could create a simpler approach for this specific problem. So, in the interests of sharing...
Rather than the very useful but complex formula at 12:30
= Table.TransformColumnNames(TSalary_Table, each List.Accumulate(Table.ToRecords(Mapping) ... etc etc etc
Suggested formula
= Table.RenameColumns(TSalary_Table,
List.Zip({Mapping[Before],Mapping[After]}),
MissingField.Ignore)
Translation
The second parameter of Table.RenameColumns is a list of lists, for example
{{"NOMBRE", "Name"}, {"DEPARTMENTO", "Department"}, {"SALARIO", "Salary"}}
So, how to turn the mapping table into a list of lists
Any of these options will do the job
List.Zip({Mapping[Before],Mapping[After]}),
or Table.ToRows(Table.SelectColumns(Mapping,{"Before", "After"})),
or Table.ToRows(Mapping)
Add the extra parameter MissingField.Ignore to ignore issues with attempting to rename columns that don't exist in specific files
Very elegant, thanks for sharing!
I couldn't get the solution from the video to work for my use case, but your did. Thanks for sharing!
Excellento!
MY FRIEND YOU HAVEW SAVED MY LIFE!! HEADS UP TO YOU FOR YOUR FORMULA SUGGGESTED TO LEILA, THIS ONE DID WORK FOR ME.!! BEST REGARDS!!
Sir, thank you for taking the time to write this comment. You simply gave answer to my whole problem with renaming multiple columns at once, I'm delighted!
Huge thank you.
Hmmm, honestly this video is little overhead for me first time, so going to watch again, pausing and revising and I am sure I will get it! 😁But many thanks for this useful video, as always! 😊👍
Hi Vijay. I fully understand. It took me a few weeks to understand how the list.accumulate function works 🤯
Leila, I love your honesty. Now I do not feel bad for not catching this explanation the first time. Thank you for all that you do for us and for Microsoft Excel, Leila.
This example is very well explained and quite insightful. I'd like to highlight one issue when utilizing the Text.Replace function. The outcomes might be unexpected, as it could inadvertently replace segments of text within a specific column. This complexity increases further when dealing with source files that aren't in the .xlsx format, such as .CSV files
Excellent video, this lesson is quiet advanced because it has some functions not easy to explain at first besides List.Accumulate (each, underscore, the record function), but you covered perfectly all this topícs in your Power Query course. If somebody reads this comment, take Leila's course, it's worth and mind blowing.
Thank you!
same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data.what need to do to catch both sheet data
This is a big challenge for me. Thanks for the tutorial. It's given me a good headstart at resolving this challenge of combining data of same sizes but different field labels
It works, But is going overhead.. Three Cheers to Leila, Well expalined, Many Thanks for sharing the knowledge
You're welcome!
I'm happy that I found you ... thanks for sharing your knowledge... means a lot for me for doing my fundamental analysis...your doing really a great work ...
Lovely!! you've just taken a load of stress off my table :) thanks for sharing!!
You are so welcome!
I've been using power query quite a lot but didn't know about this transformation. You're Awesome Leila.🤘
Awesome! Thank you!
I wish I could give thousands of thumbs up. I love it and will use it a lot
I am very excited really your teaching skills are another level
So nice of you
What a tutorial!!! Your videos are very effective and efficient for the MS users!!
Thank you for the video, it really helped me understand some of the tools available.
I'm still not sure how to solve my specific situation though. If you, or anyone reading this, knows how, please let me know!
-I receive multiple excel workbooks from different clients every day.
-I want to combine them into one sheet in one workbook.
-They are NOT formatted as excel tables.
-All of them merge and center the first row above their column "headers", and add text, sometimes different text between the clients.
-I do not need this first row at all, but it must be there when the client sends it.
-I do not want to manually remove the top row from each workbook.
-Some of the "headers" match, some are different but mean the same thing, and some are unique and need to be incorporated, with blank spaces for the worksheets that don't have data in that column.
-Some worksheets have a merged and color-filled bottom row, sometimes with text, sometimes the bottom two rows are merged (individually, not with each other).
-I do not need any of the information in these merged cells.
-Two of the columns are "DATE" and "TIME", and I need the entire end product to be sorted in ascending order for both.
-The sheet names are always inconsistent.
Thanks a lot, you have saved me 8 hrs of work that i do on a quarterly basis.
Glad it helped!
@@LeilaGharani I tried and I found something wrong with it. My two sources have "POLICY" and "POLICY_NO" both mapped to "POLICY NO". Query reflects only data from "POLICY_NO" and column head is changed to "POLICY NO NO". Could you please help on this or suggest where I can learn from?
Hi Leila, I love all the content from your channel and also how didactically your explain everything. :)
I started to use Power Query recently and this issue of having different headers hunted me from the beginning. To solve that I adopted a different approach though, from the one you explained in this video. I load the files with different language only as connections, duplicate the columns, and rename them to the English terms. Afterwards, I am able to append all the sheets. I think that the 2 approaches work well. The choice for the right method is probably personnel and depends on how many columns, how many sheets, and user familiarity with Power Query.
Elaine
I just append all and then merge and rename the columns. If a new column appears on refresh I can go edit the merge step. But I'll try this method soon because I'd rather learn it before I actually NEED it. Then I'll eagerly await the appropriate messy data opportunity.
I like watching this video at 2x speed. Easier to understand. 😆 great tip! Thank you for all your do!
Hi Leila, wonderful video. Can you tell me when to use List.transform and when to use list.accumulate and if one is superior to the other. Thank you.
Brilliant use of power query. Thank you!
Hi Leila. Nice to see a Power Query topic this week! Great challenge and solution. Thanks for sharing :)) Thumbs up!!
Thanks for watching Wayne! It's a bit of a jump from my previous Excel for Beginners video 😉
Great example. Reminded me to go back to the course content and re-watch now that I have a very similar use case. Thanks!
Thank you for your videos! I have learned so much! Keep them coming!
Excellent and strong !
One question : how activate the formula suggestion in PQ ??
Your videos are very helpful, thank you.
Please make a video explaining how to use this technique before expanding the table.
Hi Leila your vedio most useful to us....!!!! Keep updating more vedio of power query...!!!! Thank you so much for Dil se...... ❤️❤️❤️❤️❤️❤️
I learn a lot from you! Great trick to handle format issues
This is very complicated formula to transfer the name format. I hope there is a better and simplier method to fix the name format difference. Thank you!
Hi! I’ve found an more direct function which is list.zip() ! It needs only {reftoconversion_table[before], reftoconversion_table[after]} and the final argument for missing elements is also very nice! Thank you Leila 😉
Thanks for sharing, Antoine!
same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data .what need to do to catch both sheet data?
Amazing video, need to ask you question about slightly different issues not sure how to compute
Thanks for inspiring videos. I was stuggling to get my head around similar kind of problem. After some trial and error found alternative option to reach similar outcome with merge function. On the "Transform Sample File", when the initial table is open, first transpose whole table. Now the original column headers are in Column1. Then the Column1 is merged with file that contains mapping information (columns OldName and NewName). After merging NewName is expanded to visible and moved to lefthand side to first column. Then Column1 is deleted, table is transposed back to original layout and headers promoted. Then just append all files based on the sample file :)
Thanks - your approach is simpler nut Kudos to Leila on the List.Accumulate function approach
exactly what I'm looking for, thanks again Leila
Woah.. this is like magic.. thanks for the wonderful video on our daily battle with inconsistent data.. really helpful 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻
My pleasure 😊
This is impressive, but honestly I feel like a macro could accomplish this with less effort. Maybe it's a lazy way of thinking from my end, but it's not just powerquery that is used but some very advanced pq functions. Maybe I just need to throw myself into work that is best done in powerquery so it becomes more second nature to me and then I can iterate to something as advanced as this but this just seems so complex. Definitely impressive, but seems a bit of over engineering. Thank you for opening our eyes to the options out there though.
I hope you can help :) If anyone can ---- it's you. :)
I've watched all your videos now countless times. I've posted this question in stackable, but you said in your class that UA-cam was your discord so here is my question:
What I'm trying to create is a series that repeats every 8 rows, that spills down until the calculated value equals a referenced cell.
// **L15** This is a **$** amount.
**My Example is as follows.**
I need the top starting value, which is in row F, to calculate three variables as it spills down each row.
**Cell F17 first calculation** and equals the dollar created from the following variables.
// B17= % such as 34%
// L17= Dollar amount used (which is dynamic via a data validator)
// D17= Number of people to multiply
As each row drops in the series...
// B17 becomes smaller, 34%,21%,13%,8%,5%,3%,2%,1%
// D17 grows larger by ^2= 10,20, 40, 80,100...
The idea is for the top number and each next variable to use the sum 8 series range:
// `D17:D24` which equals 2,550
Yes, I have created a much simpler process to ascertain what the value SHOULD be by simply using the following formula:
=SUM(D$17:D$24)*(B17*$L$17)
However, why I had to change my approach is listed below.
The series or an array formula series, that repeats every 8 rows,
however, as the repeating pattern occurs in column B
B17: 34%,21%,13%,8%,5%,3%,2%,1%
The growth patterer compounds in
// F17 = the amount of compounded Value
// D17 = the ^2 amount used by an expositional of 2
// L17 = the increase of $ amount used each cycle.
// L15 = Target Number for the series to stop**
The reason for this elaborate formulation is to predict changes via several input variables to find the
STOP CONDITION dependant on F column is
// >= L15
To achieve the correct top-level calculation my function is
`={B17*$L$17}*{sum(offset(D17,0,0,8))}`
This adds all the future cells for an 8 range series then multiplies them, providing the correct calculation.
THE PROBLEM IS
ONCE it SPILLS, the offset calculates the numbers in the NEXT repeated series, making the only correct calculation - the first row.
I tried
`={B17*$L$17}*{sum(offset(D17,0,0,$8))}``
But I can't lock an offset height value.
I did find where someone wrote about using a
// row(F1)-1
but I'm not sure how to implement that.
THE PROBLEM CONTINUES
Once I solve this spill issue
The STOP CONDITION may happen at several different rows depending on the referenced cells, that contain the target number for the multiple inputs and variables that adust growth.
Meaning that once the Value in the initial Value column **F** hits the target of the referenced cell
// L15 = dollar amount
The pattern stops.
I hope this is understandable.
Thanks for the help if anyone out there sees this. Thank you.
This is the type question that you may want to share on a Facebook Excel group, if not solved already.
There is some very useful information in this video. I will need to watch it again for sure. Thanks Leila
Glad it was helpful!
Hi Leila, your tutorials are amazing ! Thank you
Could you please do a tutorial on the underscore operator and the "each" keyword? I find these, especially the underscore, very confusing as I can't figure out which one to use. Thanks
Thanks Leila. I need to watch this video many times to absorb :)
Gracias por tus excelentes videos Leila!!
Hi Leila, thank you for the very useful videos. I have a question. Is it possible to, instead of combining and transforming multiple files in a folder, transform first then combine the resulting tables? How to do it? Thanks.
As usual, a great video and great explanations. It'll take me time, though, to fully understand. But, even if I'm also working with your Udemy courses, these videos are a great help.
hell of a video.. blown my mind into pieces... damn I'm trying this one to grab a hand on... Thanks Leila.. Love u lots..
Still not right there with my knowledge, but going to save and watch when needed. Thanks for another brilliant one.!!
Thanks Chris. This one does take a while to digest!
Dear Leila
I love your video tutorials,
Although, this time, I don't seem to understand the basis of your video.
I understood that to create a mapping table I must first manually list the headers ("BEFORE" column) of all the tables I am working on and then create a second column ("AFTER") with the names I want to transform the "Before" Headers column into.
How can I handle this procedure if my tables are many? Won't I waste too much time collecting all the headers?
What did I miss/not understand about your instructions?
Thank you if you may reply
"we've asked the companies to fix their data. But they don't listen"
Heard.
You are simply awesome... You make such complicated things look so simple... Thanks a lot for your videos🙏
This is awesomely explained, great one!
Glad you liked it!
Wow, your mastery of Excel and Power Query is outstanding! When you said, "...I understand this is a little geeky...." My thought was, "hmm., this is more like nerdy. Did she ever spend any time playing with other kids? She should try to solve the Riemann Hypothesis! I'm sure she would be able to solve it." :)
Haha, it took me a looong time to get the hang of this function. But it's just so useful once you understand it.
@@LeilaGharani Thank you for sharing that! My brain was spinning while trying to understand that function. It also made me wonder about my mental capacity to learn, and I was wishing for better weather to ride my motorcycle instead. It’s encouraging to hear that even you struggled with it. I will persevere and watch this video a few more times so that it sticks to my brain. I have no doubt about its usefulness. Thank you sharing your knowledge, and have a good day! :)
Excellent, will have to create dummy files practice and understand. Thanks a lot, Leila
Hi.. Thank for your efforts in this. Could you also suggest easier way of combining data with different headers ?
This again highlights just how weak the PQ development environment is - it is more or less impossible to debug List.Accumulate operations. How did you do it Leila?
Wonderful video Leila. It's difficult to digest. One really need to practice this functions many times before getting a dab hand on it. Thanks. Can you also do a video on excel 365 solution to this different header problem?
Could you elaborate more on buffering mapping table? What do you mean exactly? I'm confused
This is great! Thank you a ton, Leila!
Very well explained. However, we would like to follow your tutorial step by step, as you have explained that may be easier if you can provide a link to download excel files under reference.
Thanks.
Awesome Leila, with this session, I have become a big fan of you 😘😘😘😘
Like this can you make an another session for matching data also please
Hi, thank you very much for this video!
I have a follow up question. What if the other files have different number of columns and values in different formats? For example in my case, "May- 2024 Sales" file contains all columns from previous months but also additional columns that were added to it and wouldn't be present in say "Apr-2024 Sales" file. Ideally I want all older files to display Null in this new column but I am not sure how to do it. I've been stuck on this problem for a while, I'll really appreciate it if you can help me out.
Love your videos. Simple to follow & real scenarios.
So if I have a text/ csv file, downloaded from the web (with garbage data as well) and I want output in a certain format in excel, what would be better - Power Query or Macro?
Thxs
Way past my skill-set or need, but great information and interesting to watch a master at work.
Do not feel alone. Sometimes, we use brute force to get a similar task done.
You can also transpose and lookup from your mapping table. Again transpose then make header.
Do you use lookup before or after the transformation.??
@@garylillich after first transpose
Thank you for sharing! I have followed every step but it still not working in my case, i am not sure if it is because my data file is not a table. There are too many files that i cannot set them to table format one by one.
Power Query is a new animal I've been diving into at work lately. This is fascinating. I am already amazed at what you can do with just excel but this really has me ready to dive into the course and learn Power Query. Is Power Pivot similar to this or is that a different process all together?
Thanks for very usefull solution. I have two querries with different headers loaded from website, are there any way to append its?
Fantastic!! Just what I needed! You are clearly a genius! :)
Glad it helped!
Confusing but I will watch it again and again
This is going to save me 5 millions months of arranging data files from our regions. Plus I need not to work my system generated report any longer. Just download into a folder and that's it.
This is very useful. Now add this challenge. The data can be different too. Like for example (Elbow, Elb, Elb., ELBOW, EBOW). Can we use list.accumulate to modify the data under a specific column as well?
That's easier to do. You could use Merge with a transformation table to map. If you need to map multiple words in the same field, then List.Accumulate will do the job.
Thank you so much Leila for the video. Well explained
You're welcome 😊
It is a very useful thing but in a case when you have "almost perfect" environment of data, or very close to that. But reality is of course different, where you are dealing with a jungle of data, which gives hard time to transform properly.
Random question, but how do you make your videos so clean with visuals and transitions. Do you use premiere pro :)
Thanks for noticing 😊 Mostly we use Camtasia with a bit of Premiere Pro and After Effects.
@@LeilaGharani Thanks for letting me know. I’ll have to try and improve my skills with those awesome tools. :)
This seemed to be perfect for what I was looking for...multiple spreadsheets with inconsistent columns. Unfortunately I was not able to repeat the functionality in the spreadsheets I have. For starters, when importing the data, it is not finding the table I defined as a parameter. That is TSalary in your example. In addition, when I import the actual data, Excel is ignoring the header in the source document and creating new header names, Column1, Column 2, etc.
Thank you professor Gharani 🤗
It is a excellent and hard lesson!!! Would you mind share your example files for us to practice? Many thanks
It would help users to follow along if you place the exercise files somewhere to download.
Wonderful idea.
Great video! Thank you!
Would love for you to provide a copy of this workbook. I am still having issues making this one work. Thanks!
This video is amazing, thank you so much for sharing Leila!
I have taken your course on mastering power query but i must have missed the part about List.Accumulate so i will need to go back through the course again but what great idea!
Glad you like it. It's included in the Pro section on XelPlus.
Pretty useful, as always excellent, thank you Leila
Glad you think so!
Me sirvio demasiado este video para integrar mis tablas, mil gracias!!
Thank you so much for this wonderful video. It's gonna help us a lot.
Nice lesson and very useful
Amazing Explanation Leila , i tried to reproduce the above example and it worked :) with a slight hiccup between the Column1 and Column11 as my before is just a list of Columns with a number (Column1, Column2 etc) for some reason the Current and Sate in the text.replace function recognizes Column11 as Column1 and therefore applies the after of Column1 instead of Column11 , is there a way to force the Text.Replace to look for the exact string and not an approximate :) ?
Much obliged once again for the Amazing Video
Thanks for this video Leila ! Could you please help me with the following challenge ?
Columns: JAN 2021, FEB 2021, MAR 2021, APRIL 2021….. DEC 2021
Based on current month (DEC 2021), I want to calculate the average of previous three months (SEP, OCT, NOV) for each row.
I am unable to figure out how this can be done. Please help.
Thanks !
Is it possible to manipulate data tables with headers and sub headers? For example let’s say you have a list of 10 items in column A. Then columns B and C are sub headers for units sold and $ sold, respectively, for Store #1, the header. Then you have columns D and E as units sold and $ sold (sub headers) for Store #2 (header), and repeat for as many stores as you have. Is it possible to use this type of data table
Great presentation, as usual!
How to append from several tables (not files) loaded in my workbook with different column headings and numbers?
Very much appreciate your response. Good Luck!
Thank you for this! I have multiple tabs (worksheets) within a single workbook. I'm getting confused because it adds a "Promoted Headers" step. Anyone know how to handle that?
This was such a handy tip. One issue I ran into though is similar column names. I decided to remap a series of columns that started with "Column" and the Text.Replace ended up replacing part of other column names. (Column1,Column2,Column3,...Column10,Column11,Column13)
A few quick remedies but not necessarily ideal
1. add entries at the bottom of the mapping table for the weird column names
2. sort the mapping table in reverse order
It's almost like setting up a switch statement so you really need to pay attention to when the condition is triggered.
Is there something in List.Accumulate that joins instead of replaces? or replaces whole words?
Hey, whenever i start typing the formula I cannot see the drop down of the formulas which is used during the video. Can you help me out with the same??
Hello Leila, I have a specific query: I would like to translate headers of multiple files in Excel simultaneously. Each file is composed of one sheet.
Thanks for this video.
It took me some time to understand the function...But finally I did.
However, I faced this problem. I wanted to change my headers from ...Date1, Date2, Date3... to DATE.
I got this result = DATE1, DATE2, DATE3.
The function Text.Replace changed only Date in the BEFORE .
As far as I am concerned, Date was a partial text in the AFTER column.
Any thoughts as to how PQ can make this change.
In Excel we would use search & replace using match entire cell contents.
Do we have such a feature?
Leila I would like to see the solution using List.Accumulate.
There is no Text.ReplaceValue function to take care of substrings.
I solved the headers replacements using 2 other methods & am happy by doing so I have a better understanding of PQ.
First again! Love the explanation!
Yay! Thank you!
Excellent video! I loved it!!!
But, I have a question: What if the data is coming as range (not as table) and is having 2 unwanted rows on top of every single file?
I had the same problem. I was able to get it to work by adding a line before the Table.TransformColumnNames. This is my M script:
let
Source = Excel.Workbook(#"Parameter (3)", null, true),
Navigation = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
#"Removed top rows" = Table.Skip(Navigation, 2),
#"Promoted headers" = Table.PromoteHeaders(#"Removed top rows", [PromoteAllScalars = true]),
Custom = #"Promoted headers",
TransformColumnHeader = Table.TransformColumnNames(Custom, each List.Accumulate(Table.ToRecords(COLUMN_MAPPING), _ , (state, current)=> Text.Replace(state,current[BEFORE],current[AFTER])))
in
TransformColumnHeader
@@chantellevandalen5806 Hey, I am facing a problem that whenever I start typing the formula as mentioned in the video. I'm not able to see the dropdown formulas to use. Can you help me out with the same?
Great video many thanks Leila
You’re most welcome 😊
Good evening, I'd like to ask 2 questions: 1) once we get the Power Query processing in relation to the settings we created and create a "table", is there any way to automatically export it as an XLXS file so that it is no longer related to the Query? 2) Related topic: if we have a file consisting of multiple columns, can we provide with a "command" the saving of as many files as there are "row" fields of a given column? Thanks in advance