If you want to keep the rest of the columns while expanding, check part 2: kzitem.info/news/bejne/yYB8mJiGn3uWlII
@CAKimberlyLewis
2 жыл бұрын
I love how you break down the before (not optimal) and after (better) options! This truly helps me grasp the amazingness of these tips. Thank you!
@CurbalEN
2 жыл бұрын
Thanks Kimberly!
@torben87
2 жыл бұрын
An interesting additional gain from this method: HUGE speed gains. I had a query to a number of rather large CSV files that couldn't finish during a whole 8 hour night, using traditional folder query. Using the method in this video, it finished in less than 10 minutes! If I don't have to do a transpose, unpivot or other transformational changes to the individual files before appending them, I'll definitely stick with Curbal's approach from now on 😁
@CurbalEN
2 жыл бұрын
Fantastic!! 🥳🥳
@rosswaterston
2 жыл бұрын
A great simple video showing how to better deal with simple combines. There is no need to use a function if you don't need file-level transformations.
@CurbalEN
2 жыл бұрын
Thanks!
@rafalg8575
2 жыл бұрын
Thank you for instruction. Recently I had to combine over 30 Excel files but I spent some time to create common structure and normal expanding just worked fine however I agree way you showed is better if you have no chance to familiarize yourself with files.
@mahmoudalsaadawy
Жыл бұрын
Thanks a lot, very good. but what if i have another columns beside DATA column ?
@user-ms9hs2qg8o
Жыл бұрын
That's amazing. I do have a question: how can I do this keeping the name of source to extracking the year? I f i use Table. combine i can only keep the column with table. Any advice on this? Thank you 🙂
@1yyymmmddd
Жыл бұрын
Good trick but this way you get combined bare files. What if those files have headers? Power Query function alows you to make changes to Sample file that will be carried on every file in your list before combinng them. Agree that it is sometimes annoying when you lose columns or get file, that acts as the Sample file, deleted.
@CarlosPenha-ee1xv
5 ай бұрын
Thank you for video. how do you create the titles for the table?
@adambaker2463
2 жыл бұрын
Genius as always, been struggling with this for a while as im trying to combine transmittal files which in theory are supposed to be the same but of course humans being humans the columns are added or removed and headers changed! This should come in handy for such purposes! Graciass
@adambaker2463
2 жыл бұрын
Also a good expample of the importance not to just use the ootb functionality too..
@CurbalEN
2 жыл бұрын
Thanks!!
@PeteMackay
2 жыл бұрын
Brilliant! cleans up the helper query overload you get sometimes too. Thanks.
@CurbalEN
2 жыл бұрын
😃
@waysilentbob6976
2 жыл бұрын
I'm glad I spotted this before I started importing user created xlsx files! :D Thanks, Curbal!
@CurbalEN
2 жыл бұрын
Pleasure!!
@richardpearcephotography599
10 ай бұрын
The other issue you have with the std method is when the sample file name changes or is deleted from the folder.
@soniagonzalez4897
2 жыл бұрын
Hello Many thanks for your video. Just help me with one of my concers. It worked great with Excel. However I have other files in CSV, and using excel.workbook it give me an error in the custom column. Can you please show, how to do same with CSV. files? Many thanks
@rdaleprice7184
Жыл бұрын
Hello Ruth, thank you for another great video! If there are multiple worksheets in the Excel workbook, how do we specify a specific worksheet or table within TABLE combine?
@colintate
2 жыл бұрын
This is fantastic. Thanks for showing how to do it! (I look forward to revamping my reports and futureproofing accordingly!)
@CurbalEN
2 жыл бұрын
🎉🎉
@daryllynch998
2 жыл бұрын
Great tip Ruth. I wouldn't recommend your approach though because loses the Filename or File Index. I normally like the source file to be identifiable for auditing purposes. Can I suggest adding the following steps after Expanded Custom. This would add the headings and row numbers before expanding the data #"Promote Headers" = Table.TransformColumns(#"Expanded Custom", {"Data", each Table.PromoteHeaders( _ ) } ) #"Add Rows Numbers" = Table.TransformColumns(#"Promote Headers", {"Data", each Table.AddIndexColumn( _ , "Row Index", 1, 1) } )
@CurbalEN
2 жыл бұрын
You can keep the file name by not removing it.
@J0schua
2 жыл бұрын
I have the same issue when loosing the file names but do not quite understand how your extra lines are to be integrated, Daryl. Could you post the whole code assuming that my data is not a excel table but rather "normal" data (Export from DAX Studio) ? Thanks 🙂
@J0schua
2 жыл бұрын
@@CurbalEN actually the table.combine does only keep the column you specify. All file information is lost, which is a problem for me because I am dealing with separate Versions of the same data set in each file and filename = Version ...Maybe you could explain how to get around that elegantly as well ;-) Thanks for the good work !!
@CurbalEN
2 жыл бұрын
Yes, I have a part 2 explaining all that scheduled for next week. Stay tuned!
@lucassardo
Жыл бұрын
You're the best, much easir, fast and cleaner !!
@shivanimittal8397
7 ай бұрын
Hello Ruth, I have a question: what if the column names are the same but they are arranged in different orders in each file
@UlyssesHaq
2 жыл бұрын
PoderBI!! The Power of this technique is off the charts 🙌🏻🙌🏻🙌🏻🤓
@CurbalEN
2 жыл бұрын
Si, podemos 🙌🙌
@wayneedmondson1065
2 жыл бұрын
Hi Ruth. Table.Combine is a great trick! Thanks for sharing this technique. Thumbs up!!
@raitup00
2 жыл бұрын
Excelente! Siempre se aprende algo nuevo y en un paso tan simple como la importación de archivos. Saludos!
@rashidkhan8161
2 жыл бұрын
Hello, I have 2 excel files Jan-22 & Feb-22, each file contain 5 sheets sales, product , region, date & category, when I get 1 file and transform it all 5 sheets showing 5 queries separately, how can I combine feb-22 file in it and make it dynamically. Thanx
@IBRAOMER1
4 ай бұрын
Hey CurBal, I've been trying to retrieve the folder path for a folder in my OneDrive Personal account. I need it for Excel Power Query to append files from that folder. Despite Googling and watching KZitem tutorials, I'm still struggling to find the folder path. Any suggestions?
@Spagetovich
2 ай бұрын
you are focusing on showy; but you have talent.
@1yyymmmddd
2 жыл бұрын
This is quite hypothetical example to my understanding. If you have additional columns in File3, but not in the rest of files, most probably you do not need those columns. In my experience more frequent example is when additional columns appear in the newest file and those columns need ot be preserved for the future ones. In such case it is easier to deal with the situation by modifying Transform Sample File query that Power Query creates automatically for you. You just add additional steps 1) Filter your files by Date Modified descending, 2) Keep top 1 row 3) Expand Content 4) Promote headers This way you will always have the column structure of the newest file in the folder.
@CurbalEN
2 жыл бұрын
True but newest does not imply representative specially if you are saving multiple files in One day and refresh only daily.
@ziplesswonder
2 жыл бұрын
What a valuable 5 minutes that was!
@CurbalEN
2 жыл бұрын
🎉🎉
@irinamelamud5930
Жыл бұрын
Hello. I have a question. When I get into the point to Load the file, column "Content" is missing. Is any way you may help on how to fix it? Thanks a lot
@kumarprabhu
2 жыл бұрын
My data is there in 2nd sheet. When I am trying to implement the same by manual expand it is pulling data from all the sheet..how do i restrict the data for only sheet 2
@lilyflowyyy
10 ай бұрын
Hi! I'm using the getting data from folders, and the excel file have same data structure. However, one file has the column name of YYMMMM while the other is REV_DT, but both actually the same. Should I just rename the column in the original file? Im trying to change the REV_DT to YYMMMM, but the not sure how to rename it once I combine into the PowerBI from a folder. Hope you can help!
@cicciogrecia
2 жыл бұрын
That thing in Power Query when you type the first letters of a function, you get the name, you enter it and it does not replace the text you have already typed and you have to delete it every time, is sooooo frustrating!!!
@CurbalEN
2 жыл бұрын
I know 🤦♀️🤦♀️
@AnT508
2 жыл бұрын
Related: when you autocomplete, and Power Query "helpfully" removes the subsequent function reference so you have to retype that again...
@CurbalEN
2 жыл бұрын
If you write the parentheses before you right the function it will not get removed :)
@AnT508
2 жыл бұрын
@@CurbalEN Yeah, I noticed that too! But I always forget... Ah, well, it gives me a chance to practice my "vocabulary" :D
@CurbalEN
2 жыл бұрын
😂😂 I forget too!
@muzammilkarajgi7507
2 жыл бұрын
Can we connect 2 Power bi files as a link (button) in another Power bi file ?
@AkshayKumar-vd5wn
2 жыл бұрын
Thanks for the video Ruth. Is there a way you can upload multiple files from a folder into powerBI but not combine them? Instead upload them as seperate tables?
@vagmine3077
2 жыл бұрын
I have .csv files of data, when i was load these files from excel to Power bi showing some error. (data showing in coding)
@AgulloBernat
2 жыл бұрын
nice one as long as you don't need any modifications on your individual excel files before combining
@CurbalEN
2 жыл бұрын
Yes, in that case you need a function :)
@mohammadthalha6750
Жыл бұрын
How would you do it for csv files instead of xls files. I have been trying to replicate the same but with no success
@adambaker2463
2 жыл бұрын
My problem is also some tables will go up to 6 pages but most will be on 1..how do i manage to bring in the data without having to find a table with 1,2,3,4,5,6 tables and bring them all in as the first file example and append them all? Maybe this technique helps for that too? I guess i will try and see..
@davidjosevarelagarcia7011
2 жыл бұрын
great video, i have a question , i have several file from different customers ( 12 ) each of file have different columns 23,22,20,19. the information that a need could be in the columns 13, the next month comes in the column 15 , etc. How can I unite all these files taking into account that the information I need is in different columns in each month? any advice? .
@matthewgoldring
2 жыл бұрын
Table.Combine is a neat trick, thanks! Although how would you then use row 9 as your column headers?
@CurbalEN
2 жыл бұрын
Yes, that is the weakness of this method, but at least you will see the column1 header and will be able to fix it. The other ways will hide the column totally.
@DataHacker
2 жыл бұрын
@Curbal I gues you can try promoting first raw as a header in a custom function. Then, when you are combining files, each has correct headers. A neat trick though, I will use it for sure
@joetynan123
2 жыл бұрын
you can use the expand as normal but feed in the column names first ColNames = List.Distinct(List.Combine(Table.AddColumn(Source1 ,"All",each Table.ColumnNames([Headers]))[All])), Expand = Table.SelectColumns(Table.Combine(Source1[Headers]),Source1 )
@CurbalEN
2 жыл бұрын
Using custom functions will work, but if you do know how (a bit adavance technique), then this method will have this weakness :(
@CurbalEN
2 жыл бұрын
Awesome, thanks!
@cherianiype
2 жыл бұрын
Thank you again Ruth for this! terrific stuff!
@cristianprifti
2 жыл бұрын
If you would have had two columns like File Name and Data, would you have written the last syntax different to keep also the File Name column ?
@CurbalEN
2 жыл бұрын
Just don’t delete it on the remove other columns step and you will be able to keep it
@justinmblue
Жыл бұрын
Love it, please slow down
@soy34mb
2 жыл бұрын
Hola Ruth, la forma en que yo lo hago creo que es mucho más sencilla, llamo los archivos del folder, luego los ordenó de más nuevo al más antiguo por fecha de creación. Esto hace que el "first file" sea el más reciente el cual es el que tiene la columna nueva y con esto se resuelve el problema. Siempre me ha funcionado bien ese truco, pero es bueno saber que hay otras formas de hacerlo. Muchas gracias por compartir. Saludos desde Nicaragua. Jimmy.
@CurbalEN
2 жыл бұрын
Y si llegan más de un archivo y el último no es el representativo?
@soy34mb
2 жыл бұрын
@@CurbalEN si entiendo la duda, pero normalmente el último archivo suele ser el representativo de todos los subsecuentes, por lo menos es lo que he visto en mi experiencia, pero si, en caso que no sea así siempre, ya tengo la solución gracias a ti 😀
@martyc5674
2 жыл бұрын
Thanks Ruth- great explanation as always.
@EricaDyson
2 жыл бұрын
Well my my.. I'm actually doing things right first time.. but I think that's only because you showed use a long time ago about Excel. Workbook!
@EricaDyson
2 жыл бұрын
showed us!
@CurbalEN
2 жыл бұрын
🎉🎉🎉
@gospelmoto2833
Жыл бұрын
You just nailed what I need. Thanks!
@CurbalEN
Жыл бұрын
🥳
@kennethstephani692
2 жыл бұрын
Great video, Ruth!!
@CurbalEN
2 жыл бұрын
Thanks!
@sherinthomas801
2 жыл бұрын
Nifty trick Ruth! Is it taxing computationally when working with large datasets with many fields? Also I would love if you could do a video on setting up incremental refresh for data from a sharepoint folder.
@CurbalEN
2 жыл бұрын
Will do! :)
@samratmajumder9860
2 жыл бұрын
Thanks for the nice content. While importing multiple set of files from Sharepoint, I am facing issues. The pbix file is not opening after I imported around 10 set of files each category having 20 files (so in total 200+ files) the content of those excel files are very small, hardly 200 rows in each files, however it seems transforming too many files from Sharepoint creating issue. Any idea how to resolve that. TIA
@HH-du4rc
2 жыл бұрын
I have a JSON file that only loads the values and not the field name that goes with it.. for a very short example, I have: "carrier":"29" within the JSON string. I want "carrier" to be the column heading or the field name but I am only getting the "29". Notepad ++ formats it nicely but our business partners want us to load the daily JSON file in Power BI.. any ideas how to get the field names? I watched several videos but none seem to cover this. Thanks
@brianspiller9075
2 жыл бұрын
Questions. What happens with Other Sheets in the file? Is [Data] going to pull all sheets? The Header for NOTES is not in the same row as other Headers. What technique are you going to use to get that header name to the field Name since we wouldn't be able to "Use first row as Headers"?
@CurbalEN
2 жыл бұрын
You can do this with functions too so you can pull any data you want. Use Excel.Workbook([Content], true) and expand all metadata in [Custom]
@mehdihammadi6145
2 жыл бұрын
Very interesting. Thanks for sharing
@VladMZ
2 жыл бұрын
Very good! Neat trick to remember.
@pat3755
2 жыл бұрын
If you do that and use first column as headers, the additional column will just be column 5? Or is there a more efficient way than first column as headers
@CurbalEN
2 жыл бұрын
Excel.Workbook([Content], true) will promote the column headers 🎉
@ewad
2 жыл бұрын
Very neat :) Thank you Ruth.
@smartexcelsolutions3024
2 жыл бұрын
Have you checked how this method compares with the traditional method in terms of performance? One would assume that it should perform better since there is no file level transformations.
@CurbalEN
2 жыл бұрын
No, I haven’t , but please let us know if you do
@jansjoblom1602
2 жыл бұрын
Was wondering if its the sama as i have done this using table.combine with a empty table. That way I always specify to an empty table cols i need and combine it do data table for securing I have all cols
@CurbalEN
2 жыл бұрын
Interesting, does that technique force all columns including new ones? Have to test it, thanks!!
@henrifanda4784
2 жыл бұрын
great tips
@ukaszobiedzinski5703
2 жыл бұрын
Hi. Great video. Do you know how to do the same thing as you did here, but on CSV files?
Can you help me with below scenario? Is there any possibility to get the data from excel based on worksheet property(sheet8) name instead of actual name(cw 02). I have an excel workbook which will get updated one in particular sheet with sheet name changes
@ianuplask7738
2 жыл бұрын
if it’s the only sheet in the workbook you can skip reference to the sheet so the query won’t break every time the sheet name changes
@facundo70
2 жыл бұрын
so helpful! THX!
@CurbalEN
2 жыл бұрын
😃😃
@culpritdesign
2 жыл бұрын
Great content!
@CurbalEN
2 жыл бұрын
Thanks!
@OsamaHassanin
2 жыл бұрын
I think the problem will be in the header names of the new columns. When you make the 1st row as header, the new column will be under default name like column5. Any easy way suggestions.
@CurbalEN
2 жыл бұрын
Yes, that is the weakness of this method, but at least you will see the column1 header and will be able to fix it. The other ways will hide the column totally.
@@CurbalEN Yes, just promote before you expand. I normally use something like this: Let Source = Folder.Files(FP ), //Change source if needed #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")), #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each Excel.Workbook([Content])), RemothCol = Table.SelectColumns(#"Added Custom",{"Custom"}), Custom1 = Table.Combine(RemothCol [Custom]), #"Filtered Rows1" = Table.SelectRows(Custom1, each ([Kind] = "Sheet")),// or Table if you would prefer #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1" ,{"Data"}), PromHead = Table.TransformColumns(#"Expanded Custom", {"Data", each Table.PromoteHeaders( _ ) } ), // ColNames = Table.ColumnNames(PromHead {0}[Data]), //This is looking at the column names from the most recent file - change if you want names from all ColNames = List.Distinct(List.Combine(Table.AddColumn(PromHead ,"All",each Table.ColumnNames([Data]))[All])), RemoveCols= List.Select(ColNames,each not Text.StartsWith(_,"Column")), Expand= Table.SelectColumns(Table.Combine(Source1[Headers]),RemoveCols) in Expand
@OsamaHassanin
2 жыл бұрын
@@daryllynch998 Excellent, Thanks for the solution. only one Question Why adding the Index? it's working fine without it.
@CurbalEN
2 жыл бұрын
Excel.Workbook([Content], true) will promote the column headers 🎉
@amazon5665
Жыл бұрын
how u combined?
@tommynguyen4253
2 жыл бұрын
is there anyway to select a few sheets only? This is expanding all sheets.
@tommynguyen4253
2 жыл бұрын
Ah, seems that I need to manually put the "Item" in the expand Data steps; then filter the name from there: #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data", "Item"}, {"Data", "Item"}),
@CurbalEN
2 жыл бұрын
Create a function for that :)
@madeleineocarroll2063
2 жыл бұрын
When I create the custom column using Excel.CurrentWorkbook ([Content]), the resulting column is 100% Errors.
@CurbalEN
2 жыл бұрын
Maybe it is a csv?
@madeleineocarroll2063
2 жыл бұрын
@@CurbalENHi Ruth! Thank you for your response. They are all Excel files though. 36 of them.
@CurbalEN
2 жыл бұрын
Could you post the details to the power bi community , best place to troubleshoot and get help :)
@motoray1973
2 жыл бұрын
How do you then make use of the extra column automatically? I had this exact scenario recently. I was using Power Pivot instead of Power BI, though. To make use of the extra column, I still had to manually add it to the Pivot Table, so I stopped looking for something like this and just kept a sample file with all the columns in the folder and forced Power Query to look at it. In my scenario I had control over what was going into the folder though.
@CurbalEN
2 жыл бұрын
Mmmm it doesn’t get added automatically to the pívot? I was expecting that to happen
@motoray1973
2 жыл бұрын
@@CurbalEN At least not in the version of Power Pivot I'm using--we're not on the latest version of Excel.
@CurbalEN
2 жыл бұрын
Bummer… then you have to do it manually, but at least it is better than not getting them
@siriagcabrero2976
2 жыл бұрын
I would like a vídeo about Big data speed with poder query and power.pivot
@CurbalEN
2 жыл бұрын
For big data you need to use live connection or other technologies
@jennyfu2834
2 жыл бұрын
Legend!! thank you for this!!
@CurbalEN
2 жыл бұрын
🎉🎉🎉
@lotter3008
2 жыл бұрын
thank you so much as usual!
@CurbalEN
2 жыл бұрын
🎉
@kasbu
2 жыл бұрын
Hi, would this still work if the "new" column appears in the middle of the table? Wouldn't this mix up column content? I would take a guess that using the default sample file method is a way to mitigate this as you could match column content by column names even if the order has changed.
@CurbalEN
2 жыл бұрын
The default sample file method will not find new columns
@kasbu
2 жыл бұрын
@@CurbalEN No, it will not add new columns, however, I think that this method would mess up the combined table if one of the existing columns from the middle of the table would disappear or if the column order in one of the files would change. The content would not stack up correctly in the final table, column content would be mixed up. If there is a transformation file and if you have not identified columns by name in the sample file (in the code), all files would be combined by column names correctly. So the question is - are you more worried about adding new columns or could you also expect to lose some of the existing columns or their order could change.
@CurbalEN
2 жыл бұрын
Excel.Workbook([Content], true) will promote the column headers and combine them correctly
@kasbu
2 жыл бұрын
@@CurbalEN ahhh, cool, didn't know about the extra variable there. Thank you, will try this out! :)
@fernandotapia6810
2 жыл бұрын
life hack!! as always thanks Ruth
@CurbalEN
2 жыл бұрын
☺️
@visheshjjain
2 жыл бұрын
That’s a neat trick! 👍🏻
@CurbalEN
2 жыл бұрын
👏
@galaxyangelzz
2 жыл бұрын
only if you dont have to transform input-file
@CurbalEN
2 жыл бұрын
Exactly, functions are for that case :)
@stevegosselin5788
2 жыл бұрын
I share the pain at 2:47 😅
@CurbalEN
2 жыл бұрын
Shared pain is less painful ;)
@santhoshr7502
2 жыл бұрын
👍👍👍
@CurbalEN
2 жыл бұрын
🎉
@abhijeetghosh27
Жыл бұрын
👌👌👌👌
@CurbalEN
Жыл бұрын
🥳
@gef24
Жыл бұрын
I'm pretty sure you know it, STOP TYPING THE DOT. :)
Пікірлер: 171