No video :(

COMBINE FROM FOLDER in Power BI | What to do when new columns don't show?!

Подписаться 225 тыс.

Просмотров 30 тыс.

50% 1

Make combine from folder more future proof by automatically incorporating new columns added to any of the files in a folder.
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:48 When combine from folders fails
1:37 Combine from folder - Standard scenario
3:30 Adding new files with new columns
4:13 Adjust combine from folder so that new columns are included
09:24 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on RU-vid: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX

Опубликовано:

6 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 138

@Zakehonest7 2 года назад

You make it easy to follow, step by step and in simple basic terms. Thank you!

@SolidSnake59 2 года назад

I have been looking for good advanced PowerQuery lessons. Your videos are just what I need. Thank you!

@lh7617 2 года назад

Awesome Video. I searched everywhere for an easy to understand, quick solution. Great Job on the explanation

@TimePasser Год назад

my brother and my friend.. I cant thank you enough.. this is exactly what i needed..

@BernatAgulloRosello 3 года назад

Just discovered this channel and it does bring a lot of value. Good rhythm good content, congrats!

@HowtoPowerBI 3 года назад

Awesome, thank you! Welcome 😃

@istiaqjahanshuvo5041 7 месяцев назад

Just becoming your fan day by day! Amazing contents! Thank you so much!!!

@ashishjuneja6574 2 года назад

OMG... exactly what I was looking for. Thanks a ton !!

@leticiacoelho8231 10 месяцев назад

Great content, very easy to follow! Thank you!!

@kartik-wi2jv 3 года назад

You are Awesome... in both Content and Presentation.. Keep Sharing.. 👍....

@sujawahab3933 2 года назад

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

@HowtoPowerBI 2 года назад

that's great to hear Suja!!! thx for checkn out my videos! 😀

@stevegisselman765 2 года назад

Always the best tutorials Bas! Thank you for all you do!

@HowtoPowerBI 2 года назад

☺️thanks Steve! Much much appreciated:)

@onkartanwade2077 9 месяцев назад

Hi Sir, thank you for this solution I have been stuck for a long time to figure it out. Great relief.😅

@padraichayes3836 2 года назад

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!

@HowtoPowerBI 2 года назад

Great to hear! thx for watching Padraic! 😀

@codewalters 11 месяцев назад

This video helped me . Thank you so much.

@narayanankannan3628 11 месяцев назад

Your videos are a good reference point!

@tubeitz 2 года назад

Excellent content, thank you

@kenharp 2 года назад

Awesome video, thank you.

@joaorataoo 2 года назад

Excellent Bas!!!

@PedroCabraldaCamara 3 года назад

Amazing video Bas. Thank you so much

@HowtoPowerBI 3 года назад

thx for watching Pedro, appreciate it!

@mubangizijulius4303 2 года назад

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

@rameshu3091 11 месяцев назад

Yes, even I am facing the same issue. Evaluation and Loading taking too much time

@BaileyAirsoft Год назад

What a Bas. (Read boss) Exactly what I was looking for. Thank you, kindly.

@HowtoPowerBI Год назад

Glad to hear this Bailey! Thanks!

@SaniGarba 2 года назад

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.

@RogerioFelixrdf Год назад

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...?

@anhnguyen-jv5vq 2 года назад

Awesome! I can't thank you enough!

@HowtoPowerBI 2 года назад

happy to hear that Anh! thanks for watching :)

@michaeltadesse3678 2 года назад

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?

@richardsonwg1 6 месяцев назад

Bas, thank you so much for this :)

@zsuzsagecs7831 Год назад

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!

@p3trolhead 2 года назад

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!

@lenzypetty9371 Год назад

Excellent explanation as usual

@HowtoPowerBI Год назад

Thanks a lot!

@Metzanine 2 года назад

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.

@narendralv6379 3 года назад

Brilliant!

@HowtoPowerBI 3 года назад

Thank you Narendra 😀

@mukeshved Год назад

Hi , very helpful thanks 👍 Can you suggest any book for Power BI and DAX ?

@Mareeyaaaaa 2 года назад

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!

@anirudhrao3e939 2 года назад

Fantastic!

@HowtoPowerBI 2 года назад

Many thanks! 😀

@fabianpetersen2452 2 года назад

Bas you the best, thank you 🙏

@HowtoPowerBI Год назад

:) appreciate it!

@Unbox747 2 года назад

Amazing

@xodrinker 3 года назад

Neat, thx Bas.

@HowtoPowerBI 3 года назад

😊thanks

@rossbailey1959 2 года назад

Clever, very clever

@PythonWithJoe 3 года назад

very helpful

@HowtoPowerBI 3 года назад

thanks Joe! 😀

@jesseemling4817 Год назад

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.

@Prapti706 2 года назад

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?

@alexpetkunas9669 Год назад

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!

@alexpetkunas9669 Год назад

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!

@dariuszspiewak5624 Год назад

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.

@HowtoPowerBI Год назад

Thanks! There are some playlists (dax, design, shorts and challenges) already but i will see if there would be some better division

@rameshu3091 11 месяцев назад

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

@maurobassan9416 2 года назад

