A cleaner, better and faster way to bulk import files in Power BI: Csv.Document and Excel.Worbook

Рет қаралды 37,654

5 жыл бұрын

You heard it on the title, just watch and enjoy :)
#curbal #powerbi #powerquery
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!
www.amazon.com/shop/curbal
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB

Пікірлер: 207

@learnspreadsheets 5 жыл бұрын

It works with pdfs too. =Pdf.Tables([Content])

@mattmiller07 5 жыл бұрын

Thanks David. That's a great tip. I hadn't explored that, but it will come in handy for sure.

@learnspreadsheets 5 жыл бұрын

As soon as the pdf connector came in I tried it, and it works!

@CurbalEN 5 жыл бұрын

Thanks for sharing, didn’t know that either :) /Ruth

@CurbalEN 5 жыл бұрын

Pinned for more visibility and a million thanks for sharing :) /Ruth

@learnspreadsheets 5 жыл бұрын

Thanks! I use “from folder” even when it’s just individual files. It’s not sensitive to file name changes in the same way! Probably 80% of my data sources use “from folder”

@implementedgmbh 5 жыл бұрын

Hello Ruth, just tried your tip with a Sharepoint Folder and it is also working! Perfect! So we save a lot of maintenance efforts. Before we had one query for every csv-file, now we have only one query for the whole folder! Just one tip - depending on the delimiter of the csv-files: We expanded the Csv.Docuent Statement as follows: Csv.Document([Content], [Delimiter=";"]). We had difficulties to import our csv-Files because the Standard delimiter in the CSV.Document-Statement is "," In our csv-Files we have a ";". After adding the [Delimiter = ";"] statement it works perfect! Thanks again!!!!

@CurbalEN 5 жыл бұрын

Yeah!!! Music to my ears!! I will do a follow up video on the topic hopefully on Monday, don’t miss it :) /Ruth

@implementedgmbh 5 жыл бұрын

@@CurbalEN Definitly not! Happy to see you (not only!) on Monday! :-)

@CurbalEN 5 жыл бұрын

Thanks 😊 /Ruth

@PedroHenrique-kl3ww 2 жыл бұрын

Man, two years later and you saved me! Thanks a lot for this tip!

@implementedgmbh 2 жыл бұрын

@@PedroHenrique-kl3ww 😃 you made my day!

@LIST27 4 жыл бұрын

Hi Ruth, you are amazing ! Thanks to the lockdown I've more time now to watch your very useful videos. Your tip to avoid all those helper queries and functions will keep my Excel files and query dependencies definitively more comprehensive. In some instances I need to define a specific deliminiter or coding table. After a while I figured out how I can add this in the Csv.Document([Content] step : = Table.AddColumn(#"Previous step", "Custom", each Csv.Document([Content], [Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]))

@prempramodbhardwaj2624 2 жыл бұрын

Thank you Ruth! Great! I just start learning Power BI and got great help from this video to consolidate my day to day task report!!

@lenzypetty1097 4 жыл бұрын

I am grateful again to Ruth for reviewing a great topic. Although, I prefer to use the "Combine & Transform", and then use the "Transform Sample File" method, which will apply transformations to all of the files in the folder before they are Combined, such as removing rows, columns, unpivoting, etc.

@NorbertoVeraReatigaNVR 5 жыл бұрын

Cada segundo del vídeo hace honor al titulo!!! Siempre el mejor canal de aprendizaje en Power BI!!! Gracias Ruth!

@CurbalEN 5 жыл бұрын

😊😊 ahhh, gracias !! /Ruth

@mariaalcala5159 3 жыл бұрын

The best video ever! Thanks a lot. You can imagine how much I was struggling with changes in the sample file and the the preview of all files! It was really annoying! I used your solution and it worked perfectly!

@CurbalEN 3 жыл бұрын

🥳🥳🥳

@rdaleprice7184 4 жыл бұрын

One suggestion, when you go to filter off the duplicate headers, you can go to the drop-down arrow for the column, then select the Text Filter, Select "Does Not Equal", then key in the "Column Name".

@mattmiller07 5 жыл бұрын

Ruth, you've done it again. Great tip. To add on to what you showed...In cases where you don't have a date or year in the file's title, you could also reference the Date Modified metadata to create a file date. Obviously, that's tricky if the modified date doesn't reflect the actual date of the underlying info, but I use that column often. Just wanted to mention that in case it helps someone else.

