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.
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.
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
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!!!
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 ...
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
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
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.
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.
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
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, 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 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 :)
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?
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 TH-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 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.
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?
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.
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.
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!
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.
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! :)
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
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
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.
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.
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
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!
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?
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
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?
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!
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.
This is a different subject matter but your assistance will be greatly appreciated. I know how to set the scroll area so I won't fly down to row 1245. How do I save these changes. Whenever I close the workbook, the scroll range is deleted. I have saved it as a macro enabled workbook and that did not work. Thank you
I understand the source tables. Your video just jumps into the mapping table. Did you create a new table for that? Where did it come from? I though Power Query would generate the new table... I can't figure out the first step here. How am I getting it FROM the original tables to the "Mapped table"?
Thank you Leila. Vert useful for me. I need your help : what’s the equivalent to “if nested” in power query? I generated a great ledger and would like to sort in a column, every operation by its nature to present the accounts to my end users who are not very familiar with the accounting vocabulary.
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.
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.
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
By far one of the best (if not the best) excel content creator on youtube. Simply amazing Leila
Wow, 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.
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!!!
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 ...
Danke!
Danke auch!
It works, But is going overhead.. Three Cheers to Leila, Well expalined, Many Thanks for sharing the knowledge
You're welcome!
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
Lovely!! you've just taken a load of stress off my table :) thanks for sharing!!
You are so welcome!
What a tutorial!!! Your videos are very effective and efficient for the MS users!!
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 wish I could give thousands of thumbs up. I love it and will use it a lot
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.
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 😉
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.
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
I am very excited really your teaching skills are another level
So nice of you
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 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.
I've been using power query quite a lot but didn't know about this transformation. You're Awesome Leila.🤘
Awesome! Thank you!
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, 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
I like watching this video at 2x speed. Easier to understand. 😆 great tip! Thank you for all your do!
"we've asked the companies to fix their data. But they don't listen"
Heard.
Thanks
Thank you!
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
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?
Thanks Leila. I need to watch this video many times to absorb :)
Your videos are very helpful, thank you.
Please make a video explaining how to use this technique before expanding the table.
There is some very useful information in this video. I will need to watch it again for sure. Thanks Leila
Glad it was helpful!
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..
Hi Leila your vedio most useful to us....!!!! Keep updating more vedio of power query...!!!! Thank you so much for Dil se...... ❤️❤️❤️❤️❤️❤️
Thank you for your videos! I have learned so much! Keep them coming!
Brilliant use of power query. Thank you!
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.
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 TH-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.
Gracias por tus excelentes videos Leila!!
I learn a lot from you! Great trick to handle format issues
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!
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.
exactly what I'm looking for, thanks again 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?
Excellent and strong !
One question : how activate the formula suggestion in PQ ??
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. :)
Excellent, will have to create dummy files practice and understand. Thanks a lot, Leila
Amazing video, need to ask you question about slightly different issues not sure how to compute
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.
You are simply awesome... You make such complicated things look so simple... Thanks a lot for your videos🙏
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.
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!
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.
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! :)
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.
It would help users to follow along if you place the exercise files somewhere to download.
Wonderful idea.
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
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
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
This is great! Thank you a ton, Leila!
First again! Love the explanation!
Yay! Thank you!
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.
Fantastic!! Just what I needed! You are clearly a genius! :)
Glad it helped!
Thank you so much Leila for the video. Well explained
You're welcome 😊
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.
This is awesomely explained, great one!
Glad you liked it!
Would love for you to provide a copy of this workbook. I am still having issues making this one work. Thanks!
Woah.. this is like magic.. thanks for the wonderful video on our daily battle with inconsistent data.. really helpful 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻
My pleasure 😊
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 video! Thank you!
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.
Confusing but I will watch it again and again
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.
Thank you professor Gharani 🤗
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
Could you elaborate more on buffering mapping table? What do you mean exactly? I'm confused
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?
Hi.. Thank for your efforts in this. Could you also suggest easier way of combining data with different headers ?
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
Me sirvio demasiado este video para integrar mis tablas, mil gracias!!
It is a excellent and hard lesson!!! Would you mind share your example files for us to practice? Many thanks
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?
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!
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.
Thanks for very usefull solution. I have two querries with different headers loaded from website, are there any way to append its?
This is a different subject matter but your assistance will be greatly appreciated.
I know how to set the scroll area so I won't fly down to row 1245. How do I save these changes. Whenever I close the workbook, the scroll range is deleted. I have saved it as a macro enabled workbook and that did not work. Thank you
I understand the source tables. Your video just jumps into the mapping table. Did you create a new table for that? Where did it come from? I though Power Query would generate the new table... I can't figure out the first step here. How am I getting it FROM the original tables to the "Mapped table"?
Thank you Leila. Vert useful for me. I need your help : what’s the equivalent to “if nested” in power query? I generated a great ledger and would like to sort in a column, every operation by its nature to present the accounts to my end users who are not very familiar with the accounting vocabulary.
Hi Leila,
What if you have a mapping sheet for header reference, the AFTER table is in column A and the BEFORE is in column B and C and D?
Is there something available instead of Header horizontal I can do the same function vertically??
Thank you so much for this wonderful video. It's gonna help us a lot.
Pretty useful, as always excellent, thank you Leila
Glad you think so!
Hi Leila, I hope all is well. Do you know how to use Power Query to merge files from a folder where the data has no column name / header please?
Thank you! :) But would it work if there were different number of columns in some tabels regardless of language?
Great video many thanks Leila
You’re most welcome 😊