{"id":6487,"date":"2020-05-17T18:15:56","date_gmt":"2020-05-17T16:15:56","guid":{"rendered":"https:\/\/blog.via-internet.de\/?p=6487"},"modified":"2023-11-05T15:01:16","modified_gmt":"2023-11-05T14:01:16","slug":"power-bi-importing-multiple-files","status":"publish","type":"post","link":"https:\/\/via-internet.de\/blog\/2020\/05\/17\/power-bi-importing-multiple-files\/","title":{"rendered":"Power BI | Importing multiple files"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Getting Started<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To import multiple files from a folder, the following two steps had to be done:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>create a list of all files in the folder<\/li><li>for each file: read the file and add it to the result table<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">When importing files with Power BI, you can do both tasks together or each task separately.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The decision, which way to go, ist done after selection the folder:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_combine_1-1.png\" alt=\"\" class=\"wp-image-6515\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">You could choose between 4 posibilities.  Strictly speaking, you have to possibilities, both with the same to final steps.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/i2.wp.com\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-15_54_03.png?fit=700%2C391&amp;ssl=1\" alt=\"\" class=\"wp-image-6557\"\/><\/figure>\n\n\n\n<ol class=\"is-style-default wp-block-list\"><li><strong>Load <\/strong>or <strong>Combine<\/strong> files <ul><li><strong>Load <\/strong>means, the list of the files will be loaded as table<br>Technicaly two things are done:<ul><li>a connection is created in the model<\/li><li>the data (list of files) is loaded to the mode<\/li><\/ul><\/li><\/ul><\/li><li>Just <strong>Load <\/strong>or <strong>Transform <\/strong>data<ul><li>Transform means, you will end up in the Power Query Editor, so you can add additional modifications<\/li><\/ul><\/li><\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">In order to better understand the process, we show the two steps separately: one after the other<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Load the list of files from folder<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Start Power BI and close the start screen, if it is still visible.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Then, click on the <strong>Get Data<\/strong> Button in the <strong>Home <\/strong>Ribbon<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_01-700x420.png\" alt=\"\" class=\"wp-image-6490\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">If you click on the small down arrow on the <strong>Get Data<\/strong> Button, you have to select the option <strong>More<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_02-700x420.png\" alt=\"\" class=\"wp-image-6491\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Now, select <strong>Folder<\/strong>  and click on <strong>Connect<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_03-700x770.png\" alt=\"\" class=\"wp-image-6492\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Enter the folder  (or <strong>Browse<\/strong>&#8230;) with the files to be loaded and click <strong>Ok<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_04-700x221.png\" alt=\"\" class=\"wp-image-6493\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">After this, Power Query will create a table with all files in the folder.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now, here is the point to decide, which way to go:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Combine<ul><li>Read list of files and combine all files into on table<\/li><\/ul><\/li><li>Load<ul><li>Just keep the list of files and return to Power BI<\/li><\/ul><\/li><li>Transform<ul><li>Keep the list of files and open the Power Query Editor<\/li><\/ul><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">We will choose to load the files, because we will do each step later separately<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_05-700x525.png\" alt=\"\" class=\"wp-image-6494\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In Power BI Desktop, click on the <strong>Data <\/strong>Icon to show the resulting table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Combine all files into one table<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To add additional steps, we need the Power Query Editor.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So click on the 3 dots at the right side of the Query name <strong>Samples <\/strong>and choose <strong>Edit Query<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/i2.wp.com\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_then_combine_0.png?fit=700%2C204&amp;ssl=1\" alt=\"\" class=\"wp-image-6553\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Now, you are in the Power Query Editor<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_10-700x411.png\" alt=\"\" class=\"wp-image-6499\"\/><\/figure><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:66.66%\">\n<p class=\"wp-block-paragraph\">To combine all files, just click on the small icon beneath the header of the content column:<\/p>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:33.33%\">\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_then_combine_1_2.png\" alt=\"\" class=\"wp-image-6561\"\/><\/figure>\n<\/div>\n<\/div>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_then_combine_5-700x381.png\" alt=\"\" class=\"wp-image-6504\" width=\"628\" height=\"342\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In the following dialog, you will see all files an a preview of the content for each file.  For excel files, you will see the sheet names and the names of the intelligent tables in the sheets.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Click on <strong>OK <\/strong>to start the import.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_then_combine_2-700x557.png\" alt=\"\" class=\"wp-image-6540\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">When Power Query is done with this step, you will see the result:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_load_then_combine_7-700x381.png\" alt=\"\" class=\"wp-image-6506\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The previous query <strong>Samples <\/strong>is still there, but now with the content of all files.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, you will see four other elements:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_10_11.png\" alt=\"\" class=\"wp-image-6562\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">How combining the files is done<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Each query consists of a list of steps, which are process one after another. Normaly, each step is using the result (data) of the previous step, performs some modifications and has a result (data) for the next step.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So, each step is modifying the whole data of the previous step. Describing some modifications means either<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>do one thing, e.g. add an additional column<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">or<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>do something for each row in the data<br>This means, we need some sort of a loop, like &#8220;do <em>xyz <\/em>for <em>each <\/em>row in the data<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Lets see, how Power Query solves this task.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the query <strong>Samples<\/strong>, exampine the Step <strong>Invoke Custom Function1<\/strong> <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_15_33-700x214.png\" alt=\"\" class=\"wp-image-6570\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The Step if performing the M function Table.AddColumn<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/m_function_addcolumn.png\" alt=\"\" class=\"wp-image-6582\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This functions needs 3 parameter:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>table<\/strong>: which is normaly the name of the prevoius step<br>In our example<em> #&#8221;Filtered Hidden Files1&#8243;<\/em><\/li><li><strong>newColumnName<\/strong>: the name for the column to be added<br><em>&#8220;Transform File&#8221;<\/em><\/li><li><strong>columnGenerator<\/strong>: a function which is called for each row in the input table and creates the new column content<br><em>each #&#8221;Transform File&#8221;([Content])<\/em><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This results in the following procedure:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>for each row of the list of files (output from step <em>#&#8221;Filtered Hidden Files1&#8243;<\/em>)<\/li><li>get the content of the column Content (this will be the parameter for the function call)<\/li><li>call the function <em>&#8220;Transform File&#8221;([Content])<\/em> to create the column with one parameter: the value of the column <em>([Content]<\/em> i<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Helper Queries (Required)<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/transform-file.png\" alt=\"\" class=\"wp-image-6578\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_14_31-700x134.png\" alt=\"\" class=\"wp-image-6567\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This is the required function to create the column content for each file<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><em>Helper queries (Optional)<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For the resulting query <strong>Samples <\/strong>to work, only the function definition is required.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But Power Query add some additional elements, to test the function and show the result<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_34_20.png\" alt=\"\" class=\"wp-image-6575\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Create a parameter used in the query <strong>Transform Sample File<\/strong> and define the curent value <strong>Sample File<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_13_46.png\" alt=\"\" class=\"wp-image-6564\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_34_29.png\" alt=\"\" class=\"wp-image-6577\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Define  a value for the parameter. Here, the first row in the list of files is used.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_14_12-700x45.png\" alt=\"\" class=\"wp-image-6566\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_13_59-700x50.png\" alt=\"\" class=\"wp-image-6565\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_34_38.png\" alt=\"\" class=\"wp-image-6574\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Create a query and use an excel workbook as input. The name of the excel file is speficied as a parameter<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/m_function_excel_workbook.png\" alt=\"\" class=\"wp-image-6585\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In this query, the previously create parameter <strong>Parameter1 <\/strong>is used as parameter (to much of the word parameter, i know :))<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_14_50-700x47.png\" alt=\"\" class=\"wp-image-6568\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/2020-05-17-16_15_00-700x46.png\" alt=\"\" class=\"wp-image-6569\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Importing multiple files with different formats<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If the selected folder contains files with different format, the result is not what you may be expect:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The list of files contains all files, both csv files and xls files<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_different_formats_2-700x336.png\" alt=\"\" class=\"wp-image-6592\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">When combining the files, you can select between the files. So first take a look at an csv file:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_different_formats_3-700x504.png\" alt=\"\" class=\"wp-image-6593\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The csv file looks as expected:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_different_formats_4-700x507.png\" alt=\"\" class=\"wp-image-6594\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">But the xls files looks strange:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_different_formats_5-700x523.png\" alt=\"\" class=\"wp-image-6595\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">But lets try. Click on ok to combine all files.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But, looking at the resulting query, the data of the xls files still looks strange:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_different_formats_6-700x214.png\" alt=\"\" class=\"wp-image-6596\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">To understand this, take a look into the create transfer function:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_different_formats_7-700x75.png\" alt=\"\" class=\"wp-image-6591\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The crucial instruction is line 2:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"csharp\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">Source = Csv.Document(Parameter3,[Delimiter=\",\", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The source document (each file in the list of files) is interpreted as csv file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So, the xls files are also read in as csv files. This leads to the strange result.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You can fix this by adding an additional filter step in the query to select only csv files:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/blog.via-internet.de\/wp-content\/uploads\/2020\/05\/import_multiple_files_different_formats_8.png?fit=700%2C242&amp;ssl=1\" alt=\"\" class=\"wp-image-6601\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Getting Started To import multiple files from a folder, the following two steps had to be done: create a list of all files in the folder for each file: read the file and add it to the result table When importing files with Power BI, you can do both tasks together or each task separately. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":6589,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[59,79],"tags":[],"class_list":["post-6487","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-power-bi","category-uncategorized"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/posts\/6487","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/comments?post=6487"}],"version-history":[{"count":1,"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/posts\/6487\/revisions"}],"predecessor-version":[{"id":9737,"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/posts\/6487\/revisions\/9737"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/via-internet.de\/blog\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/media?parent=6487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/categories?post=6487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/via-internet.de\/blog\/wp-json\/wp\/v2\/tags?post=6487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}