@CurbalEN 5 жыл бұрын

Yey!! That is a great tip, thanks for sharing with us :) /Ruth

@arunprakash1763 4 жыл бұрын

You saved my life. This will be really helpful to move queries from Power Query to Dataflows.

@CurbalEN 4 жыл бұрын

Glad to hear! /Ruth

@giuseppegullo8771 5 жыл бұрын

I would add that keeping the attribute « hidden » = « false » is a good practice. This way, if a source file (CSV in this case) is open while refreshing the Power BI report, it will avoid having duplicates due to Power BI importing the temporary file as well.

@CurbalEN 5 жыл бұрын

Yes! Thanks for the tip! I wish I could pin more than one comment! Will have to make a follow up video :) /Ruth

@rabsonlungu 4 жыл бұрын

Ruth, Excellent KT. You have solved my problem.

@juanpaduann 2 жыл бұрын

Love your videos! thank you so much for taking the time to do this!

@joshuaprets2044 3 жыл бұрын

I'm glad I watched this video. I hate all that clutter. Thanks!

@CurbalEN 3 жыл бұрын

Me too! Couldn't stand it 😂😂

@nathaliamoraes7465 3 жыл бұрын

You have no ideia how much you helped me with this video! Thank you so much!!!

@CurbalEN 3 жыл бұрын

Yey!! 🥳🥳

@dannyhometown 4 жыл бұрын

Hi Ruth, this video is saving my life. Big THANKS. - Dan

@CurbalEN 4 жыл бұрын

🥳🥳

@jimmy3862 5 жыл бұрын

Simpler things are the best! Great tutorial. Thanks a lot.

@CurbalEN 5 жыл бұрын

Thanks Jimmy, I share your philosophy 100% /Ruth

@implementedgmbh 5 жыл бұрын

Thank you Ruth! Great! That is exactly what I was looking for... you are the best!

@CurbalEN 5 жыл бұрын

Thank you!! :) /Ruth

@KaananAdams 4 жыл бұрын

Same for JSON. =JSON.document([Content]) So much tidier! Great video!

@CurbalEN 4 жыл бұрын

Agree! /Ruth

@JolandaBecker 2 жыл бұрын

Thank you, this has save me so much headache. Your videos are so good and have been career changing to me. :) Go Curbal!

@CurbalEN 2 жыл бұрын

Thanks for the feedback!!

@ddp2049 5 ай бұрын

Thanks I learn how to use csv file today

@BroadtreeSolutions 5 жыл бұрын

Thanks Ruth, that is way cleaner than Combine & Edit! 😊👍🙏

@CurbalEN 5 жыл бұрын

My favorite method :) /Ruth

@mehblahmehblah Жыл бұрын

Thanks for this video!

@miguelmr8869 2 жыл бұрын

Thank you my friend, great you share these wise advices

@scottpowell803 4 жыл бұрын

Hey Ruth - I just wanted to give you a big shout out for this video. I've been trying to load Power BI audit log information into a dataflow, but the out-of-the-box transformations PBI does when you try to combine files breaks badly when the data has some problems with it (in this case, the max string length for one of the audit columns was too long and the PowerShell script truncated it - causing the linefeed to go away.... I struggled with this for hours today until I remembered your video and gave it a try - worked like a charm, I was able to easily filter out the 40 bad records (out of 1.7 million), and get on with my work. Thank you so much for publishing this! Scott

@CurbalEN 4 жыл бұрын

Music to my ears!! That is the exact reason I do this , thanks for sharing it with me 😊 /Ruth

@stephenclarke7351 Жыл бұрын

Love this. Ruth you are a star

@CurbalEN Жыл бұрын

😊

@PedroCabraldaCamara 4 жыл бұрын

I'm glad i've finaly watch this video...thank you so much Ruth!

@CurbalEN 4 жыл бұрын

Read the comments also ! Great tips in there :) /Ruth

@PedroCabraldaCamara 4 жыл бұрын

@@CurbalEN oooooohhhhh.... pdf's also??? Amazing!!!! and xml files also? Please say yes....

@giftcp82 4 күн бұрын

love this. Its awesome

@christopherhastings2142 5 жыл бұрын

Great solution! Wow I really like the simplicity of m code!

@CurbalEN 5 жыл бұрын

M is fantastic and the reason I fell in love with Power Bi :) /Ruth

@miguelmr8869 2 жыл бұрын

