{"id":166,"date":"2020-08-08T06:23:01","date_gmt":"2020-08-08T11:23:01","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=166"},"modified":"2020-08-20T13:21:29","modified_gmt":"2020-08-20T18:21:29","slug":"importing-text-files-in-power-bi","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2020\/08\/08\/importing-text-files-in-power-bi\/","title":{"rendered":"Importing text files in Power BI"},"content":{"rendered":"\n<p>What do you do when text files are not formatted as <code class=\"\" data-line=\"\">.csv<\/code> or <code class=\"\" data-line=\"\">.json<\/code> ? If they repeat a pattern, you can automate it. <br><br>( You can download the <a href=\"https:\/\/ninmonkeys.com\/examples\/pbix\/Importing text files in Power BI.zip\">final .PBIX report here<\/a> )<\/p>\n\n\n\n<p>This file always uses:<\/p>\n\n\n\n<ul><li>3 values for for each &#8220;table&#8221; row<\/li><li>One line per value &#8211; which becomes our &#8220;columns&#8221;<\/li><li>every &#8220;column&#8221; line has a <code class=\"\" data-line=\"\">:<\/code> in it. If you skip all lines without a <code class=\"\" data-line=\"\">:<\/code> it filters all unwanted lines<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\" data-line=\"\">Title\n---------------------\nDeviceID: One\nInfo1: One:Info1\nInfo2: One:Info2\n\n---------------------\n\nDeviceID: Two\nInfo1: Two: Info1\nInfo2: Two: Info2\n\n---------------------\n\nDeviceID: DeviceID\nInfo1: Three: Info1\nInfo2: Three: Info2<\/code><\/pre>\n\n\n\n<p>First take a look at the final function. Then I&#8217;ll explain how it works.<\/p>\n\n\n\n<p>The main query calls a custom function<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\" data-line=\"\">ConvertTableFromTextLines( &quot;c:\\data\\sample.txt&quot;, &quot;:&quot;, 3 )<\/code><\/pre>\n\n\n\n<p>All the work is one here<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\" data-line=\"\">let  ConvertTableFromText = (filepath as text, splitCharacter as text, linesPerRecord as number, optional encoding as nullable number) as table =&gt;\n    let\n        TextLines = Lines.FromBinary(\n            File.Contents( filepath, null ),\n            null,\n            null,\n            encoding ?? TextEncoding.Utf8\n        ),\n        TextLineTable = Table.FromColumns(\n            { TextLines }, {&quot;Line&quot;}\n        ),\n\n        Pairs = Table.SelectRows(\n            TextLineTable,\n            each Text.Contains( &#091;Line], splitCharacter )\n        ),\n\n        SingleRecordAsCols = Table.SplitColumn(\n            Pairs,\n            &quot;Line&quot;,\n            Splitter.SplitTextByEachDelimiter(\n                { splitCharacter }, QuoteStyle.Csv, false\n            ),\n            {&quot;Name&quot;, &quot;Value&quot;}\n        ),\n\n        TotalLines = Table.RowCount( SingleRecordAsCols ) ,\n        NumberOfGroups = TotalLines \/ linesPerRecord,\n\n        ListOfRecords = List.Generate(\n            () =&gt; 0,\n            each _ &lt; NumberOfGroups,\n            each _ + 1,\n            each (\n                Record.FromTable(\n                    Table.Range(\n                        SingleRecordAsCols,\n                        _ * linesPerRecord,\n                        linesPerRecord\n                    )\n                )\n            )\n        ),\n        t = Table.FromList(\n            ListOfRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error\n        ),\n\n        columnNameList = Record.FieldNames( ListOfRecords{0} ),\n\n        TableOfRecords = Table.ExpandRecordColumn(\n            t, &quot;Column1&quot;, columnNameList, columnNameList\n\n        )\n    in\n        TableOfRecords\nin\n    ConvertTableFromText\n<\/code><\/pre>\n\n\n\n<p>First we load the text file as a list.  <code class=\"\" data-line=\"\">File.Contents( filepath )<\/code>  reads all file types. <code class=\"\" data-line=\"\">Lines.FromBinary( Contents )<\/code> converts it into text. Depending on how the file was saved, you may need to set the <code class=\"\" data-line=\"\">encoding<\/code><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"303\" height=\"423\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-2.png\" alt=\"\" class=\"wp-image-181\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-2.png 303w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-2-215x300.png 215w\" sizes=\"(max-width: 303px) 100vw, 303px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Don&#8217;t worry if <strong>encoding<\/strong> sounds scary.  If text loads with the wrong characters, try the other one. Two good ones to try first are:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><code class=\"\" data-line=\"\">TextEncoding.Windows<\/code><\/td><td>Power BI defaults  to this for windows files<\/td><\/tr><tr><td><code class=\"\" data-line=\"\">TextEncoding.Utf8<\/code><\/td><td>Anything on the internet uses <code class=\"\" data-line=\"\">UTF8<\/code> by default. <\/td><\/tr><\/tbody><\/table><figcaption>Tip: If a file truly is <code class=\"\" data-line=\"\">ASCII<\/code> then <code class=\"\" data-line=\"\">UTF8<\/code> will work<\/figcaption><\/figure>\n\n\n\n<p>Next remove any lines that are missing <code class=\"\" data-line=\"\">:<\/code><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"481\" height=\"334\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-3.png\" alt=\"\" class=\"wp-image-182\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-3.png 481w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-3-300x208.png 300w\" sizes=\"(max-width: 481px) 100vw, 481px\" \/><\/figure>\n\n\n\n<p>Then split each line into two columns:<br><strong>Transform Column -&gt; Text -&gt; Split -&gt; By Delimiter <\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"529\" height=\"399\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-4.png\" alt=\"\" class=\"wp-image-183\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-4.png 529w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-4-300x226.png 300w\" sizes=\"(max-width: 529px) 100vw, 529px\" \/><\/figure>\n\n\n\n<p>I started with these options<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"371\" height=\"447\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-5.png\" alt=\"\" class=\"wp-image-184\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-5.png 371w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-5-249x300.png 249w\" sizes=\"(max-width: 371px) 100vw, 371px\" \/><\/figure>\n\n\n\n<p>I renamed them to <strong>Name<\/strong> and <strong>Value<\/strong> because they will be converted into records.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"484\" height=\"334\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-7.png\" alt=\"\" class=\"wp-image-187\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-7.png 484w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-7-300x207.png 300w\" sizes=\"(max-width: 484px) 100vw, 484px\" \/><\/figure>\n\n\n\n<p>To convert multiple lines into a single <code class=\"\" data-line=\"\">record<\/code>I used <code class=\"\" data-line=\"\">List.Generate<\/code>. You can think of it like a <code class=\"\" data-line=\"\">for loop<\/code><\/p>\n\n\n\n<p>There are<\/p>\n\n\n\n<ul><li>3 lines per record<\/li><li>9 total lines = the number of lines in the file<\/li><li>3 total groups = 9 total lines \/ 3 lines per record<\/li><\/ul>\n\n\n\n<p>We need to loop 3 times. I&#8217;ll recreate a loop like this, converted to Power Query<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\" data-line=\"\">for( $i = 0 ; $i &lt; NumberOfGroups; $i += 1){\n    CreateRecord()\n}<\/code><\/pre>\n\n\n\n<p><code class=\"\" data-line=\"\">List.Generate()<\/code> has 4 arguments. Each one is a function. It uses:<\/p>\n\n\n\n<ul><li><code class=\"\" data-line=\"\">initialize<\/code> sets your starting condition. ( set <code class=\"\" data-line=\"\">i<\/code> to <code class=\"\" data-line=\"\">0<\/code> )<\/li><li><code class=\"\" data-line=\"\">condition<\/code> tests if we&#8217;ve reached the total number of groups<\/li><li><code class=\"\" data-line=\"\">next<\/code> changes the value of <code class=\"\" data-line=\"\">i<\/code>. We want to add <code class=\"\" data-line=\"\">1<\/code><br>optional <code class=\"\" data-line=\"\">selector<\/code> function creates the value every loop. This calls <code class=\"\" data-line=\"\">CreateRecord()<\/code> once for every loop<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\" data-line=\"\">    ListOfRecords = List.Generate(\n        () =&gt; 0,\n        each _ &lt; NumberOfGroups,\n        each _ + 1,\n        each (\n            Record.FromTable( \n                Table.Range(\n                    SingleRecordAsCols,\n                    _ * linesPerRecord,\n                    linesPerRecord\n                )\n            )\n        )\n    ),<\/code><\/pre>\n\n\n\n<p>The <code class=\"\" data-line=\"\">next<\/code> function first grabs rows from the table <code class=\"\" data-line=\"\">SingleRecordAsCols<\/code>. It converts those into a single <code class=\"\" data-line=\"\">record<\/code><\/p>\n\n\n\n<p>Every loop <code class=\"\" data-line=\"\">3<\/code> is added to the <code class=\"\" data-line=\"\">offset<\/code>.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><code class=\"\" data-line=\"\">i<\/code> value<\/td><td><code class=\"\" data-line=\"\">offset<\/code><\/td><td><code class=\"\" data-line=\"\">count<\/code><\/td><\/tr><tr><td>0<\/td><td>0 * 3 = 0<\/td><td>3<\/td><\/tr><tr><td>1<\/td><td>1 * 3 = 3<\/td><td>3<\/td><\/tr><tr><td>2<\/td><td>2 * 3 = 6<\/td><td>3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>You end up with a <code class=\"\" data-line=\"\">list<\/code> of <code class=\"\" data-line=\"\">records<\/code><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"403\" height=\"304\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-9.png\" alt=\"\" class=\"wp-image-189\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-9.png 403w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-9-300x226.png 300w\" sizes=\"(max-width: 403px) 100vw, 403px\" \/><figcaption>Choose <strong>convert to table<\/strong><\/figcaption><\/figure>\n\n\n\n<p>Finally expand<strong> records to columns<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"532\" height=\"368\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-10.png\" alt=\"\" class=\"wp-image-190\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-10.png 532w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-10-300x208.png 300w\" sizes=\"(max-width: 532px) 100vw, 532px\" \/><\/figure>\n\n\n\n<p>The final result:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"519\" height=\"190\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-11.png\" alt=\"\" class=\"wp-image-191\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-11.png 519w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/08\/image-11-300x110.png 300w\" sizes=\"(max-width: 519px) 100vw, 519px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Importing data from text files that are not formatted as `csv` or `json`<\/p>\n","protected":false},"author":1,"featured_media":170,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[13,9],"tags":[6,7,8],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/166"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=166"}],"version-history":[{"count":15,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/166\/revisions"}],"predecessor-version":[{"id":799,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/166\/revisions\/799"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/170"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}