It's not a perfect world and when it comes to data, it is very much true and this is the perfect real-life scenario which you've presented; data is dirty, especially when it is coming from different entities, different geography, different people, and different systems. Thanks, Celia, for sharing the knowledge and insight which I' sure will help many data enthusiasts out there like myself. Great stuff!!! 👍👍👍👍
Yes, the video covers one solution for one particular challenge. On a real world scenario, there might be extra steps needed before and/or after what I presented. It is up to each developer to adapt and combine this technique with other techniques according to what is need. Thank you for your valuable feedback and support.
Great Example Celia! Your pace explaining both list.zip and table,renamecolumns functions is excellent . Thanks for posting this great example. You are my number one source when it comes to power query!
This is really super helpful - I am implementing this process immediately at work. It will save so much time. So we will be repeating the process each week with the updated files. The goal going forward is to open the power query file and then just do a data, ‘refresh all’ and done! Thank you👍
Your videos are so phenomenal! I love the way you break this stuff down… I subscribed to your channel and I plan on watching every one of your videos. Thank you!
@@ubaidillahmuhammad20in the Telegram group, click the 3 dots, then click to search, then search for T0030. You will find the post with the files. Alternatively, click the name of the Telegram group at the top, and then click where it says Files at the bottom.
Celia, muchísimas gracias por este tutorial!!!! No hay palabras suficientes para agradecértelo. Sólo tengo que mirar un poco más cómo crear las listas y ponerme a trabajar en mis datos. Son ficheros de 8 fuentes diferentes, con un total de unos 20 ó 25 columnas de datos para analizar. Muchas gracias!!!!! Un saludo
@@CeliaAlvesSolveExcel , perdona que te moleste otra vez. Tengo varios archivos de varios proveedores. Los quiero consolidar todos en uno solo para poder analizar los datos de manera global. Preo algunos proveedores no incluyen algunos de los datos que quiero (y otros sí que los tienen), y además algunos datos vienen en los archivos originales en dos columnas. O sea, que tengo que tengo que hacer los siguientes cambios en los archivos originales, antes de consolidar todos los datos en una misma hoja. 1Añadir columnas con datos como Currency )GPB, or EUR, or USD), 2.- Consolidar datos que vienen en el original como "parte 1 y "parte " de un mismo dato, en un dato que contenga los dos, juntos )por ejemplo, eld CPC part 1 tiene 5 dígitos, y el CPC 2 teiene dos digitos, yo quiero un CPC con 7, no necesito los otros dos. Se puede hacer esto sin añadir mucho trabajo manual cada mes, que es cuando recibo los archivos? Gracias
Hola Antonio. Without knowing the details, I works day that you need to create a folder for each provider reports and a query to gather and and clean the data from each folder (either all the files in the folder ir just the most recent one.) Then create a last query that appends the results of each provider query. If all works out well, next time place each report in each folder and click Refresh All.
Thank you for the excellent video! Supposing I had 4 tables with the the same content but different headings on each table. Can I make the mapping table with all heading types and their corresponding heading?
Yes, you can include all the headers from the 4 tables in the mapping table and indicate how you want them to be renamed. You do not need to include the ones you want to stay the same.
Each business case will have a different logic. Extra columns or different column names may need extra steps to treat the data. If these changes are completely random and unpredictable it might de difficult to establish a reliable query to prepare your data. See if the examples in this video help for your case: ruclips.net/video/wSwXyfaXQgU/видео.html
Every tutorial I watch already has a header mapping table already completed. What happens if you are importing multiple excel files with multiple column headers and it would be too time consuming to go through each file to understand the difference in column headers? Is there anyway you can get a list of ALL the column headers in ALL the files first and these would then serve as your before column in your header making table ?
The M function Table.ColumnNames outputs a list of all the column names in a table. Connect to the folder. Then, use that M function in the last step in the transform sample file query. Then, see what you get when you expand the colum with the result from each table in the main query.
Hi ! I'm running into the following issue: At 19:00 I get this error: Expression. Error: The field 'X' already exists in the record Name = X Value = Any idea what the issue might be?
It looks like you are instructing Power Query to rename one column with a name that already exists in another column in that table where the column renaming is happening.
@@CeliaAlvesSolveExcel I see ! So in your exemple, if in my 3 files I had columns named School, School Name and School ID, I would not be able to rename them all School because one of them already has the name School?
@@CeliaAlvesSolveExcel Thanks! Does that mean it is not possible to renames a header when there is a similar one in another file? For example, in my case, I have several files containing a "Ad Set Name" header. I wish to rename them all to "Territory ". Is that possible?
Hi, great video. Is a way to rename columns if my source data is part of my worksheet. Let's say from your example all three examples were on on spreadsheet in different tabs? Perhaps you can do a video on this?
Celia, I just happened upon your video and love the explanation. Will this also work if there are unneeded columns in the middle of some files that you are combining? I am importing data from a source that sometimes changes not only the names but also the structure by adding, deleting or moving the columns. There are hundreds of files in my folder that are being combined, so very difficult to know which follow which structure change. Many thanks in advance!
Hi, Nicole. The other columns will remain with the same column name, and will disappear from your consolidated data when you select the columns that are common and you want to keep and select remove other columns. When preparing the Transform Sample File Query, make sure you use one of the files with most columns in it, specially if you are reading from sheets instead of tables.
This is a great lesson Celia. Would this also work if there is a case mismatch between the find column in the replacement table and the actual column headers in the target table?
Monoj, Power Query is case sensitive, so case mismatches may cause an error. You can add an additional step to convert the columns hearers to the correct casing first
Hi Celia That was a good one, and easy to implement. One thing though that puzzles me, how do you set Power Query to “Load to Only Create Connection” as default? Best regards
@@CeliaAlvesSolveExcel Hi Celia! Quick question and thank you so much in advance for any help you can provide: I followed the steps you explained in the video with .xls files. It worked all the way and consolidated the info perfectly in Power Query. But when I gave the instruction to close & load to a table in a separate tab, it gave me the following message: "Expression Error.: The key didn't match any rows in the table". Do you happen to know how to fix it? I checked the code in the Advanced Editor for the Transform Sample File between what is created using your .csv files and the .xls files; the only difference is that for the .xls files there is an extra line of code that says: "Fran_Sheet=Source{[Item="Fran",Kind="Sheet"]}[Data]", where Fran is the name of the table...
@@LAG5499 try replacing Kind="Sheet" with Kind="Table" Not sure if Table is with T or t. You can check that by looking at the table in step Source in PQ editor, column Kind, the row with Fran as table name.
Excellent video - very clear and concise! - I do have one more... query. I have multiple folders streaming into one file - however, 2 of them have the same column name which relate to different things, eg: File 1: Sector relates to locations, File 2: Sector relates to type of business. How do I clarify the differentiation? Ideally, I need both columns in my table, but need them names separately. Any ideas? :)😄
Hi, AJ. Maybe the technique to use in that situation is not quite the same as the one in the video. Even because you mention separate folders for each file type. Are you combining the data from both file types? If yes, how you are combining it: merging or appending it? Without knowing all the details, my suggestion is that in one of the queries that imports the data from one of those file folders, include a step to change the name of the Sector column to something else that makes sense to the case. If you do that early in the process, that column will always be identified by its new name for the rest of the process without being mixed with the other column. I hope this helps.
That was a really great way of renaming, i've used various methods insert rows and then promote them and even using zip, but within the Table.renamecolumns argumnent, which needs a lot more steps, I'll subscribe.
Hi, Dino. Without looking at your project details, I would suggest applying the same technique to the queries importing from each folder, and then creating a new query that appends the resulting tables from the several folders. I hope this helps. De nada! 😉
Good stuff. Now I'm wondering if we can build a kind of schema table as metadata in the workbook, including types to change to, and use a function to "clean" the column names, column types etc. I would create all column headers with snake_case and maybe allow for specification of numerical precision as well. Anyway, thanks for the video! Sparked more ideas :)
There goes Owen, light speeding to a whole new world of possibilities! 😄 that's a great idea and project. You might as well add the row number in which column headers are in each file. 😎
This didnt work for me. Anyone know why? I keep getting an error stating RenameOperations Value details [list] when trying to add the RenameList to the transform sample file
Oddly enough this cause me the "Expression.Error: Evaluation ran out of memory and can't continue." just when i expand the tables. Update: I delete the expand step and re-do it. That did the trick.
Excellent technique! Inconsistent file formats are a constant problem. Thank you for sharing.
Yes! One of many challenges we get from data.
Thank you for watching and your feedback.
It's not a perfect world and when it comes to data, it is very much true and this is the perfect real-life scenario which you've presented; data is dirty, especially when it is coming from different entities, different geography, different people, and different systems.
Thanks, Celia, for sharing the knowledge and insight which I' sure will help many data enthusiasts out there like myself. Great stuff!!! 👍👍👍👍
Yes, the video covers one solution for one particular challenge. On a real world scenario, there might be extra steps needed before and/or after what I presented. It is up to each developer to adapt and combine this technique with other techniques according to what is need.
Thank you for your valuable feedback and support.
@@CeliaAlvesSolveExcel Absolutely!!
I can't agree more.
I am so grateful. This has solved a challenge I had for long. Thank you
I am grateful for you letting me know that. It makes my day. :)
Great Example Celia! Your pace explaining both list.zip and table,renamecolumns functions is excellent . Thanks for posting this great example. You are my number one source when it comes to power query!
And you just made my day! Thank you, @jazzista1967
Thank you, Celia and Stay safe. Look forward to your next video!
Finally! I've tried other methods to map the headers when importing files with tables already defined without joy - Your method cracked it - thanks! 😁
Awesome! And thank you for letting me know. Your feedback brightens my day. 😊
This is really super helpful - I am implementing this process immediately at work. It will save so much time. So we will be repeating the process each week with the updated files. The goal going forward is to open the power query file and then just do a data, ‘refresh all’ and done! Thank you👍
That's what Power Query Magic is all about! :) glad that it helped you, Irene.
lovely!!!! it really solves a BIG problem bothering me for a Year!!! Big Thanks!
Awesome! Very glad it helped you. Thank you for leaving your feedback. 🙏
Celia thank you for your tutorial, is simple but effective
Glad that it helped you.:)
An excellent lesson Celia. Thanks for the practical example and demonstration. Thumbs up!!
Thank you, Wayne! Always so nice to hear from you.
Your videos are so phenomenal! I love the way you break this stuff down… I subscribed to your channel and I plan on watching every one of your videos. Thank you!
Thank you, Cecil. I am very glad that you found my videos helpful. Let's keep learning!
Excellently explained! Easy to understand. Thanks so much.
You're very welcome, Gospel! Glad that it helped. Thank you for your feedback.
Wow! That was one of the greatest videos I've ever seen! It really helped me get a better understanding of how Power Query M interprets things. :-)
Thank you for your feedback, Gregory! Very glad that it helped you moving a step up. Keep learning!
Cool, Calm and very useful delivered in smooth style thanks again. Andrew
Thank you, Andrew :)
This is the most amazing combining files with different header tricks. Thanks fo sharing. You have solved my problem
Very glad that I could help. :)
@@CeliaAlvesSolveExcel Yes, you saved my time. I have downloaded different videos but your's was just very detailed and helpful
This is great Celia! I always learn so much from you. Thanks for sharing! 🤗
I learn from you too, Alejandra! Thank you for watching. :)
@@CeliaAlvesSolveExcel 🥰
Excellent! A masterful explanation-thank you so much!
@@shanthirajkini awesome! Glad that it helped you. Thank you for leaving your feedback.
In telegram there is no files yet
@@ubaidillahmuhammad20in the Telegram group, click the 3 dots, then click to search, then search for T0030. You will find the post with the files.
Alternatively, click the name of the Telegram group at the top, and then click where it says Files at the bottom.
Clear & comprehensive explanation great.
Thank you, Ratna. Glad that it helped.
Brilliant technique! Many thanks, Celia.
Thank you Ian! Glad that you found it valuable. 👊
You are great in explaining power query. Thank you.
Thank you. Glad that you find value in the videos.
Celia, muchísimas gracias por este tutorial!!!! No hay palabras suficientes para agradecértelo. Sólo tengo que mirar un poco más cómo crear las listas y ponerme a trabajar en mis datos. Son ficheros de 8 fuentes diferentes, con un total de unos 20 ó 25 columnas de datos para analizar. Muchas gracias!!!!! Un saludo
@@AntonioHuete-Munoz pues que de nada, Antonio! Me alegra que te haya ayudado. Que te salga bien y adelante!
@@CeliaAlvesSolveExcel , perdona que te moleste otra vez. Tengo varios archivos de varios proveedores. Los quiero consolidar todos en uno solo para poder analizar los datos de manera global. Preo algunos proveedores no incluyen algunos de los datos que quiero (y otros sí que los tienen), y además algunos datos vienen en los archivos originales en dos columnas. O sea, que tengo que tengo que hacer los siguientes cambios en los archivos originales, antes de consolidar todos los datos en una misma hoja. 1Añadir columnas con datos como Currency )GPB, or EUR, or USD),
2.- Consolidar datos que vienen en el original como "parte 1 y "parte " de un mismo dato, en un dato que contenga los dos, juntos )por ejemplo, eld CPC part 1 tiene 5 dígitos, y el CPC 2 teiene dos digitos, yo quiero un CPC con 7, no necesito los otros dos. Se puede hacer esto sin añadir mucho trabajo manual cada mes, que es cuando recibo los archivos?
Gracias
Hola Antonio.
Without knowing the details, I works day that you need to create a folder for each provider reports and a query to gather and and clean the data from each folder (either all the files in the folder ir just the most recent one.)
Then create a last query that appends the results of each provider query.
If all works out well, next time place each report in each folder and click Refresh All.
Great video celia, i'm going to use it at work ! ✌
Awesome, Anthony! Glad that it helped. :)
Muito bom e muito útil Célia. Muito obrigado por essa aula, eu buscava essa alternativa há muito tempo 👏👏👏👏👏
Que bom que ajudei, Ricardo. :) bom proveito aí!
Thank you for the excellent video! Supposing I had 4 tables with the the same content but different headings on each table. Can I make the mapping table with all heading types and their corresponding heading?
Yes, you can include all the headers from the 4 tables in the mapping table and indicate how you want them to be renamed. You do not need to include the ones you want to stay the same.
Great video, What if next quarter there are some more coloumns or coloumn are renamed differently again by sender
Each business case will have a different logic. Extra columns or different column names may need extra steps to treat the data. If these changes are completely random and unpredictable it might de difficult to establish a reliable query to prepare your data.
See if the examples in this video help for your case: ruclips.net/video/wSwXyfaXQgU/видео.html
Thanks for a great video. Regards from South Africa.
Thank you, Chris! Glad that it helped you. Regards from Canada! :)
Love it!!! This is exactly what I was looking for
Great! Glad that I could help.
Every tutorial I watch already has a header mapping table already completed. What happens if you are importing multiple excel files with multiple column headers and it would be too time consuming to go through each file to understand the difference in column headers? Is there anyway you can get a list of ALL the column headers in ALL the files first and these would then serve as your before column in your header making table ?
The M function Table.ColumnNames outputs a list of all the column names in a table.
Connect to the folder. Then, use that M function in the last step in the transform sample file query. Then, see what you get when you expand the colum with the result from each table in the main query.
Hi !
I'm running into the following issue:
At 19:00 I get this error:
Expression. Error: The field 'X' already exists in the record
Name = X
Value =
Any idea what the issue might be?
It looks like you are instructing Power Query to rename one column with a name that already exists in another column in that table where the column renaming is happening.
@@CeliaAlvesSolveExcel I see !
So in your exemple, if in my 3 files I had columns named School, School Name and School ID, I would not be able to rename them all School because one of them already has the name School?
@@A7Xfanfr correct. In that case you do not include School in the renaming list because that column does not need to be renamed.
@@CeliaAlvesSolveExcel Thanks!
Does that mean it is not possible to renames a header when there is a similar one in another file?
For example, in my case, I have several files containing a "Ad Set Name" header. I wish to rename them all to "Territory ". Is that possible?
@David Renoux it is. Just add that pair of old column name and new column name to the table shown right after min 4:26.
Hi, great video. Is a way to rename columns if my source data is part of my worksheet. Let's say from your example all three examples were on on spreadsheet in different tabs? Perhaps you can do a video on this?
Celia, I just happened upon your video and love the explanation. Will this also work if there are unneeded columns in the middle of some files that you are combining? I am importing data from a source that sometimes changes not only the names but also the structure by adding, deleting or moving the columns. There are hundreds of files in my folder that are being combined, so very difficult to know which follow which structure change. Many thanks in advance!
Hi, Nicole. The other columns will remain with the same column name, and will disappear from your consolidated data when you select the columns that are common and you want to keep and select remove other columns.
When preparing the Transform Sample File Query, make sure you use one of the files with most columns in it, specially if you are reading from sheets instead of tables.
Thank you so much. You really helped me.
Glad that the video was helpful to you. Thank you for watching and leaving your feedback. Cheers! :)
This is a great lesson Celia.
Would this also work if there is a case mismatch between the find column in the replacement table and the actual column headers in the target table?
Monoj, Power Query is case sensitive, so case mismatches may cause an error. You can add an additional step to convert the columns hearers to the correct casing first
Great video, thanks a lot!
You're very welcome. Thank you for watching and your feedback.
Hi Celia
That was a good one, and easy to implement.
One thing though that puzzles me, how do you set Power Query to “Load to Only Create Connection” as default?
Best regards
Hi, Ivan. Watch here how to set the default loading destination as "connection only." ruclips.net/video/GC775BkS528/видео.html
Hello there!! Amazing video!! Great explanation!! Everything was very clear!! Best video on this topic. I just subscribed to the channel :-)
Thank you very much for your feedback and welcome to my channel! :)
@@CeliaAlvesSolveExcel Hi Celia! Quick question and thank you so much in advance for any help you can provide: I followed the steps you explained in the video with .xls files. It worked all the way and consolidated the info perfectly in Power Query. But when I gave the instruction to close & load to a table in a separate tab, it gave me the following message: "Expression Error.: The key didn't match any rows in the table". Do you happen to know how to fix it?
I checked the code in the Advanced Editor for the Transform Sample File between what is created using your .csv files and the .xls files; the only difference is that for the .xls files there is an extra line of code that says: "Fran_Sheet=Source{[Item="Fran",Kind="Sheet"]}[Data]", where Fran is the name of the table...
@@LAG5499 try replacing Kind="Sheet" with Kind="Table"
Not sure if Table is with T or t.
You can check that by looking at the table in step Source in PQ editor, column Kind, the row with Fran as table name.
Excellent video - very clear and concise! - I do have one more... query.
I have multiple folders streaming into one file - however, 2 of them have the same column name which relate to different things, eg: File 1: Sector relates to locations, File 2: Sector relates to type of business. How do I clarify the differentiation? Ideally, I need both columns in my table, but need them names separately.
Any ideas? :)😄
Hi, AJ. Maybe the technique to use in that situation is not quite the same as the one in the video. Even because you mention separate folders for each file type.
Are you combining the data from both file types? If yes, how you are combining it: merging or appending it?
Without knowing all the details, my suggestion is that in one of the queries that imports the data from one of those file folders, include a step to change the name of the Sector column to something else that makes sense to the case. If you do that early in the process, that column will always be identified by its new name for the rest of the process without being mixed with the other column.
I hope this helps.
Well-explained!
Thank you, Dexter
Thank you ... I just subscribed to your channel
Awesome! 🥳 Happy that I helped and welcome to my channel. 😊
That was a really great way of renaming, i've used various methods insert rows and then promote them and even using zip, but within the Table.renamecolumns argumnent, which needs a lot more steps,
I'll subscribe.
Yay! Welcome! So glad that this video helped you.
Excelente ensinamento. Obrigada
Muito obrigada, Ana! Fico feliz que lhe seja útil.
Hi Celia very good I have the same problem but with multiple folders, i would like to know how to do it? Tks in advance Obrigado
Hi, Dino. Without looking at your project details, I would suggest applying the same technique to the queries importing from each folder, and then creating a new query that appends the resulting tables from the several folders. I hope this helps.
De nada! 😉
Thanks for sharing
Thank you Celia i am grateful
You're welcome, Cheikh. Glad that it helped you.
You're welcome, Aicx. Glad that it was helpful for you.
Good stuff. Now I'm wondering if we can build a kind of schema table as metadata in the workbook, including types to change to, and use a function to "clean" the column names, column types etc. I would create all column headers with snake_case and maybe allow for specification of numerical precision as well.
Anyway, thanks for the video! Sparked more ideas :)
There goes Owen, light speeding to a whole new world of possibilities! 😄 that's a great idea and project. You might as well add the row number in which column headers are in each file. 😎
This didnt work for me. Anyone know why? I keep getting an error stating RenameOperations Value details [list] when trying to add the RenameList to the transform sample file
Difficult to say without looking at it. Make sure your are not missing a step, or a square basket or curly bracket somewhere.
Oddly enough this cause me the "Expression.Error: Evaluation ran out of memory and can't continue." just when i expand the tables.
Update: I delete the expand step and re-do it. That did the trick.
To mach bla bla bla
Thanks for the video. You are very good at explaining stuff. 👏
You're weekday! Glad that it helped.