thank you, I did with XML files: Xml.Tables([Content])

@rolandotemporale791 5 жыл бұрын

Thanks Ruth!! Very useful

@CurbalEN 5 жыл бұрын

My pleasure and glad it helps :) /Ruth

@ndjanardhan 5 жыл бұрын

it is new to me i.e. Csv.Document, Thanks for the nice video :)

@CurbalEN 5 жыл бұрын

You welcome! Check mondays video for a deep dive on these functions :) /Ruth

@REInvestorCEO 5 жыл бұрын

Many thanks! Fantastic tip.

@CurbalEN 5 жыл бұрын

Makes it easier to import files :) /Ruth

@fernandomanzanares5555 4 жыл бұрын

Another way of doing this is building a function, then applying the function to a list of all the files. In this case it's not worth it, but if you have tons of files and need to perform some operations on them (like removing the first N rows of each file) it makes your life easier. It's also helpful when the files are somewhat different and you have to perform some changes in some files and not in the rest, then you can make those changes based in conditions and it will work :-)

@CurbalEN 4 жыл бұрын

Yes! I use that all the time :)

@robionaire 5 жыл бұрын

Hi, Thanks so much for your videos. I am learning a lot from you. Question : Suppose i want to share a report internally and give users the right to edit it before publishing it in the workspace, is it possible when the data is in my one drive. What s the best way to do it? Thanks in advance.

@nevillewhite2458 3 жыл бұрын

Super helpful. I wish this was the default way that Power BI imported files from folders...

@ronisilva4132 3 жыл бұрын

Thanks Ruth! I love your videos! This in particular helped me a lot. However, just recently I found out that some longer strings in are being cut out. Do you have a solution to how I can manually set the length of string per cell in a query? All the best!

@GoodlyChandeep 5 жыл бұрын

Excellent Trick Ruth! Liked it. Although this query might stop working when you have a different year promoted to a header. The query won't be able to perform the renaming of the column. Instead you can rename the 0th row as "Year" before you promote the headers :)

@CurbalEN 5 жыл бұрын

Yes,I did that a bit too fast , thanks for sharing :) /Ruth

@bradendriscoll 4 жыл бұрын

this might help if that becomes an issue: kzfaq.info/get/bejne/gJ6ZjZt4tp2mqoU.html

@aadilansareen3415 9 ай бұрын

Thanks!

@CurbalEN 9 ай бұрын

Thanks! 😃

@TheVamos777 5 жыл бұрын

I use this command to force a rename of the Filename (or year in your case) regardless of the data after promoting the headers whenever I load multiple files. = Table.RenameColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, "Filename"}}) Ensure you pick Filename first when deleting other columns to ensure it is at the start. Change #"Changed Type" to your previous step name if it's different

@CurbalEN 5 жыл бұрын

Thanks for sharing! I do need to make a part 2! You guys are awesome for sharing all your tips too :) /Ruth

@pratikfutane8131 5 жыл бұрын

Great way..!!!thanks

@CurbalEN 5 жыл бұрын

Clean and easy :) /Ruth

@alreadydeadfunk 5 жыл бұрын

files = Folder.Files(file_directory) csv_files = List.Transform(files[Data], each Csv.Document(_, [ Delimiter = ",", QuoteStyle = QuoteStyle.Csv])) combined = Table.Combine(csv_file) Specify the file directory and set that to a variable, file_directory, before the code above

@CurbalEN 5 жыл бұрын

Thanks for sharing! /Ruth

@rdaleprice7184 4 жыл бұрын

I used the Excel.workbook([content] and that worked; however, there are multiple sheets (like-kind data) within the Excel workbooks. How do you make the combine and promote headers work with multiple worksheets?

@murirokcs5518 4 жыл бұрын

I can't believe there is a dislike on this awesome video

@CurbalEN 4 жыл бұрын

😂😂 thanks! /Ruth

@MarcioJunior-pj5bv 3 жыл бұрын

Its greattttttttttttttttttt. I am from brazil

@CurbalEN 3 жыл бұрын

Hello 👋 Marcio!

@imranali-iy5wk 3 жыл бұрын

I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).

@CAKimberlyLewis 3 жыл бұрын

I love you!

@CurbalEN 3 жыл бұрын

💛

@juank5697 4 жыл бұрын

HI, youre videos are amazing!!!!! do you have a video where clean up a list of telephone number?

@user-du6km3cl1v Жыл бұрын