greaT video, does this work even in case some files have less columns?i'm gonna try onn my own now! :)

@pierre-nicolasbissonnet4198 Год назад

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 ?

@Karthikediga 3 года назад

You have great talent. Bring this to Guy n Cube team. They will put up a video and send credit to you.

@HowtoPowerBI 3 года назад

thank you! 🙂😊

@ChrisSmithFW 2 года назад

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.

@HowtoPowerBI 2 года назад

thanks for sharing the update Chris! For CSV files you could also take out the nr of columns argument (sometimes works) -> see video here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-wuLnv3QJCHg.html

@ChrisSmithFW 2 года назад

@@HowtoPowerBI Sweet. Checking this out now as I'm adding more fields to the same query files. Much appreciated.

@jc1086 Год назад

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?

@jc1086 Год назад

@danielvolgan 2 года назад

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!

@soy34mb 3 года назад

Thanks! Nice hack

@HowtoPowerBI 3 года назад

thx Jimmy!

@DinoAMAntunes 3 года назад

Hi very good Tks a lot. You have any multiple columns example, with multiple table with cartesian-product ?

@HowtoPowerBI 3 года назад

thanks! not yet, will put it on my list for next videos ;)

@Alan.DL7 2 года назад

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!

@Prapti706 2 года назад

Same question for me. Thanks!

@SoNonWoo 2 года назад

what do we do for a sharepoint folder? Looks like my transform file is the one that's missing columns

@sagenodes 6 месяцев назад

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?

@bravucod 3 года назад

Thanks! Can this be done with rows instead of columns?

@HowtoPowerBI 3 года назад

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).

@moustafaibrahim1485 2 года назад

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?

@tjeerdvandermeulen2123 16 дней назад

Does this also work when the position of the columns changes over the different files?

@tinyentropy 2 года назад

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?

@abhishekawari919 Год назад

Nice❤

@HowtoPowerBI Год назад

😊

@abhishekawari919 Год назад

@@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

@abubakrsaadmakyhoun7535 3 года назад

great

@HowtoPowerBI 3 года назад

thank you Abubakr!😀

@williamarthur4801 Год назад

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.

@SamehRSameh 3 года назад

Need use sumifs between 2 date and other argument by query

@yashaswinivijay 4 месяца назад

My power query doesnt automatically detect the first row as headers like yours does. Do you have any suggestions on how to rectify it?

@Bob_flint 2 года назад

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

@Alan.DL7 2 года назад

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.

@ravi19990 2 года назад

What happens if we replace the first file to April month file in sample file?

@mirrrvelll5164 Год назад

Great one! You could do something like this to: List.Union (List.Transform( XYZ[Transform File], each Table.ColumnNames(_)) )

@vishnuvardanatmakuri 2 года назад

how to combine files with similar data but different column names? please help.

@kebincui 2 года назад

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 🌹🌹

@HowtoPowerBI 2 года назад

hi Kebin :) thank you for the feedback, I appreciate your kind words very much!

@jeffrhodes264 Год назад

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?

@scottbaxter987 2 года назад

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

@dhruvkapoor767 10 месяцев назад

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

@EvilClxwn Месяц назад

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?

@marcuscrutchfield3992 2 года назад

What if youre importing from an Excel file with multiple sheets? How do you invoke a function before expanding?

@HowtoPowerBI 2 года назад

I explain how to do that here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-L-Q5Vz1YEQQ.html

@fatbikejamie 3 года назад

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...

@HowtoPowerBI 3 года назад

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,

@fatbikejamie 3 года назад

@@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... 👍😁

@HowtoPowerBI 3 года назад

@@fatbikejamie I'm sure you'll get it to work ;) ... super hero cape? :p ... maybe I can borrow one from you?

@fatbikejamie 3 года назад

@@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! :)

@MrDhunpagla 3 года назад

Hi, i am getting expression error with last column name as a new column added in source file. How can I manage it ?

@HowtoPowerBI 3 года назад

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)

@user-fd8hy4id3e 4 месяца назад

Transform from Sample File -> Change number of columns in the M-code

@monster3bod 6 месяцев назад

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

@HowtoPowerBI 6 месяцев назад

Because for the second one it is not called transformfile but transformfile1 or 2 … so therefore it cant find it

@IconicLemon 3 года назад

Thanks! Can this also be used if columns are removed? I would assume so.

@HowtoPowerBI 3 года назад

Yes, absolutely 🙂

@junaidmohammed280 3 года назад

How do we change back the datatype?

@HowtoPowerBI 3 года назад

just select all columns and choose detect data type un der transform 🙂

@monster3bod 7 месяцев назад

This way is working with every new column ???? Right

@philipmorgan2302 3 года назад

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?

@philipmorgan2302 3 года назад

at the first created custom step of the video @ 5:00

@HowtoPowerBI 3 года назад

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.

@philipmorgan2302 3 года назад

@@HowtoPowerBI I have not been able to get it to accept the name.

@ammosalmanac 2 года назад

@@philipmorgan2302 Remame it in all the steps.

@pierre-nicolasbissonnet4198 Год назад

@@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 ?