Wow Excellent video. I have searching for this everywhere. Finally I got it. Very easy to follow. Thanks much for uploading it. Could you please share how we can combine files from a sharepoint folder please?
Thank you so much. I combined more than 10 workbooks following your steps yes it worked but it is too slow on loading and refreshing. It can take almost an hour. what should I do because this technique had worked for me? Your guidance will be highly appreciated
I will say that when I did this it turned all my columns into Text Columns. I had to manually go back to each column to turn it back to a currency or number column. Still very helpful
Thanks for the helpful video ,also could you help me with the question , get most recent files FROM FOLDER in Power BI | What to do when new columns don't show?
Hi Bas, great video, thank you but what to do if let’s say in the last 2 files you don’t only have 2 extra columns but the old column names have also been slightly changed compared to the other files + the order is not the same either anymore? Thank you!
Exactly the solution I was looking for. Cheers my buddy! My question is, my PBI has Added Conditional Column that I've already created after I combined and transformed the data. There are at least 5 additional steps. Can I still go ahead and do this solution and still be able to use all my added steps?? or will it effect those steps? Thank You!
This worked for me. HOWEVER, when I add my next file (or the next or next), those columns are not being added. I need to be able to combine every file that goes into the folder automatically.
This is a fantastic solution and perfectly presented. Kudos! Thought you solved my problem but this solution doesn't work with CSV's. Looking for the same but for CSV's. When I perform the [Transform File] steps, I see that the new CSV is imported but still the new fields are not. UPDATE: I've been working on this all day and finally have it working properly. For some reason, as mentioned earlier, when I perform these steps, the source CSV does not include the new fields/columns from [Transform Data]. No matter what I tried, I could not get the test source file to show the new fields by performing just [Transform Data]. Because it was not displaying the new fields, I opened the Advanced Editor from "Transform Sample File," located the "Columns =" code and aggregated the number of new columns to the existing "Columns =" value. I then went back into the Query file, went to the "Data" Applied Step and clicked on the test file. Voila, the new fields are there. Appears the takeaway, at least for me, is to update the total number of fields included in the file containing the largest number of fields before performing these steps...or at least getting them to work properly. Thanks so much for the help on this. Been wracking my brain all week.
thanks for sharing the update Chris! For CSV files you could also take out the nr of columns argument (sometimes works) -> see video here ua-cam.com/video/wuLnv3QJCHg/v-deo.html
Thank you! My situation called for 1 extra column and this worked however I got the extra column added to the first folder CSV file as well. The one that didn’t originally have the additional column. Did you get the same result?
Thank you! My situation called for 1 extra column and this worked however I got the extra column added to the first folder CSV file as well. The one that didn’t originally have the additional column. Did you get the same result?
Thank you! My situation called for 1 extra column and this worked however I got the extra column added to the first folder CSV file as well. The one that didn’t originally have the additional column. Did you get the same result?
Hi Bas. Your materials on Power BI are impressive. The vid format you've chosen is superb. Congratulations :) I know you are presenting/touch on many different aspects of PBI; from solving problems via Power Query through to designing the layout of stunning reports and... everything in between. But is there any division here? So that, say, one could browse through just the vids you've made on Power Query? Or the design? Or DAX? Or are these all just vids that one has to manually sift through to find topics related to some technique/technology/part? Thanks.
Thanks Bas for the nice video. This is what I was looking for but evaluation and Loading is taking a long time. Please let us know to reduce refresh time
Great tutorial. Thank you! I'm having a small issue, this works when dealing with a single query, but I noted that when you create additional queries and when the "Transform File" name changes to "Transfer File (2)", it returns an error - which looks something like: ----> ^^^^^^^^ How would one fix this?
This is very helpful. There was one issue with it that I'm wondering if it's user error, or if this method doesn't account for it. I changed the header in one of the Excel spreadsheets in the folder I connected to. When I refreshed my Power BI report, I got an error saying that column wasn't found. It's because I changed the name, but I was hoping it would work. I am going to continue troubleshooting and trying to find a way to fix that dynamically so that I don't have to go in and fix it manually each time a column name gets changed. Still a great trick though!
Looks like it was user error. I reordered columns and changed the data type which calls out the column specifically in the step, so I went to advanced editor and took that column name out of the steps. All good now. I don't really need the columns to be in any specific order, and I can change the data type in the report view, so I can probably remove those steps altogether so it doesn't happen in the future. Again, very helpful video, so thanks!
Useful, thank you! How would you deal with combining files from a folder where most files have the same number of columns, but for a couple they have one column LESS? I've got P&L outputs from Xero that are doing this - at the moment I'm fixing the source data (adding in the extra column) but it would be great to get the query to do this.
Thank you for sharing this! can this process be done after you have transformed the sample file with less columns than the new added file? Let’s say I have transformed the sample file by unpivot and group the data; however, a new file with extra columns has been added. Thanks!
Thank you very much Bas. Is it possible to show how to deal with changing empty rows within the same scenario, i.e., changing column names/numbers and different number of empty rows from month to month? Thank you.
I know you posted this some time ago, but i'll ask anyway, I just pasted the previous #"Removed Other Columns1" and expanded myself, but we both get the 'Transfrom File' Table.ExpandTableColumn(Custom1, "Transform File", Names ), and I'm not quite sure what it's referring to, if it's invoking the transform file function, what on? great video all same, I've only started following you recently, have you any on custom functions, my personal nightmare to get working how i want.
Amazing video - question : my transform file has actually a number in brackets next to it. So when apply your logic either with or without the number, I have a error that says ‘transform file unknown’. Would you have an idea on how to fix it ?
This is great Bass, much needed solution. I have this exact scenario with a small hiccup, for starter the files do not contain tables, so I am connecting to sheets, that is no problem however the issue is that all of them contains a blank row before columns names. When you create a list of tables to get the columns names 1 step after. When I do that I get just generic names (column 1, column 2, etc) because headers are not promoted. So, my question: How do you promote the headers inside each table before extracting the columns names of the combined files? I hope that makes sense. Thanks for your help!
Very helpful thanks a million for sharing! Find this technique is especially helpful instead of running loads of queries and appending each as it just hangs! :) Thanks again!
Thanks man, but I have a situation where I have files w different number of columns that need to be unpivoted first, which will let them all w 3 columns and finally I can combine them. Any ideas? Thanks!
Hi... great.. next coming up the next month you have also 5 column but then they have different header for the 'Product Cost' column.. instead of they type Product Code they didn't put the space 'ProcductCode'.. how do we combine both Product Code and ProductCode column so they merge together. - really appreciate the input
Just follow his steps to get the column names. Once everything is done you will end up with the 2 columns: Product Cost and ProductCode, then just merge them using the Merge Columns in the transform tab and that should do.
Just had a doubt what if the data we are getting from sharepoint is very large and complex transformation needs to be done as i am trying to add and create new custom column in power query instead of dax it is taking forever to do it what would be the best practices to do anything would help
new rows are automatically picked up when you refresh. You can just make a connection to a folder or files and thats it (you dont have to go through all of that effort shown in the video).
Hi - what if you are trying to combine txt files together with different columns, but you have to promote the headers first to determine the column names?
Thanks Brother. Your video is so clear and explained a complex operation in a clear way, your explanation on M code adjustment to accomodate the new columns is just like peeling the onion layer by layer; very organised and easy to understand and follow 👍👍👍👍; thank you so much for sharing your wisdom 🌹🌹
I have done this for 1 file. However, another excel work book in the same folder isnt showing when i try to select it on PI Nav, any ideas? (explained as simple as possible please)
I need some help. I am connected with two sharepoint folders in power bi. One folder contains the sales data and the other folder contains the costs, which means I get every month the new data in two different tables. What I need to do is adding a column with the win for every month but automated. How can i realize that?
When the number of files increases in the folder , everytime while refreshing data scans all the files which increases computation time....is there a way to scan only the latest files on top of the existing data ?
Everything seems to work until i get to the last step. I am working with 414 excel files pulling from a folder. It start to load the last applied step but then freezes on a file and then starts again and has bugged out several times for over an hour! I am not sure what to do hear. Is there another approach that can be applied in order for me to get all the data pulled in correctly?
This worked really well from my first set of data, but then i went to repeat the steps for a second set that I need to combine in the same query and it didn't work. I got this error: Expression.Error: The column 'Transform File' of the table wasn't found. Details: Transform File
Does this work on .XLSX file only? I'm trying to follow along with .CSV files (our primary data source) and it doesn't seem to bring in the extra columns...
Hi Jamie, this also works for .CSV files. However, you then still need to make the following changes: 1) go to the transform file function and remove Columns=4, 2) go to the transform sample file, source step, remove Columns=4,
@@HowtoPowerBI OK - I had to make the two transform file changes FIRST. The column names handling is a little wonky but at least new columns appear - renaming them as they pop up is a VERY MINOR issue compared to having to rebuild the query ever time the customer "helps" with a new, random, file format! :)
I have this problem with a .csv file which became extended with additional columns. The new columns will not be added to the data model. What can I do?
@@HowtoPowerBI I have an problem that having inconsistence header in multiple woeksheets and thoes header we have map then consolidate. Can you please guide on this problem statement
Hi Dhun, could you tell me which error you get at which step. Make sure to double check that you refer to #"Removed Other Columns1" in the expand step -> Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Headers)
Hi. I just tried this and I get the error message "Expression.Error: The column 'Transform File' of the table wasn't found. " any suggestions what is wrong? I note that my Transform File column has a (4) following it, is it this?
Hi Philip, yes that's it probably 🙂 . @ 5:00 you see the colum name for me is Transform File .. just check how it is named for you and adjust it accordingly.
@@ammosalmanac I have the same problem. Renaming does not work or using the name ‘transform file (4)´ does not work also. Shall I use ´ or ´´ or anything else ?
When i do it for one connection with excel files it worked great but i have two connections to a folder it wont work for another why it says tbasform file of the table wasn't found can any one tell me
You make it easy to follow, step by step and in simple basic terms. Thank you!
my brother and my friend.. I cant thank you enough.. this is exactly what i needed..
Wow Excellent video. I have searching for this everywhere. Finally I got it. Very easy to follow. Thanks much for uploading it.
Could you please share how we can combine files from a sharepoint folder please?
I have been looking for good advanced PowerQuery lessons. Your videos are just what I need. Thank you!
Vielen Dank! Very good information, just a question, how do it if the new column is in the middle or we don't know the exact position...?
Awesome Video. I searched everywhere for an easy to understand, quick solution. Great Job on the explanation
Just becoming your fan day by day! Amazing contents! Thank you so much!!!
Thanks for this .. you have no idea how much this helped me to one of my work assignment. i will subscribe and check all your videos
that's great to hear Suja!!! thx for checkn out my videos! 😀
Thank you so much. I combined more than 10 workbooks following your steps yes it worked but it is too slow on loading and refreshing. It can take almost an hour. what should I do because this technique had worked for me? Your guidance will be highly appreciated
Yes, even I am facing the same issue. Evaluation and Loading taking too much time
Very easy to understand. Thank you for the help
I will say that when I did this it turned all my columns into Text Columns. I had to manually go back to each column to turn it back to a currency or number column. Still very helpful
Thanks for the helpful video ,also could you help me with the question , get most recent files FROM FOLDER in Power BI | What to do when new columns don't show?
Hi Bas, great video, thank you but what to do if let’s say in the last 2 files you don’t only have 2 extra columns but the old column names have also been slightly changed compared to the other files + the order is not the same either anymore? Thank you!
Exactly the solution I was looking for. Cheers my buddy!
My question is, my PBI has Added Conditional Column that I've already created after I combined and transformed the data. There are at least 5 additional steps. Can I still go ahead and do this solution and still be able to use all my added steps?? or will it effect those steps?
Thank You!
You are Awesome... in both Content and Presentation.. Keep Sharing.. 👍....
This worked for me. HOWEVER, when I add my next file (or the next or next), those columns are not being added. I need to be able to combine every file that goes into the folder automatically.
This is a fantastic solution and perfectly presented. Kudos! Thought you solved my problem but this solution doesn't work with CSV's. Looking for the same but for CSV's.
When I perform the [Transform File] steps, I see that the new CSV is imported but still the new fields are not.
UPDATE:
I've been working on this all day and finally have it working properly. For some reason, as mentioned earlier, when I perform these steps, the source CSV does not include the new fields/columns from [Transform Data]. No matter what I tried, I could not get the test source file to show the new fields by performing just [Transform Data]. Because it was not displaying the new fields, I opened the Advanced Editor from "Transform Sample File," located the "Columns =" code and aggregated the number of new columns to the existing "Columns =" value.
I then went back into the Query file, went to the "Data" Applied Step and clicked on the test file. Voila, the new fields are there.
Appears the takeaway, at least for me, is to update the total number of fields included in the file containing the largest number of fields before performing these steps...or at least getting them to work properly.
Thanks so much for the help on this. Been wracking my brain all week.
thanks for sharing the update Chris! For CSV files you could also take out the nr of columns argument (sometimes works) -> see video here ua-cam.com/video/wuLnv3QJCHg/v-deo.html
@@HowtoPowerBI Sweet. Checking this out now as I'm adding more fields to the same query files. Much appreciated.
Thank you! My situation called for 1 extra column and this worked however I got the extra column added to the first folder CSV file as well. The one that didn’t originally have the additional column. Did you get the same result?
Thank you! My situation called for 1 extra column and this worked however I got the extra column added to the first folder CSV file as well. The one that didn’t originally have the additional column. Did you get the same result?
Thank you! My situation called for 1 extra column and this worked however I got the extra column added to the first folder CSV file as well. The one that didn’t originally have the additional column. Did you get the same result?
Great content, very easy to follow! Thank you!!
Hi , very helpful thanks 👍
Can you suggest any book for Power BI and DAX ?
Hi Sir, thank you for this solution I have been stuck for a long time to figure it out. Great relief.😅
Just discovered this channel and it does bring a lot of value. Good rhythm good content, congrats!
Awesome, thank you! Welcome 😃
Hi Bas. Your materials on Power BI are impressive. The vid format you've chosen is superb. Congratulations :) I know you are presenting/touch on many different aspects of PBI; from solving problems via Power Query through to designing the layout of stunning reports and... everything in between. But is there any division here? So that, say, one could browse through just the vids you've made on Power Query? Or the design? Or DAX? Or are these all just vids that one has to manually sift through to find topics related to some technique/technology/part? Thanks.
Thanks! There are some playlists (dax, design, shorts and challenges) already but i will see if there would be some better division
Thanks Bas for the nice video. This is what I was looking for but evaluation and Loading is taking a long time. Please let us know to reduce refresh time
Great tutorial. Thank you!
I'm having a small issue, this works when dealing with a single query, but I noted that when you create additional queries and when the "Transform File" name changes to "Transfer File (2)", it returns an error - which looks something like:
----> ^^^^^^^^
How would one fix this?
What a Bas. (Read boss)
Exactly what I was looking for. Thank you, kindly.
Glad to hear this Bailey! Thanks!
This is very helpful. There was one issue with it that I'm wondering if it's user error, or if this method doesn't account for it. I changed the header in one of the Excel spreadsheets in the folder I connected to. When I refreshed my Power BI report, I got an error saying that column wasn't found. It's because I changed the name, but I was hoping it would work. I am going to continue troubleshooting and trying to find a way to fix that dynamically so that I don't have to go in and fix it manually each time a column name gets changed. Still a great trick though!
Looks like it was user error. I reordered columns and changed the data type which calls out the column specifically in the step, so I went to advanced editor and took that column name out of the steps. All good now. I don't really need the columns to be in any specific order, and I can change the data type in the report view, so I can probably remove those steps altogether so it doesn't happen in the future. Again, very helpful video, so thanks!
Useful, thank you! How would you deal with combining files from a folder where most files have the same number of columns, but for a couple they have one column LESS? I've got P&L outputs from Xero that are doing this - at the moment I'm fixing the source data (adding in the extra column) but it would be great to get the query to do this.
Thank you for sharing this! can this process be done after you have transformed the sample file with less columns than the new added file? Let’s say I have transformed the sample file by unpivot and group the data; however, a new file with extra columns has been added. Thanks!
Always the best tutorials Bas! Thank you for all you do!
☺️thanks Steve! Much much appreciated:)
Thank you very much Bas. Is it possible to show how to deal with changing empty rows within the same scenario, i.e., changing column names/numbers and different number of empty rows from month to month? Thank you.
Does this also work when the position of the columns changes over the different files?
OMG... exactly what I was looking for.
Thanks a ton !!
I know you posted this some time ago, but i'll ask anyway, I just pasted the previous
#"Removed Other Columns1" and expanded myself, but we both get the 'Transfrom File'
Table.ExpandTableColumn(Custom1, "Transform File", Names ), and I'm not quite sure what it's referring to, if it's invoking the transform file function, what on? great video all same, I've only started following you recently, have you any on custom functions, my personal nightmare to get working how i want.
My power query doesnt automatically detect the first row as headers like yours does. Do you have any suggestions on how to rectify it?
greaT video, does this work even in case some files have less columns?i'm gonna try onn my own now! :)
Amazing video - question : my transform file has actually a number in brackets next to it. So when apply your logic either with or without the number, I have a error that says ‘transform file unknown’. Would you have an idea on how to fix it ?
This video helped me . Thank you so much.
This is great Bass, much needed solution. I have this exact scenario with a small hiccup, for starter the files do not contain tables, so I am connecting to sheets, that is no problem however the issue is that all of them contains a blank row before columns names. When you create a list of tables to get the columns names 1 step after. When I do that I get just generic names (column 1, column 2, etc) because headers are not promoted. So, my question: How do you promote the headers inside each table before extracting the columns names of the combined files? I hope that makes sense.
Thanks for your help!
Same question for me. Thanks!
Very helpful thanks a million for sharing! Find this technique is especially helpful instead of running loads of queries and appending each as it just hangs! :) Thanks again!
Great to hear! thx for watching Padraic! 😀
Your videos are a good reference point!
Hi very good Tks a lot. You have any multiple columns example, with multiple table with cartesian-product ?
thanks! not yet, will put it on my list for next videos ;)
Thanks man, but I have a situation where I have files w different number of columns that need to be unpivoted first, which will let them all w 3 columns and finally I can combine them. Any ideas? Thanks!
Hi... great.. next coming up the next month you have also 5 column but then they have different header for the 'Product Cost' column.. instead of they type Product Code they didn't put the space 'ProcductCode'.. how do we combine both Product Code and ProductCode column so they merge together. - really appreciate the input
Just follow his steps to get the column names. Once everything is done you will end up with the 2 columns: Product Cost and ProductCode, then just merge them using the Merge Columns in the transform tab and that should do.
Just had a doubt what if the data we are getting from sharepoint is very large and complex transformation needs to be done as i am trying to add and create new custom column in power query instead of dax it is taking forever to do it what would be the best practices to do anything would help
Excellent explanation as usual
Thanks a lot!
Transform from Sample File -> Change number of columns in the M-code
You have great talent. Bring this to Guy n Cube team. They will put up a video and send credit to you.
thank you! 🙂😊
Excellent Bas!!!
Awesome video, thank you.
Thanks! Can this be done with rows instead of columns?
new rows are automatically picked up when you refresh. You can just make a connection to a folder or files and thats it (you dont have to go through all of that effort shown in the video).
Hi - what if you are trying to combine txt files together with different columns, but you have to promote the headers first to determine the column names?
Bas, thank you so much for this :)
Excellent content, thank you
Amazing video Bas. Thank you so much
thx for watching Pedro, appreciate it!
Bas you the best, thank you 🙏
:) appreciate it!
what do we do for a sharepoint folder? Looks like my transform file is the one that's missing columns
Great one!
You could do something like this to:
List.Union (List.Transform( XYZ[Transform File], each Table.ColumnNames(_)) )
Awesome! I can't thank you enough!
happy to hear that Anh! thanks for watching :)
Brilliant!
Thank you Narendra 😀
Thanks Brother. Your video is so clear and explained a complex operation in a clear way, your explanation on M code adjustment to accomodate the new columns is just like peeling the onion layer by layer; very organised and easy to understand and follow 👍👍👍👍; thank you so much for sharing your wisdom 🌹🌹
hi Kebin :) thank you for the feedback, I appreciate your kind words very much!
I have done this for 1 file. However, another excel work book in the same folder isnt showing when i try to select it on PI Nav, any ideas? (explained as simple as possible please)
I need some help. I am connected with two sharepoint folders in power bi. One folder contains the sales data and the other folder contains the costs, which means I get every month the new data in two different tables. What I need to do is adding a column with the win for every month but automated. How can i realize that?
Amazing
When the number of files increases in the folder , everytime while refreshing data scans all the files which increases computation time....is there a way to scan only the latest files on top of the existing data ?
Everything seems to work until i get to the last step. I am working with 414 excel files pulling from a folder. It start to load the last applied step but then freezes on a file and then starts again and has bugged out several times for over an hour! I am not sure what to do hear. Is there another approach that can be applied in order for me to get all the data pulled in correctly?
This worked really well from my first set of data, but then i went to repeat the steps for a second set that I need to combine in the same query and it didn't work. I got this error: Expression.Error: The column 'Transform File' of the table wasn't found.
Details:
Transform File
Does this work on .XLSX file only? I'm trying to follow along with .CSV files (our primary data source) and it doesn't seem to bring in the extra columns...
Hi Jamie, this also works for .CSV files. However, you then still need to make the following changes:
1) go to the transform file function and remove Columns=4,
2) go to the transform sample file, source step, remove Columns=4,
@@HowtoPowerBI omg... i am so trying this again tomorrow at work. I hope you have a super hero cape ready in case I actually get this to work... 👍😁
@@fatbikejamie I'm sure you'll get it to work ;) ... super hero cape? :p ... maybe I can borrow one from you?
@@HowtoPowerBI OK - I had to make the two transform file changes FIRST. The column names handling is a little wonky but at least new columns appear - renaming them as they pop up is a VERY MINOR issue compared to having to rebuild the query ever time the customer "helps" with a new, random, file format! :)
Fantastic!
Many thanks! 😀
Thanks! Can this also be used if columns are removed? I would assume so.
Yes, absolutely 🙂
I have this problem with a .csv file which became extended with additional columns. The new columns will not be added to the data model. What can I do?
What if youre importing from an Excel file with multiple sheets? How do you invoke a function before expanding?
I explain how to do that here ua-cam.com/video/L-Q5Vz1YEQQ/v-deo.html
Bas how can I get this working with a SharePoint folder connection? When I try it this way the last step just hangs when loading and times out?
Hi i am on Removed other column step i adeed new step For seeing columns on tables but for new file additional.column not showing up pls help
What happens if we replace the first file to April month file in sample file?
Neat, thx Bas.
😊thanks
This way is working with every new column ???? Right
very helpful
thanks Joe! 😀
how to combine files with similar data but different column names? please help.
Need use sumifs between 2 date and other argument by query
Nice❤
😊
@@HowtoPowerBI I have an problem that having inconsistence header in multiple woeksheets and thoes header we have map then consolidate. Can you please guide on this problem statement
Hi, i am getting expression error with last column name as a new column added in source file. How can I manage it ?
Hi Dhun, could you tell me which error you get at which step. Make sure to double check that you refer to #"Removed Other Columns1" in the expand step -> Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Headers)
Thanks! Nice hack
thx Jimmy!
Hi. I just tried this and I get the error message "Expression.Error: The column 'Transform File' of the table wasn't found.
" any suggestions what is wrong? I note that my Transform File column has a (4) following it, is it this?
at the first created custom step of the video @ 5:00
Hi Philip, yes that's it probably 🙂 . @ 5:00 you see the colum name for me is Transform File .. just check how it is named for you and adjust it accordingly.
@@HowtoPowerBI I have not been able to get it to accept the name.
@@philipmorgan2302 Remame it in all the steps.
@@ammosalmanac I have the same problem. Renaming does not work or using the name ‘transform file (4)´ does not work also. Shall I use ´ or ´´ or anything else ?
When i do it for one connection with excel files it worked great but i have two connections to a folder it wont work for another why it says tbasform file of the table wasn't found can any one tell me
Because for the second one it is not called transformfile but transformfile1 or 2 … so therefore it cant find it
great
thank you Abubakr!😀
How do we change back the datatype?
just select all columns and choose detect data type un der transform 🙂
Bro you show us full video screen of powerbi desktop .. I think editing is bit different.....
thanks for the feedback
I like your style, shows always the relevant bits
Could you please send these files.
see download link in the description 😉
Too many ads :(
Sorry, thats how it is
Place change your type of presentation. Very hard n difficult to look into that small portion of content
Thx for the feedback