Hi this works if we same types of csv files, incase we have different types of csv files with different data not related to each other and columns are also not same let table 1 has slno,name,age table2 has empcode,emailid in such case how can we extract table at once

@MrCiccoz 2 жыл бұрын

Many thanks for the Video. Question, is there a way to select encoding UTF-8 when you need to read CSV folder?

@CurbalEN 2 жыл бұрын

Yes, just code it in ;)

@MrCiccoz 2 жыл бұрын

@@CurbalEN gmmm ok how? :D do you have video or a manual? absolutely not a programmer

@CurbalEN 2 жыл бұрын

@@MrCiccoz I dont know the code by heart, it is the fourth parameter: docs.microsoft.com/en-us/powerquery-m/csv-document

@borjagabella1708 4 жыл бұрын

Hi Ruth! Is it possible to make an incremental refresh to import a folder of csv files? Only import the new files. I need to import many big CSV files and every month takes it longer. Gracias !:)

@robertzimmerman8910 Жыл бұрын

Everything worked as you demonstrated except for the step that produces the Name column. You appended the month and day to the year however we didn't get to see the distinct values in that column. In working through the steps, I wound up with a name column that contained the piece of the file name (in this case year), for the first file only. Since this is an ideal column to filter out additional headers, I'll have to use another column to filter on. This could be a big deal if I have hundreds of thousands of records. Do you find the same results?

@JohnnyTwoFingers 4 жыл бұрын

Ruth or anyone, do you know of a video or blog post that explains really well how all the transform files and everything in there work together???

@CAKimberlyLewis 3 жыл бұрын

Any tips/guidance for .txt by chance?

@entertainmentgalaxy971 5 жыл бұрын

Very informative channel. Please share maximum no of PQ functions with examples in PQ day. Thanks for sharing.

@CurbalEN 5 жыл бұрын

In PQ day? There is a Power Query day? /Ruth

@entertainmentgalaxy971 5 жыл бұрын

@@CurbalEN yes i want to learn power query functions in every monday.. what is function and how it works.

@CurbalEN 5 жыл бұрын

You pretty much get that but with useful examples :) /Ruth

@denniskillz Жыл бұрын

How do you go about combining an XLS and a CSV into one file, I could use 2 different import functions and just append the table, but what happens if one of the file is missing later?

@jonmarquet 5 жыл бұрын

Great tip Ruth. Thank ypu. The problem is that i have to transform many queries. 😭😭😭😭😭😭😭😭😭😭😭

@CurbalEN 5 жыл бұрын

Use From.NowOn() instead ;) /Ruth

@faustdw88 5 жыл бұрын

use promote headers before you expand or skip rows

@CurbalEN 5 жыл бұрын

Thanks for the feedback :) /Ruth

@joshuaprets2044 2 жыл бұрын

Yes, but when you promote headers, your last column changed from Name to 2018. Then you change it to Year resulting in there being hard code to changed a column named 2018 to year. So if you add a csv file names 2017 that code will fail. Is there a way to fix that? Thanks.

@bguinn4112 4 жыл бұрын

Love the videos! I did have a CSV file that needed parameters after the [Content]. I setup a test folder with only a single file and followed your directions (Loading files from a folder) I noticed with: Csv.Document([Content]) = 134,000 records Csv.Document([Content],[Delimiter=","]) = 155,000 records Prior to figuring it out, I selected the binary just to see what it did. Results generated 155,000 records I never really figured out why the parameter made a difference since it should be default delimiter

@CurbalEN 4 жыл бұрын

Maybe power query picked another delimiter, so when you indicated the correct one it loaded everything? Just a guess though... /Ruth

@bguinn4112 4 жыл бұрын

@@CurbalEN Purely out of curiosity I tested a little more ([Content])) = 134,000 ([Content],[Columns=33]) = 155000 ([Content],[Delimiter=","]) = 155000 Was really strange behavior. Looked like any [] parameter would force it to do something different Once again... Thanks for the great videos

@CurbalEN 4 жыл бұрын

That is weird and interesting...have to try myself! /Ruth

@bguinn4112 4 жыл бұрын

@@CurbalEN Kept looking it was driving me crazy. Deep within CSV (Row 134,000 ish) in ~ column 20 of the row ProdCode,Descp: ..... ,PCODE01,18" GIZMO,Case, ..... ,PCODE02,1.99 GIZMO,Case must have hit the "" and freaked. It's almost like PowerBI did a delimiter by row as it was processing the file and setting a parameter in the Csv.Document forced it to do something else.

