Awesome Mynda and Phil! Thanks for demonstrating the various techniques and transformations, as well as for providing the sample files to follow along. Very helpful! Thumbs up!!
Thank you for this and ALL your videos (especially PQ). I used this technique recently to compare data extracted from a source system and Power BI. I was able to complete my analysis in minutes vs. hours AND it's easily repeatable. Thank you again for all your great work! I recommend your videos to all my colleagues an anyone else looking to level up their skills!
Thank you Sir and Madam! This video is really helpful for me I was facing this problem where I have dates in column 30 dates in 30 column for all 12 months which comes in 365 dates column from 12 files of each month now this video really solved my problem
Very interesting. Thank you for the simple solutions to complex problems. Wondering if the solution presented for folder files would work on an existing Enterprise-level model without too many issues. Going to think through this one. Thanks again. Great job.
Dear Mynda Thanks, that was a great tutorial One thing I not so seldom see is that there has been added or removed columns, or even renamed It´s not that big a problem to solve, but better if it´s dynamic Best regards Ivan
Good day and video! Question about the video around the 6:57 mark. The instruction removes duplicated item and date fields. Does this procedure remove data from the value field from original set of tables? It appears that value data would be lost. Thanks for the work on these!
Thanks, Harry! Power query does not change the original source data. It takes a copy of the data and applies the transformations before loading the copy to your file. It can’t write back to the source.
Nice watching the masters about their work. Now I need to take a nap as my head is spinning from all the promotions, demotions, unpinning and coding 🤨! Quick question.... is there a best practice procedure (or have you created any resources that cover this) - also for cases where columns have different names but with the added complexity where unique identifiers don't exist on all tables to match the data? I'm thinking of a scenario where you wish to merge 3 tables. 2 of the tables can be linked through a staff code for instance, but the third table only has the staff name. In the perfect world, a lookup could be used to locate the staff id from the name. That might work in 90% of the lookups, but the other 10% may be unsearchable due to misspellings or dupes. Maybe I'm asking too much of Power Query in this instance, but I would be interested to know what techniques others use to surmount this challenge?
@@MyOnlineTrainingHub Wow thanks so much for this Mynda, I've just had a look and I'll keep this ace up my sleeve in case I encounter this challenge again. Thanks also for the quick response. Well you learn something new every day! 😊
Question: isn't it faster to modify transform sample table? By faster I mean both coding it as a user and faster for PB/PQ to transform file. Can you advise?
Yes, absolutely use the sample file as an option. I'm not sure it would be any faster. You'd have to test on large datasets. The main purpose of this tutorial was to educate about the ability to manipulate tables in columns. Hopefully you'll find that ability useful and the easy ways to get the correct formulas by adding a step and then copying the formula.
Great video love all of your videos. Can I ask what your approach would be if you were given a file with 3000 columns with merged columns so entries for about 1500 items in 2 columns and you need to add up the information in each of these 2 columns to get the values for 100 rows of data? The first column of the pair has a name and the second is column1 through column1500. My first instinct is to run away screaming but there must be some way to transform this into usable data 🧐
It's difficult to picture your scenario. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Since we remove duplicates from the query - what if some of the lines in each of the files were not dupposed to be duplicate in case of transactional data (i.e. sales of one SKU in Apr-22 of 159 units appearing twice) and hence tge PQ would take that out as duplicate where it should not. In that case - would it not be better to modify the file name and have a filter column where "month column" would be equal to the "source column (file name)"
Then there aren't any duplicates to remove, so I don't see the issue. Probably best if you post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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
@@MyOnlineTrainingHub Thank you so much for your attention and your reply. Is there any way to change the source of a query from "file" to "folder"? Another question: if the file is shared with other people, its there any way to "lock/protect" the query to avoid that other people can damage it? I'd like just them, as in the folder solution, could press just "update" button when the file was changed into the folder. Thanks in advance.
Yes, you can change the source from file to folder, but this will also require some other changes to the query. Probably best to create a new query using From Folder and then copy the code from that query into your From File query. Yes, yo can protect the workbook structure so they cannot open the query editor, but the query can still be refreshed. Please post any further questions on our forum where we can help you further (as I am unlikely to see further replies to this thread due to the volume of comments I get): www.myonlinetraininghub.com/excel-forum
while importing web data through power query the document is showing a "table" name under the column name "children"... can u pl guide how to import data contained in that "table"..???
Great video! I am struggling with this where I need to combine worksheets where the first group of columns are identical (“budget”, “EAC”, “ACWP”, etc.) followed by a number of variable date columns and not the same number of date columns from one file to the next. I can get them to combine but my dates aren’t always sorted in order from left to right. Do you have a solution for this scenario?
I get files on daily basis and need to maintain the data in common file. For example i get data form 3 studies, there are 15 different files with different columns names. However consists data of 3 studies respectively. I open an excel and copy and paste the data from 15 files into its respective tabs. so basically maintain 3 study files. Can i use power query to automate it. Like running and rhe data get added to the same tracker every day
OMG, yes absolutely Power Query can do this for you. If you'd like to get up to speed with Power Query, please check out my course: www.myonlinetraininghub.com/excel-power-query-course
Thank you for this great tutorial. I'd really appreciate your help on a related more complex scenario. I have several files, each file has several sheets with different headers. I want to combine all sheets from all files into one big table. I managed to do everything but I'm not able to get around the logic where it takes a specific file as example for setting the table schema. So I end up with the structure of first file and I lose all others. Just for simplicity, Let's say I have 2 files: first has 2 columns A and B. While second file has B and C. I want to combine the files similarly to when append between queries is done. The result should be the union of all table headers (A, B, C). A,B,C are not values from a dimension, so unpivot option is not relevant.
@@MyOnlineTrainingHub Thank you, I have managed. The trick was to create a query that would scan the files and find all columns named and create a list of these columns. Then, to use this list when expanding each file.
Not sure what you mean by that. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How do you deal with the different date formats? One of the file uses day/month/year (wich is the good way) and the other file uses month/day/year (wich shouldn't NEVER be allowed!) When you change the data type to dates, the M/D/Y change properly the other ones give errors. These are small things that make computing even more difficult for no reason. I also have to deal with comas and points since I'm using a French Excel.
In Australia our dates are the good way too ;-) The first file uses mmm-yy. There is no day component, so when we change the data type to date, Power Query automatically assumes the day is the 1st of each month. The other file is dd/mm/yyyy. If you're having trouble with the dates not changing correctly, please see this video on changing data types using locale: ruclips.net/video/QKgS3hrrmvw/видео.html
Hi mynda I have data set consist column city, mon, tue ..... Etc, total sales, commission, bonus, rank And I have assign rank to each city As per column ( sales rank 609 2) By using range lookup formula I can't understand how to use to do this
I have a situation where I have many csv files with different column names all in different places. I can't use the solutions for example 2 because they assume the columns are in the same spot, just with different header names. And the data is not pivoted so example 1 is not applicable. I really just need a solution that can merge the headers only, then combine the files. This can be done easily for files that you manually select, but not for random files within a folder.
Hi Jacob, if the files have the same names, but in a different order, then you can use Get Files > From Folder and Power Query will match the columns for you.
Awesome Mynda and Phil! Thanks for demonstrating the various techniques and transformations, as well as for providing the sample files to follow along. Very helpful! Thumbs up!!
Our pleasure, Wayne!
Thanks. I’ve just used this trick at work today and it’s much neater than what I would have done without your video.
Great to hear, Stephen!
Could you re share this tutorial again by u @Mynda Treacy
1-year ago but this solved a problem I was having so you have a new subscriber.
Great to hear!
This is fantastic. I never knew that you can manipulate tables in columns. Brilliant. Thank you for uploading this
Glad you liked it!
This is the solution I have been looking for for months! Thank you!
Glad we could help 😊
Excellent! No words to appreciate the content and the way this is thought. Thank you so much!
So nice of you 🙏
very good tip Phil.. Thanks Mynda and team. Best wishes! Cherian from Abu Dhabi
Glad you enjoyed it, Cherian!
This channel is absolute magic
Thanks so much, Carlo!
This just helped me with a task I was struggling with that's has a similar dataset in this video. Thanks very much for simplifying this
Awesome to hear!
Amazing job, Mynda! I've been following up with Phil's Power Query tips, has helped me a lot at work.
Great to hear, Omar!
Amazing! Simple and efficient!
Great to hear!
Thank you for this and ALL your videos (especially PQ). I used this technique recently to compare data extracted from a source system and Power BI. I was able to complete my analysis in minutes vs. hours AND it's easily repeatable. Thank you again for all your great work! I recommend your videos to all my colleagues an anyone else looking to level up their skills!
Wow, that’s awesome to hear, Chris! 😍
Thank you Sir and Madam! This video is really helpful for me I was facing this problem where I have dates in column 30 dates in 30 column for all 12 months which comes in 365 dates column from 12 files of each month now this video really solved my problem
Glad it helped 😊
This saved me a LOT of work! Thanks a bunch!
Great to hear 😊
Absolutely brilliant!!! Pure Ninja!!! Thanks Phil! Thanks Mynda!
Our pleasure!
Fantastic. Of course it is working with many sheets of one files too.
Great to hear!
Thank you Linda for this amazing tutorial..
You are most welcome!
Thank you for sharing, guys! Helped me a lot
Our pleasure!
Amazing, thanks Mynda and Phil!
Glad you liked it, Chris!
Amazing tutorial. Thank you for sharing
Thanks for watching, Arya!
Hi Mynda!Great Tutorial From Phil With Really Helpful Tips...Thank You Both :):)
Glad you enjoyed it, Darryl!
Cool video, another option would be to remove duplicates in column 2 or 3 and then promote headers again. Cheers, Seb
Cheers, Seb!
Wow Phil very clever great tutorial thank you
Glad it was helpful, John!
Tremendous video. That's caviar!!!
Cheers, Ivan! 😊
Very interesting. Thank you for the simple solutions to complex problems. Wondering if the solution presented for folder files would work on an existing Enterprise-level model without too many issues. Going to think through this one. Thanks again. Great job.
Glad you liked it, Chris! Hope it works out for you.
Useful, Thanks Mynda!
Great to hear, Luciano!
Thank you for the tutorial.
Our pleasure 😊
Dear Mynda
Thanks, that was a great tutorial
One thing I not so seldom see is that there has been added or removed columns, or even renamed
It´s not that big a problem to solve, but better if it´s dynamic
Best regards Ivan
Cheers, Ivan!
Good day and video! Question about the video around the 6:57 mark. The instruction removes duplicated item and date fields. Does this procedure remove data from the value field from original set of tables? It appears that value data would be lost. Thanks for the work on these!
Thanks, Harry! Power query does not change the original source data. It takes a copy of the data and applies the transformations before loading the copy to your file. It can’t write back to the source.
Nice watching the masters about their work. Now I need to take a nap as my head is spinning from all the promotions, demotions, unpinning and coding 🤨! Quick question.... is there a best practice procedure (or have you created any resources that cover this) - also for cases where columns have different names but with the added complexity where unique identifiers don't exist on all tables to match the data? I'm thinking of a scenario where you wish to merge 3 tables. 2 of the tables can be linked through a staff code for instance, but the third table only has the staff name. In the perfect world, a lookup could be used to locate the staff id from the name. That might work in 90% of the lookups, but the other 10% may be unsearchable due to misspellings or dupes. Maybe I'm asking too much of Power Query in this instance, but I would be interested to know what techniques others use to surmount this challenge?
Hi David, glad you found it helpful. You might be able to use fuzzy matching for the names: ruclips.net/video/5LiBN4yE3mM/видео.html
@@MyOnlineTrainingHub Wow thanks so much for this Mynda, I've just had a look and I'll keep this ace up my sleeve in case I encounter this challenge again. Thanks also for the quick response. Well you learn something new every day! 😊
Question: isn't it faster to modify transform sample table? By faster I mean both coding it as a user and faster for PB/PQ to transform file. Can you advise?
I was going to suggest the same. Promote the headers and unpivot in the sample file!
Yes, absolutely use the sample file as an option. I'm not sure it would be any faster. You'd have to test on large datasets. The main purpose of this tutorial was to educate about the ability to manipulate tables in columns. Hopefully you'll find that ability useful and the easy ways to get the correct formulas by adding a step and then copying the formula.
Great video love all of your videos. Can I ask what your approach would be if you were given a file with 3000 columns with merged columns so entries for about 1500 items in 2 columns and you need to add up the information in each of these 2 columns to get the values for 100 rows of data? The first column of the pair has a name and the second is column1 through column1500. My first instinct is to run away screaming but there must be some way to transform this into usable data 🧐
It's difficult to picture your scenario. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Very nice, thanks
Our pleasure!
How do you view the table at the bottom of the query, when you check the changes? An example is at 3:42 of the video
Click in the white space beside 'Table' and it will display a preview pane at the bottom of the Power Query window.
Since we remove duplicates from the query - what if some of the lines in each of the files were not dupposed to be duplicate in case of transactional data (i.e. sales of one SKU in Apr-22 of 159 units appearing twice) and hence tge PQ would take that out as duplicate where it should not.
In that case - would it not be better to modify the file name and have a filter column where "month column" would be equal to the "source column (file name)"
In that case you should select the columns required in order to identify unique transactions e.g. SKU, Date and Order Number.
@@MyOnlineTrainingHub What if we don't have the order number and have to assume that each monthly record, in each of the filed is a unique record?
Then there aren't any duplicates to remove, so I don't see the issue. Probably best if you post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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
You'd have to write some VBA code to automate these tasks as there's no built in way to do this.
@@MyOnlineTrainingHub Thank you for your answer and support. Do you think I could also use POWER AUTOMATE for it?
@@MyOnlineTrainingHub Thank you so much for your attention and your reply. Is there any way to change the source of a query from "file" to "folder"? Another question: if the file is shared with other people, its there any way to "lock/protect" the query to avoid that other people can damage it? I'd like just them, as in the folder solution, could press just "update" button when the file was changed into the folder. Thanks in advance.
Not sure, as I'm not a Power Automate expert.
Yes, you can change the source from file to folder, but this will also require some other changes to the query. Probably best to create a new query using From Folder and then copy the code from that query into your From File query. Yes, yo can protect the workbook structure so they cannot open the query editor, but the query can still be refreshed. Please post any further questions on our forum where we can help you further (as I am unlikely to see further replies to this thread due to the volume of comments I get): www.myonlinetraininghub.com/excel-forum
These all assume the data is in the correct order. Is there a way to do this when the headers are mismatched and in different orders?
thanks... perfect
Glad it helped 😊
Is there a book on power query for excel?
You can find some books here: bit.ly/3HLxTgK
Amazing!!
Thanks so much 🙏😊
while importing web data through power query the document is showing a "table" name under the column name "children"...
can u pl guide how to import data contained in that "table"..???
Great video! I am struggling with this where I need to combine worksheets where the first group of columns are identical (“budget”, “EAC”, “ACWP”, etc.) followed by a number of variable date columns and not the same number of date columns from one file to the next. I can get them to combine but my dates aren’t always sorted in order from left to right. Do you have a solution for this scenario?
do a calendartable separated, add the number of the month and then put that in order. dont forget to add connections between the two
Will these changes be applied to every new file that comes in?
Yes.
I get files on daily basis and need to maintain the data in common file. For example i get data form 3 studies, there are 15 different files with different columns names. However consists data of 3 studies respectively. I open an excel and copy and paste the data from 15 files into its respective tabs. so basically maintain 3 study files. Can i use power query to automate it.
Like running and rhe data get added to the same tracker every day
OMG, yes absolutely Power Query can do this for you. If you'd like to get up to speed with Power Query, please check out my course: www.myonlinetraininghub.com/excel-power-query-course
Thank you for this great tutorial.
I'd really appreciate your help on a related more complex scenario.
I have several files, each file has several sheets with different headers.
I want to combine all sheets from all files into one big table. I managed to do everything but I'm not able to get around the logic where it takes a specific file as example for setting the table schema.
So I end up with the structure of first file and I lose all others.
Just for simplicity, Let's say I have 2 files: first has 2 columns A and B. While second file has B and C.
I want to combine the files similarly to when append between queries is done. The result should be the union of all table headers (A, B, C).
A,B,C are not values from a dimension, so unpivot option is not relevant.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub
Thank you, I have managed.
The trick was to create a query that would scan the files and find all columns named and create a list of these columns.
Then, to use this list when expanding each file.
Hi, How do you count the number of traffic lights in a range of cells ?
Not sure what you mean by that. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How do you deal with the different date formats? One of the file uses day/month/year (wich is the good way) and the other file uses month/day/year (wich shouldn't NEVER be allowed!) When you change the data type to dates, the M/D/Y change properly the other ones give errors. These are small things that make computing even more difficult for no reason. I also have to deal with comas and points since I'm using a French Excel.
In Australia our dates are the good way too ;-) The first file uses mmm-yy. There is no day component, so when we change the data type to date, Power Query automatically assumes the day is the 1st of each month. The other file is dd/mm/yyyy. If you're having trouble with the dates not changing correctly, please see this video on changing data types using locale: ruclips.net/video/QKgS3hrrmvw/видео.html
Hi mynda
I have data set consist column city, mon, tue ..... Etc, total sales, commission, bonus, rank
And I have assign rank to each city
As per column ( sales rank
609 2)
By using range lookup formula
I can't understand how to use to do this
Can you help ne out in this?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub thank you definitely I share there 🙂
Where can I get the data?
See the link in the video description.
I have a situation where I have many csv files with different column names all in different places. I can't use the solutions for example 2 because they assume the columns are in the same spot, just with different header names. And the data is not pivoted so example 1 is not applicable. I really just need a solution that can merge the headers only, then combine the files. This can be done easily for files that you manually select, but not for random files within a folder.
Hi Jacob, if the files have the same names, but in a different order, then you can use Get Files > From Folder and Power Query will match the columns for you.
Wow.... It's super complicated to me still!🤣 I have got to watch it over and over to digest the info n steps. 😅
That's the beauty of video, you can rewatch as many times as you like 😊
I like the lesson but not the teacher. He sounds sleepy or at worst uninterested.