{"id":3955,"date":"2022-01-31T09:05:51","date_gmt":"2022-01-31T17:05:51","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3955"},"modified":"2022-01-31T09:05:54","modified_gmt":"2022-01-31T17:05:54","slug":"how-to-combine-csv-files-by-using-power-bi","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3955","title":{"rendered":"How to Combine CSV Files by Using Power\u00a0BI"},"content":{"rendered":"\n

If you are finding a solution for Excel, please read this post. <\/p>\n\n\n\n

https:\/\/summalai.com\/?p=3964<\/a><\/p>\n\n\n\n

<\/p>\n\n\n\n

Below by Stephanie Bruno<\/a><\/p>\n\n\n\n

One of the unsung heroes to me in Power BI desktop (or Power Query in Excel) is how wonderfully simple it is to combine csv or Excel files from a folder. Maybe it\u2019s not totally unsung, but I think it\u2019s an everyday problem that many people have to deal with. It could easily be an entry point for many new users to see just how much easier Power Query can make their lives.<\/p>\n\n\n\n

As much as I appreciate how easy the tool allows us to automatically combine files without having to write any code, I never like all those extra queries that get created in my file and so I prefer to simplify it by just writing one magical little line of code. But I always got frustrated because my one line of code left me without proper column headers, so my one line of code turned into a few extra cumbersome steps. In this post, I\u2019ll show you how to slightly modify that one line of code to get the column headers and keep your queries clean and simple.<\/p>\n\n\n\n

First, let\u2019s look at what we get with the automatic way of combining files. I have three csv files that I\u2019d like to combine. They look like this:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

The first step is to choose to Get Data, and select \u201cFolder\u201d, and then click the Connect button.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

After browsing to a file path and clicking OK, you\u2019ll get a screen that shows the files in the selected folder, and a few different buttons.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

To let Power BI do all the work for you, just click the \u201cCombine & Transform Data\u201d button. You\u2019ll then see a screen that gives you a couple of choices to change how the files are combined, but in most cases you\u2019ll just select the default and let it do its thing.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

After clicking the OK button, Power BI will do it\u2019s magic to create the queries to combine all of these files for you. You\u2019ll see something like the queries below:<\/p>\n\n\n\n

\"\"<\/a>
output from automatic combining of three csv files<\/figcaption><\/figure>\n\n\n\n

In many cases this will be just what the user wants. Simple, easy, and the files are magically combined! However, I just don\u2019t like all those extra queries that end up under the \u201cTransform File from\u2026\u201d folder (outlined in red in the image above). I would like something that results in fewer queries. To do that, we can go back to the step where we clicked the big yellow button labeled \u201cCombine & Transform Data.\u201d Instead of clicking that button, we click the slightly less obvious button simply labeled \u201cTransform Data.\u201d This will bring up the Power Query window with just a single step in this query that just shows what files are in the selected folder.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

The trick is to explicitly tell Power BI what that binary content is in the \u201cContent\u201d column. To do that, go to the \u201cAdd Column\u201d tab, choose \u201cCustom Column\u201d, and type in the following for the formula (where \u201cContent\u201d is the name of the column that holds the Binary data):<\/p>\n\n\n\n

1<\/td>= Csv.Document([Content])<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\"\"<\/a>
1. Go to the Add Column tab
2. Click Custom Column
3. Enter the formula Csv.Document([Content])
4. Click the OK button<\/figcaption><\/figure>\n\n\n\n

This adds a new column to your output with the csv content (you can also do this for Excel files by using Excel.Workbook<\/a> instead of Csv.Document<\/a>). If you click inside one of the cells of the new column (without clicking on the word \u201cTable\u201d), you will get a preview below of the content. Notice how the column headers are all \u201cColumn 1, Column 2, Column3, etc.\u201d<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Now all that\u2019s needed is to expand that Custom column and your csvs will be combined! Beautiful! Except\u2026. those columns headers need some attention.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

Now, of course, we can promote the first row to headers, which will give us the proper column headers, but we will still have an extra row of column headers for each of the csvs (highlighted in the image above). There is an additional problem with that, which you may notice in the Name column. When we promote headers, that column will be renamed with whatever the name of the first file is. And then if you try to change that column name from \u201ccsv1.csv\u201d to something else in a further step, you will run into trouble if for some reason the first file is no longer named \u201ccsv1.csv.\u201d This is illustrated in the following image.<\/p>\n\n\n\n

\"\"<\/a>
1. Go to the Transform tab
2. Click \u201cUse First Row as Headers\u201d<\/figcaption><\/figure>\n\n\n\n

Ugh. Now we would have to filter out those rows that are column headers and also figure out how to handle the problem with the filename. Or\u2026 we could go back and tweak that one line of code we wrote to get the csv data from the binary content. To change it go to the APPLIED STEPS pane, delete the four steps after the \u201cAdded Custom\u201d step by clicking on the X to the left of the step name, then click on the gear icon next to the \u201cAdded Custom\u201d step to modify the formula. Instead of<\/p>\n\n\n\n

1<\/td>= Csv.Document([Content])<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

we can change it to the following instead.<\/p>\n\n\n\n

1<\/td>= Table.PromoteHeaders(Csv.Document([Content]))<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

After doing that, when we click in a Table cell and see the preview, we magically have proper column headers!<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

And when we expand the Custom column to get all the data, we see the right column names!<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

After clicking OK, our earlier problems of extra rows, missing column names, and having to promote something as a header that we don\u2019t want all go away! Now all you need to do is clean up the data types and you\u2019re all set. One query with just a couple of steps and your files are combined.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Don\u2019t be afraid to tweak your M code just a tiny bit to solve some problems. All we had to do here was add a little extra something to one line of code, and we ended up with a nice clean and simple query to combine csv files.<\/p>\n\n\n\n

Ref: https:\/\/data-witches.com\/2020\/10\/27\/my-favorite-clean-and-simple-way-to-combine-csv-files-in-power-bi\/<\/p>\n","protected":false},"excerpt":{"rendered":"

If you are finding a solution for Excel, please read this post. https:\/\/summalai.com\/?p=3964 Below by Stephanie Bruno One of the unsung heroes to me in Power BI desktop (or Power Query in Excel) is how wonderfully simple it is to combine csv or Excel files from a folder. Maybe it\u2019s not totally unsung, but I Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621,1],"tags":[1070,1071],"class_list":["post-3955","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","category-uncategorized","tag-combine-csv-files","tag-combine-csv-files-power-bi"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3955","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3955"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3955\/revisions"}],"predecessor-version":[{"id":3966,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3955\/revisions\/3966"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}