@iwalktheline00 4 жыл бұрын

Is there a way to exclude non-csv files from being imported? For example if I have a folder that contains .csv, .jpg, .doc files, is it possible to only import only the .csv files? Thanks

@CurbalEN 4 жыл бұрын

Just filter csv out, /Ruth

@stephenbrincat7124 5 жыл бұрын

Thanks Ruth, very interesting, any possibilities to continue with this lesson, such as adding a calendar table and keep building a matrix, charts and graphs etc. etc. Reason being is that since Power bi is continuously updating some files and downloads form the internet have become outdated.

@CurbalEN 5 жыл бұрын

Yes, it is a mess when everything changes so fast. I am planning a new serie of videos to create a report from scratch. I just need more time in a day! ;) Hopefully soon, /Ruth

@stephenbrincat7124 5 жыл бұрын

@@CurbalEN Would be nice to have such videos. Well agree on that I'm spending a lot of time watching Power Bi videos & tutorials then my to do list keeps increasing. Would like to get a course where I can learn more in detail the power bi but for my needs, as most videos are tutorials cover a wide range of topics some of which are not of any interest for my requirements.

@CurbalEN 5 жыл бұрын

The only structured content I have is the courses I have so far on Power Query and M language, but when time allows I will continue to cover the entire Power BI platform. It will take a while! /Ruth

@lisamurray1836 4 жыл бұрын

What if I have different tabs in excel. Would I still do excel.workbook or would I have to specify the tab I want and how should that be written?

@CurbalEN 4 жыл бұрын

Maybe this will help? m.kzfaq.info/get/bejne/htKDfs93rM2ReoU.html /Ruth

@efthimiosspiridopoulos9531 2 жыл бұрын

Hey Ruth, Is it possible to avoid loading several files when using this method? ie I don't need to refresh sales data from previous every time the report is refreshed.

@CurbalEN 2 жыл бұрын

You need incremental refresh for that :)

@Dev_Bartwal 5 жыл бұрын

Hi Ruth Thanks to sharing this useful vid Here I have a 1 query plzz help me out from this. I have sales data where is revenue column and cost column And I make mazers =1-cost/revenue After that value is showing in some cell infinity How to remove Infinity? That is my point I am unable to remove that

@CurbalEN 5 жыл бұрын

Use DIVIDE() instead :) /Ruth

@Karthik236 3 жыл бұрын

Curbal one question - here you assumed all the two files are part of one table and hence both these files were combined after introduction of the a column with the query excel.workbook([contents]). But if we want to introduce all as separate tables without combining could u help how this to be achieved

@StefanoVerugi Жыл бұрын

difficult one if from different files, you can load tables separately from the same file as individual queries tho

@nachomiranda5151 5 жыл бұрын

Hi Ruth. This is great. Just a quick question. Is there any way to “change” , i.e., unpivot, each of the files before loading and combining them? Thanks a lot Nacho

@CurbalEN 5 жыл бұрын

You need to load them before you can do any transformation. /Ruth

@lenzypetty1097 4 жыл бұрын

I am grateful again to Ruth for reviewing a great topic. Although, I prefer to use the "Combine & Transform", and then use the "Transform Sample File" method to perform transformations that will apply to all of the files in the folder before they are Combined, such as removing rows, columns, unpivoting, etc.

@CurbalEN 4 жыл бұрын

You can do that manually using this method: m.kzfaq.info/get/bejne/htKDfs93rM2ReoU.html /Ruth

@Davido-san 2 жыл бұрын

Super late comment, but what if you need to transform each file's content before they are appended together?

@ademirpizzolato2858 3 жыл бұрын

Hi, great hint !! I´m trying to use it with Sharepoint folder, but I was not able to figure it out. Someone can help ?

@cessjulioify 4 жыл бұрын

Hola Ruth, los mismo archivos puedes ocuparlos para varios powerbi? varios dashboard haciendo uso del archivo?

@CurbalEN 4 жыл бұрын

No entiendo muy bien la pregunta? /Ruth

@cessjulioify 4 жыл бұрын

@@CurbalEN si tengo un txt en un SharePoint con mis dimensiones lo puedo mandar a llamar en varios proyectos de PBI?

@Loevrak 5 жыл бұрын

Hello Ruth, I'm tryng to follow Your sugestions, but… I get the following:"Expression.Error: Vi kan ikke bruke felttilgang på typen Function. Detaljer: Value=Function Key=Content"Do you have any idea what could be wrongThe date are from a folder containing two files of Excel type??

@delle77 5 жыл бұрын

Hi Ruth and community, this is a really nice little trick to keep it clean. But I got a little problem CSV file: With the transform file function and sample file, power query can handle the file. With the created column as you showed, power query can not handle the file. Error message: DataFormat.Error: There were more columns in the result than expected. Details: Count=1 Why does it work one way, but not the other and is there any way to solve it? Thx

@CurbalEN 5 жыл бұрын

The csv functions hardcore the number of columns to import, I am guessing that there is a mismatch. Check this video to understand more about the csv function: m.kzfaq.info/get/bejne/eNh9e9ln37zXon0.html /Ruth

@sparvathaneni1 Жыл бұрын

I have data set about 10M to 55M in few cases from Oracle/SQL Server. How do I use power bi service to extract data? What's the usal process of data import in Power BI. Any inputs are greatly appreciated. Thank you!

@agolumeste 4 жыл бұрын

Great! How to do same with dataflow?

@CurbalEN 4 жыл бұрын

Same way?? Or it didn't work? /Ruth

@nopitchaccess 3 жыл бұрын

Sorry if this been asked already but im struggling to find an answer - is it possible to connect to csv files in a google drive folder ???

@Newwarfare4 2 жыл бұрын

Hi, could someone please explain how to the the black PBI theme?

@rickyzeng5907 Жыл бұрын

what if I don't want to combine them together, I want to bulk import, but still import as each individual file?

@andrecavasotero6234 5 жыл бұрын

Você solucionou um problema que estava enfrentando com o conector PDF. Estava dias alterando o arquivo de amostra e criando funções e um comando desses era suficiente. Obrigado.

@CurbalEN 5 жыл бұрын

Yey!! Glad to hear :) /Ruth

@madeleineocarroll2063 9 ай бұрын

Do you find that as of the April 2023 Power BI Update that adding the custom column creates unwanted duplication? I don't know what's changed, but I can't use this method anymore because it creates duplicates of EVERY row in my dataset. With no human internvetion. It just happened.

@CurbalEN 9 ай бұрын

I will check!

@madeleineocarroll2063 2 ай бұрын

@@CurbalEN Still encountering duplicate records in our queries using this method

@punit6490 3 жыл бұрын

Year is getting hard coded in the column.rename and it can cause error... Any error proofing for that...

@AlexTaskarataskar 5 жыл бұрын

This is a great video! How to do the same with TXT files ?

@CurbalEN 5 жыл бұрын

Mmm I dont know if there is a function for that... Check in the Power bi Community! /Ruth

@dekmar7954 4 жыл бұрын

Table.TransformColumns(#"PREVIOUS STEP", {"Content", each Table.FromColumns({Lines.FromBinary(_)}), type table})

@lujt006 4 жыл бұрын

Taking the code from PQ's helper query worked for me: Table.PromoteHeaders(Csv.Document( [Content],[Delimiter=" ", Columns=18, Encoding=1200, QuoteStyle=QuoteStyle.None]))

@mariaalcala5159 3 жыл бұрын

Hi Curbal! After using excel.workbook to get the content of about 32 files I am facing a problem now while expanding the data. One of the files have more than 200 columns and there were other files with just 50 or fewer columns. I notice that if one of those files with less columns on it were on the first position while expanding the data I was just getting the amount of columns of those files. Now I put what I think it might be the heaviest file (the one with more columns on it -since each file have the same amount of rows) on the first position and expanded the data. I got now more columns than before but Im still don’t getting the whole data. On a separate file I tried to get just the file with the high amount of columns und there wasn’t a problem at all to get the 200 columns. Do you have any idea why j cannot see the whole data while using this method?! I’ve been searching a lot on the internet but I haven’t found any solution yet. Any advice? I really appreciate it

@CurbalEN 3 жыл бұрын

The number of columns loaded sre hard-coded in one of the steps. Just add the columns you need there and you should be fine. On a separate note, do you need all 200 columns?

@mariaalcala5159 3 жыл бұрын

@@CurbalEN thanks for the quick answer! I did it manually as you said and it worked. Actually I just need the last five columns but how can I do it automatically? Is there a way to import just those? It was a pain to add the columns manually 😭😭

@mikelast8468 2 жыл бұрын

This way is definitely neater, but is it really faster?

@FletcherMcmullen 5 жыл бұрын

If I add another file in the same format to the folder and refresh the data, does power bi see the new file and append it to the data?

@CurbalEN 5 жыл бұрын

Yes :))) /Ruth

@FletcherMcmullen 5 жыл бұрын

@@CurbalEN Brilliant. Now if I go fix all of my old queries that have been doing it wrong the whole time, do I lose all the existing steps in those queries? I dont want to break all the work I have done already.

@CurbalEN 5 жыл бұрын

You dont have to, just follow this steps: m.kzfaq.info/get/bejne/o9aKhdaJzLPbhY0.html /Ruth

@nagarajuyadav2162 3 ай бұрын

How to import large dataset 1.7 billoins records in power bi from databricks please make a video on it thanks.

@nishbobby 5 жыл бұрын

What if there is more than one sheet in the excel? Can you please let me know how to select one sheet

@CurbalEN 5 жыл бұрын

Check this video: m.kzfaq.info/get/bejne/htKDfs93rM2ReoU.html /Ruth

@sarawilson2466 3 жыл бұрын

I'm stuck! I think I followed this exactly but getting Error in all of my fields: DataFormat.Error: There were more columns in the result than expected. Details: Count=14

@arne.munther Жыл бұрын

I didn't get it to work, only got one column out, instead of 36. My guess was that CSV-file don't have comma as the delimeter, but a semicolon. After a bit trial and error, I landed on this: =Csv.Document([Content],36,";")

@learnspreadsheets 5 жыл бұрын

I think this is an essential power query skill. Before promoting headers I add an index col, then conditional col you can see my steps here: vimeo.com/228536168?ref=em-share

@rickyzeng5907 Жыл бұрын

What if I don’t want to combine them into one single file， instead I’d like to still remain as individual files.

@abhilashpatel6284 3 жыл бұрын

I have HTML documents in the folder how can I do it with HTML document..?

@PedroCabraldaCamara 4 жыл бұрын

Hey guys. I'm finally redoing all my queries with the add column Csv.Workbook ([Content]) and it's not working because it's showing me weird characters, from a specific text column from all those files. I've tried to add Encoding=65001 and doesn't work. Anyone got this problem?

@CurbalEN 4 жыл бұрын

Try this, go to your old file, open the advanced editor and look at how power bi decoded the csv file and use the same line of code. Btw the function is Csv.Document not workbook but I am assuming it was just a typo ;) /Ruth

@PedroCabraldaCamara 4 жыл бұрын

@@CurbalEN I'm so sorry Ruth but i didn't understand. Can you help? Let me remind you that this is for all csv file in a folder. This is the code: let Source = Folder.Files("C:\Users\...\Purchases"), #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Date created", "Content"}), #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))) in #"Expanded Table Column1" After this, is the normal cleaning process. Thank you so much

@CurbalEN 4 жыл бұрын

@@PedroCabraldaCamara can you post the m code when you import on of the csv files directly? /Ruth

@PedroCabraldaCamara 4 жыл бұрын

@@CurbalEN I don't know if i understood correctly your question, but besides the code above, which is the original one, i'd love to change to this one so i don't have anymore of those Transform Files which i believe not only take more time to refresh the data as well as increase my pbi file. Here's the code that doesn't work because of those weird characters on text columns: let Source = Folder.Files("C:\Users\...\Purchases"), #"Added Custom" = Table.AddColumn(Source, "Custom", each Csv.Document ([Content])), Encoding=65001, #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Date created", "Custom"}), #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", .... Thank you so much Ruth

@CurbalEN 4 жыл бұрын

@@PedroCabraldaCamara import one of the csv files using the csv connector in power bi and share the code. /Ruth

@evelic Жыл бұрын

The excel.workbook function throws me an error.

@fegaru 3 жыл бұрын

Ruth! Excel.Workbook seems not work in newest versions... ¿Do you know what's happend?

@CurbalEN 3 жыл бұрын

It should work, which version are you using?

@fegaru 3 жыл бұрын

@@CurbalEN The last one. I usually download every month update. It's work, but now I had to add one step. After I create the custom column I need to expand to see name, data, Item, kind and Hidden, after that I had to delete all columns except data and expand it again. Its work, but needs one extra step.

@CurbalEN 3 жыл бұрын

Hasn't it always been like that?