{"id":2133,"date":"2022-10-26T21:14:32","date_gmt":"2022-10-26T21:14:32","guid":{"rendered":"https:\/\/www.bjorn-meijer.nl\/?p=2133"},"modified":"2022-10-26T21:15:54","modified_gmt":"2022-10-26T21:15:54","slug":"json-array-converteren-naar-tabel-in-power-bi-2","status":"publish","type":"post","link":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/","title":{"rendered":"Convert JSON array to table in Power BI"},"content":{"rendered":"<p>Suppose you have a JSON file that looks like this and you want to import it as a table into Power BI:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n{ &quot;fields&quot;: [ { &quot;field&quot;: &quot;field1&quot;, &quot;type&quot;: &quot;string&quot; }, { &quot;field&quot;: &quot;field2&quot;, &quot;type&quot;: &quot;string&quot; }, { &quot;field&quot;: &quot;field3&quot; , &quot;type&quot;: &quot;integer&quot; }, { &quot;field&quot;: &quot;eventTimestamp&quot;, &quot;type&quot;: &quot;date&quot; } ], &quot;results&quot;: [ [ &quot;field1value1&quot;, &quot;field2value1&quot;, 10, &quot;26 Oct 2022 22 :01&quot; ], [ &quot;field1value2&quot;, &quot;field2value2&quot;, 20, &quot;28 Oct 2022 15:22&quot; ], [ &quot;field1value3&quot;, &quot;field2value3&quot;, 30, &quot;17 Oct 2022 12:46&quot; ] ] }\n<\/pre><\/div>\n\n\n<p>This JSON file consists of two arrays:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>fields;<\/li><li>results.<\/li><\/ul>\n\n\n\n<p>In the list \u201c<strong>fields<\/strong>\u201d shows the column names and what data type the column contains. In &quot;<strong>results<\/strong>\u201d the values of the relevant columns are displayed in a structured manner.<\/p>\n\n\n\n<p>We have four columns:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>field1;<\/li><li>field2;<\/li><li>field3;<\/li><li>eventTimestamp.<\/li><\/ul>\n\n\n\n<p>We want to import the JSON into Power BI as a tabular form, resulting in the table below.<\/p>\n\n\n\n<div class=\"wp-block-wpdatatables-wpdatatables-gutenberg-block\">\n<div class=\"wpdt-c wdt-skin-light\">\n    \n<input type=\"hidden\" id=\"wdtNonceFrontendEdit_26\" name=\"wdtNonceFrontendEdit_26\" value=\"7842376c79\" \/><input type=\"hidden\" name=\"_wp_http_referer\" value=\"\/en\/wp-json\/wp\/v2\/posts\/2133\" \/>    <input type=\"hidden\" id=\"table_1_desc\"\n           value='{\"tableId\":\"table_1\",\"tableType\":\"manual\",\"selector\":\"#table_1\",\"responsive\":false,\"responsiveAction\":\"icon\",\"editable\":false,\"inlineEditing\":false,\"infoBlock\":false,\"pagination\":0,\"paginationAlign\":\"right\",\"paginationLayout\":\"full_numbers\",\"paginationLayoutMobile\":\"simple\",\"file_location\":\"\",\"tableSkin\":\"light\",\"scrollable\":false,\"globalSearch\":false,\"showRowsPerPage\":false,\"popoverTools\":false,\"hideBeforeLoad\":false,\"number_format\":1,\"decimalPlaces\":2,\"spinnerSrc\":\"https:\\\/\\\/www.bjorn-meijer.nl\\\/wp-content\\\/plugins\\\/wpdatatables\\\/assets\\\/\\\/img\\\/spinner.gif\",\"groupingEnabled\":false,\"tableWpId\":26,\"dataTableParams\":{\"sDom\":\"BT\\u003C\\u0027clear\\u0027\\u003Et\",\"bSortCellsTop\":false,\"bFilter\":true,\"aLengthMenu\":[[1,5,10,25,50,100,-1],[1,5,10,25,50,100,\"All\"]],\"iDisplayLength\":10,\"columnDefs\":[{\"sType\":\"formatted-num\",\"wdtType\":\"int\",\"bVisible\":false,\"orderable\":true,\"searchable\":true,\"InputType\":\"text\",\"name\":\"wdt_ID\",\"origHeader\":\"wdt_ID\",\"notNull\":false,\"conditionalFormattingRules\":[],\"className\":\"numdata integer  column-wdt_id\",\"aTargets\":[0]},{\"sType\":\"string\",\"wdtType\":\"string\",\"bVisible\":true,\"orderable\":true,\"searchable\":true,\"InputType\":\"text\",\"name\":\"field1\",\"origHeader\":\"field1\",\"notNull\":false,\"conditionalFormattingRules\":[],\"className\":\" column-field1\",\"aTargets\":[1]},{\"sType\":\"string\",\"wdtType\":\"string\",\"bVisible\":true,\"orderable\":true,\"searchable\":true,\"InputType\":\"text\",\"name\":\"field2\",\"origHeader\":\"field2\",\"notNull\":false,\"conditionalFormattingRules\":[],\"className\":\" column-field2\",\"aTargets\":[2]},{\"sType\":\"formatted-num\",\"wdtType\":\"int\",\"bVisible\":true,\"orderable\":true,\"searchable\":true,\"InputType\":\"text\",\"name\":\"field3\",\"origHeader\":\"field3\",\"notNull\":false,\"conditionalFormattingRules\":[],\"className\":\"numdata integer  column-field3\",\"aTargets\":[3]},{\"sType\":\"date-custom\",\"wdtType\":\"datetime\",\"bVisible\":true,\"orderable\":true,\"searchable\":true,\"InputType\":\"datetime\",\"name\":\"eventtimestamp\",\"origHeader\":\"eventtimestamp\",\"notNull\":false,\"conditionalFormattingRules\":[],\"className\":\" column-eventtimestamp\",\"aTargets\":[4]}],\"bAutoWidth\":false,\"order\":[[0,\"asc\"]],\"ordering\":false,\"fixedHeader\":{\"header\":false,\"headerOffset\":0},\"fixedColumns\":false,\"oLanguage\":{\"sProcessing\":\"Bezig...\",\"sLengthMenu\":\"_MENU_ resultaten weergeven\",\"sZeroRecords\":\"Geen resultaten gevonden\",\"sInfo\":\"_START_ tot _END_ van _TOTAL_ resultaten\",\"sInfoEmpty\":\"Geen resultaten om weer te geven\",\"sInfoFiltered\":\" (gefilterd uit _MAX_ resultaten)\",\"sInfoPostFix\":\"\",\"sSearch\":\"Zoeken:\",\"sSearchPlaceholder\":\"\",\"sEmptyTable\":\"Geen resultaten aanwezig in de tabel\",\"sInfoThousands\":\".\",\"sLoadingRecords\":\"Een moment geduld aub - bezig met laden...\",\"oPaginate\":{\"sFirst\":\"Eerste\",\"sLast\":\"Laatste\",\"sNext\":\"Volgende\",\"sPrevious\":\"Vorige\"}},\"buttons\":[],\"bProcessing\":false,\"serverSide\":true,\"ajax\":{\"url\":\"https:\\\/\\\/www.bjorn-meijer.nl\\\/wp-admin\\\/admin-ajax.php?action=get_wdtable&table_id=26\",\"type\":\"POST\"},\"oSearch\":{\"bSmart\":false,\"bRegex\":false,\"sSearch\":\"\"}},\"tabletWidth\":\"1024\",\"mobileWidth\":\"480\",\"renderFilter\":\"footer\",\"advancedFilterEnabled\":false,\"serverSide\":true,\"autoRefreshInterval\":0,\"processing\":true,\"fnServerData\":true,\"columnsFixed\":0,\"sumFunctionsLabel\":\"\",\"avgFunctionsLabel\":\"\",\"minFunctionsLabel\":\"\",\"maxFunctionsLabel\":\"\",\"columnsDecimalPlaces\":{\"wdt_ID\":-1,\"field1\":-1,\"field2\":-1,\"field3\":-1,\"eventtimestamp\":-1},\"columnsThousandsSeparator\":{\"wdt_ID\":0,\"field3\":1},\"sumColumns\":[],\"avgColumns\":[],\"sumAvgColumns\":[],\"timeFormat\":\"H:i\",\"datepickFormat\":\"dd M yy\"}'\/>\n\n    <table id=\"table_1\"\n           class=\"display nowrap data-t data-t wpDataTable wpDataTableID-26\"\n           style=\"\"\n           data-described-by='table_1_desc'\n           data-wpdatatable_id=\"26\">\n        \n        <!-- Table header -->\n        \n<thead>\n<tr>\n                    <th\n                        class=\"wdtheader sort numdata integer\"\n        style=\"\">wdt_ID<\/th>        <th\n        data-class=\"expand\"                class=\"wdtheader sort\"\n        style=\"\">field1<\/th>        <th\n                        class=\"wdtheader sort\"\n        style=\"\">field2<\/th>        <th\n                        class=\"wdtheader sort numdata integer\"\n        style=\"\">field3<\/th>        <th\n                        class=\"wdtheader sort\"\n        style=\"\">eventTimestamp<\/th>    <\/tr>\n<\/thead>\n        <!-- \/Table header -->\n\n        <!-- Table body -->\n        \n<tbody>\n            <tr id=\"table_26_row_0\">\n                            <td style=\"\">1<\/td>\n                            <td style=\"\">field1value1<\/td>\n                            <td style=\"\">field2value2<\/td>\n                            <td style=\"\">10<\/td>\n                            <td style=\"\">26 Oct 2022 22:01<\/td>\n                    <\/tr>\n                <tr id=\"table_26_row_1\">\n                            <td style=\"\">4<\/td>\n                            <td style=\"\">field1value2<\/td>\n                            <td style=\"\">field2value2<\/td>\n                            <td style=\"\">20<\/td>\n                            <td style=\"\">28 Oct 2022 15:22<\/td>\n                    <\/tr>\n                <tr id=\"table_26_row_2\">\n                            <td style=\"\">6<\/td>\n                            <td style=\"\">field1value3<\/td>\n                            <td style=\"\">field2value3<\/td>\n                            <td style=\"\">30<\/td>\n                            <td style=\"\">17 Oct 2022 12:46<\/td>\n                    <\/tr>\n    <\/tbody>        <!-- \/Table body -->\n\n        <!-- Table footer -->\n        \n        <!-- \/Table footer -->\n    <\/table>\n\n<\/div><style>\ntable.wpDataTable td.numdata { text-align: right !important; }\n<\/style>\n<style>\n<\/style>\n<\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-gegevens-ophalen-uit-een-bron\">Retrieving data from a source<\/h2>\n\n\n\n<p>Start Power BI and create a new report.<br>We start by getting the data from the JSON file. For this we go to the tab <strong>Start<\/strong> and click the button <strong>Retrieve data<\/strong>.<\/p>\n\n\n\n<p>A new window will open. If in the right column of the window you do not have a <strong>JSON<\/strong> You can enter \u201cJSON\u201d in the search box.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1298\" height=\"850\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/Import-JSON-in-Power-BI.gif\" alt=\"Import JSON source file into Power BI\" class=\"wp-image-2107\"\/><figcaption>Import JSON source file into Power BI<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-de-power-query-editor\">The Power Query Editor<\/h2>\n\n\n\n<p>The Power BI Power Query Editor opens with the imported JSON file. In the table you see two columns called <strong>fields<\/strong> and <strong>results<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"818\" height=\"161\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-geconverteerd-naar-tabel.jpg\" alt=\"Power BI Query Editor\" class=\"wp-image-2108\" srcset=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-geconverteerd-naar-tabel.jpg 818w, https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-geconverteerd-naar-tabel-300x59.jpg 300w, https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-geconverteerd-naar-tabel-768x151.jpg 768w, https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-geconverteerd-naar-tabel-18x4.jpg 18w\" sizes=\"(max-width: 818px) 100vw, 818px\" \/><figcaption>Power BI Query Editor<\/figcaption><\/figure>\n\n\n\n<p>In the table there is one row with a <strong>list<\/strong>. Click next to the text <strong>list<\/strong> of the column \u201c<strong>fields<\/strong>\u201d and you will see at the bottom of your screen (in the preview) that it contains four records. These are the column names.<br>Click next to the text <strong>list<\/strong> of the column \u201c<strong>results<\/strong>\u201c. You can see that it contains three records. These are the number of rows.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-uitvouwen-naar-rijen\">Expand to Rows<\/h3>\n\n\n\n<p>Click in the column \u201c<strong>results<\/strong>\u201d on the button with the two arrows click on <strong>Expand to new rows<\/strong>.<\/p>\n\n\n\n<p>The table has now expanded from one to three rows. Click next to the text <strong>list<\/strong> of the column \u201c<strong>results<\/strong>\u201d you will see the values of the row in question at the bottom of your screen (in the preview).<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1033\" height=\"627\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Uitvouwen-naar-nieuwe-rijen.gif\" alt=\"Power BI Query Editor Expand to Rows\" class=\"wp-image-2109\"\/><figcaption>Power BI Query Editor Expand to Rows<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-kolom-toevoegen\">Add column<\/h3>\n\n\n\n<p>Click on the tab <strong>Add column<\/strong> and click the button <strong>Custom column<\/strong>. A new window will open. Fill in the following formula:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\"><pre class=\"brush: plain; gutter: false; title: ; notranslate\" title=\"\">\nTable.Transpose(Table.FromList([results], Splitter.SplitByNothing(), null, null, ExtraValues.Error))\n<\/pre><\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1321\" height=\"713\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Aangepaste-kolom-toevoegen.gif\" alt=\"Power BI Query Editor Add custom column\" class=\"wp-image-2110\"\/><figcaption>Power BI Query Editor Add custom column<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Delete other columns<\/h3>\n\n\n\n<p>Right click on the column name \u201c<strong>Amended<\/strong>\u201d which has just been created and choose <strong>Delete other columns<\/strong>. You now only keep the column \u201c<strong>Amended<\/strong>&quot; about.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1321\" height=\"713\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Andere-kolommen-verwijderen.gif\" alt=\"Power BI Query Editor Delete other columns\" class=\"wp-image-2111\"\/><figcaption>Power BI Query Editor Delete other columns<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Expand column to rows<\/h3>\n\n\n\n<p>Click in the column \u201c<strong>Amended<\/strong>\u201d on the button with the two arrows and make sure the option \u201c<strong>Unfold<\/strong>\u201d is selected and click the \u201c<strong>OK<\/strong>&#8220;.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1321\" height=\"713\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Aangepast-uitvouwen-naar-rijen.gif\" alt=\"Power BI Query Editor Expand column to rows\" class=\"wp-image-2112\"\/><figcaption>Power BI Query Editor Expand column to rows<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Customize column names<\/h2>\n\n\n\n<p>The next step is to add column names which are in the JSON file. We do this by adjusting the current table names to the values in the column \u201c<strong>fields<\/strong>&#8220;.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-toevoegen-nieuwe-query\">Add New Query<\/h3>\n\n\n\n<p>On the right side of the Power Query editor you can see the applied steps. Stand on the third step (<strong>Type changed<\/strong>) and then right-click on the column name \u201c<strong>fields<\/strong>\u201c. From the context menu choose <strong>Add as new query<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1584\" height=\"745\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Toevoegen-als-nieuwe-query.gif\" alt=\"Power BI Query Editor Add new query\" class=\"wp-image-2113\"\/><figcaption>Power BI Query Editor Add new query<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-converteren-naar-tabel\">Convert to Table<\/h3>\n\n\n\n<p>Go to the tab <strong>transform<\/strong> and click the button <strong>To table<\/strong> in the category <strong>To convert<\/strong>.<br>A new window will open (<strong>To table<\/strong>) and click here on the button \u201c<strong>OK<\/strong>&#8220;.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1315\" height=\"689\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Converteren-naar-tabel.gif\" alt=\"Power BI Query Editor Convert to Table\" class=\"wp-image-2114\"\/><figcaption>Power BI Query Editor Convert to Table<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Expand to new rows<\/h3>\n\n\n\n<p>Click on the button with the two arrows of the column and choose <strong>Expand to new rows<\/strong>. After the rows have been expanded, click the button with the two arrows again. Press the button &quot;<strong>OK<\/strong>&#8220;.<\/p>\n\n\n\n<p>As a result, we see the column names in the first column and the data type in the second column.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1315\" height=\"689\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Rijen-uitvouwen.gif\" alt=\"Power BI Query Editor Expand Rows\" class=\"wp-image-2115\"\/><figcaption>Power BI Query Editor Expand Rows<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Customize column names in table of values<\/h3>\n\n\n\n<p>Select the table containing the data (sample). Then click from the tab <strong>Start<\/strong> on the button <strong>Advanced Editor<\/strong> in the category <strong>query<\/strong>.<\/p>\n\n\n\n<p>A new window will open in which you can adjust the so-called M-code. Adjust the M-code to the example below.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\"><pre class=\"brush: plain; gutter: false; title: ; notranslate\" title=\"\">\n let Source = Json.Document(File.Contents(&quot;C:\\Users\\BjornM\\OneDrive - deruitergwt.nl\\Documents\\Power BI\\Convert JSON\\sample.json&quot;)), #&quot;Converted to table&quot; = Table.FromRecords ({Source}), #&quot;Type changed&quot; = Table.TransformColumnTypes(#&quot;Converted to table&quot;,{{&quot;fields&quot;, type any}, {&quot;results&quot;, type any}}), #&quot;results expanded&quot; = Table.ExpandListColumn(#&quot;Type changed&quot;, &quot;results&quot;), #&quot;Custom column added&quot; = Table.AddColumn(#&quot;results expanded&quot;, &quot;Custom&quot;, each Table.Transpose(Table.FromList([results], Splitter .SplitByNothing(), null, null, ExtraValues.Error))), #&quot;Other columns removed&quot; = Table.SelectColumns(#&quot;Custom column added&quot;,{&quot;Custom&quot;}), #&quot;Custom expanded&quot; = Table.ExpandTableColumn (#&quot;Other columns removed&quot;, &quot;Custom&quot;, {&quot;Column1&quot;, &quot;Column2&quot;, &quot;Column3&quot;, &quot;Column4&quot;}, {&quot;Custom.Column1&quot;, &quot;Custom.Column2&quot;, &quot;Custom.Column3&quot;, &quot; Custom.Column4&quot;}), #&quot;Custom Expanded Column Names&quot; = Table.RenameColumns(#&quot;Custom Expanded&quot;,List.Zip({Table.ColumnNames(#&quot;Custom Expanded&quot;),#&quot;fields&quot;[Column1.field]}) ) in #&quot;Customize column names&quot;\n<\/pre><\/div>\n\n\n<p>Then press the button \u201c<strong>OK<\/strong>\u201d to apply the changes. As a result, you will see that all column names have been modified.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1584\" height=\"865\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Kolomnamen-aanpassen.gif\" alt=\"Power BI Query Editor Customize column names\" class=\"wp-image-2116\"\/><figcaption>Power BI Query Editor Customize column names<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Customize data type<\/h3>\n\n\n\n<p>The last step is to change the data type of each column. For this we use a function. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Create function<\/h3>\n\n\n\n<p>Click from the tab <strong>Start<\/strong> on <strong>New source<\/strong> and choose a <strong>Empty query<\/strong>.<\/p>\n\n\n\n<p>Enter the code below and change the query name to &quot;<strong>ConvertType<\/strong>&#8220;.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\"><pre class=\"brush: plain; gutter: false; title: ; notranslate\" title=\"\">\n= (input as text) as type =&gt; let values = { {&quot;string&quot;, type text}, {&quot;integer&quot;, type number}, {&quot;date&quot;, type datetimezone}, {input, type any} }, Result = List.First(List.Select(values, each _{0}=input)){1} in Result\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">Edit data type of column names<\/h3>\n\n\n\n<p>In the table &quot;<strong>results<\/strong>\u201d let&#039;s add the last step to give each column the correct data type.<\/p>\n\n\n\n<p>Select the table containing the data (sample). Then click from the tab <strong>Start<\/strong> on the button <strong>Advanced Editor<\/strong> in the category <strong>query<\/strong>.<\/p>\n\n\n\n<p>A new window will open in which you can adjust the so-called M-code. Adjust the M-code to the example below.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nlet Source = Json.Document(File.Contents(&quot;C:\\Users\\BjornM\\Downloads\\Convert JSON\\sample.json&quot;)), #&quot;Converted to Table&quot; = Record.ToTable(Source), #&quot;Expanded Value&quot; = Table.ExpandListColumn(#&quot;Converted to Table&quot;, &quot;Value&quot;), #&quot;Filtered Rows&quot; = Table.SelectRows(#&quot;Expanded Value&quot;, each ([Name] = &quot;results&quot;)), #&quot;Added Custom&quot; = Table.AddColumn(#&quot;Filtered Rows&quot;, &quot;Custom&quot;, each Table.Transpose(Table.FromList([Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error))), #&quot;Removed Other Columns &quot; = Table.SelectColumns(#&quot;Added Custom&quot;,{&quot;Name&quot;, &quot;Custom&quot;}), #&quot;Expanded Custom&quot; = Table.ExpandTableColumn(#&quot;Removed Other Columns&quot;, &quot;Custom&quot;, {&quot;Column1&quot;, &quot; Column2&quot;, &quot;Column3&quot;, &quot;Column4&quot;}, {&quot;Custom.Column1&quot;, &quot;Custom.Column2&quot;, &quot;Custom.Column3&quot;, &quot;Custom.Column4&quot;}), #&quot;Removed Columns&quot; = Table.RemoveColumns(# &quot;Expanded Custom&quot;,{&quot;Name&quot;}), #&quot;Custom Rename&quot; = Table.RenameColumns(#&quot;Removed Columns&quot;,List.Zip({Table.ColumnNames(#&quot;Removed Columns&quot;)),#&quot;fields&quot;[field ]})), #&quot;Custom ChangeType&quot; = Table.TransformColumnTypes(#&quot;Custom Rename&quot;,List.Zip({Table.ColumnNames(#&quot;Custom Rename&quot;)),List.Transform(#&quot;fields&quot;[Column1.type] , each ConvertType(_))})) in #&quot;Custom ChangeType&quot;\n<\/pre><\/div>\n\n\n<p>Then press the button \u201c<strong>OK<\/strong>\u201d to apply the changes. As a result, you see that the data types of all columns have been adjusted.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"1584\" height=\"865\" src=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/JSON-Gegevenstype-aanpassen.gif\" alt=\"Power BI Query Editor Change data type\" class=\"wp-image-2117\"\/><figcaption>Power BI Query Editor Change data type<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"downloads\"> <i class=\"fas fa-download\"><\/i> Downloads <\/h2>\n\n\n\n<p>If you can&#039;t figure it out with the above guide alone, download the sample file or ask a question by leaving a message.<\/p>\n\n\n\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-6540abfc-fac2-40dc-a43c-01e7df20a2fe\" href=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/Convert-JSON.zip\">Convert JSON<\/a><a href=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/Convert-JSON.zip\" class=\"wp-block-file__button\" download aria-describedby=\"wp-block-file--media-6540abfc-fac2-40dc-a43c-01e7df20a2fe\"><i class=\"fas fa-file-download\"><\/i> Download<\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>Suppose you have a JSON file that looks like this and you want to import it as a table into Power BI: This\u2026 <a class=\"read-more\" href=\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\">Continue reading<\/a><\/p>","protected":false},"author":1,"featured_media":2122,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[46,89],"tags":[93,47,91,90,94,92],"class_list":["post-2133","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-json","category-power-bi","tag-array","tag-json","tag-json-list","tag-power-bi","tag-power-query","tag-transponeren"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v20.11 (Yoast SEO v23.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>JSON array converteren naar tabel in Power BI - Bj\u00f6rn Meijer<\/title>\n<meta name=\"description\" content=\"Wat moet je doen om een JSON array als datatabel in Power BI? In dit artikel wordt stap voor stap uitgelegd hoe je dit eenvoudig kunt doen.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"JSON array converteren naar tabel in Power BI\" \/>\n<meta property=\"og:description\" content=\"Stel je hebt een JSON bestand welke er als volgt uitziet en als tabel willen importeren in Power BI: { &quot;fields&quot;: , &quot;results&quot;: , , ] } Dit JSON-bestand\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Bj\u00f6rn Meijer\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-26T21:14:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-10-26T21:15:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png\" \/>\n\t<meta property=\"og:image:width\" content=\"300\" \/>\n\t<meta property=\"og:image:height\" content=\"300\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Bj\u00f6rn Meijer\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bj\u00f6rn Meijer\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\"},\"author\":{\"name\":\"Bj\u00f6rn Meijer\",\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615\"},\"headline\":\"JSON array converteren naar tabel in Power BI\",\"datePublished\":\"2022-10-26T21:14:32+00:00\",\"dateModified\":\"2022-10-26T21:15:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\"},\"wordCount\":855,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615\"},\"image\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png\",\"keywords\":[\"array\",\"json\",\"json list\",\"power BI\",\"Power Query\",\"transponeren\"],\"articleSection\":[\"JSON\",\"Power BI\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\",\"url\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\",\"name\":\"JSON array converteren naar tabel in Power BI - Bj\u00f6rn Meijer\",\"isPartOf\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png\",\"datePublished\":\"2022-10-26T21:14:32+00:00\",\"dateModified\":\"2022-10-26T21:15:54+00:00\",\"description\":\"Wat moet je doen om een JSON array als datatabel in Power BI? In dit artikel wordt stap voor stap uitgelegd hoe je dit eenvoudig kunt doen.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage\",\"url\":\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png\",\"contentUrl\":\"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png\",\"width\":300,\"height\":300,\"caption\":\"Power BI\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.bjorn-meijer.nl\/de\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"JSON array converteren naar tabel in Power BI\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#website\",\"url\":\"https:\/\/www.bjorn-meijer.nl\/de\/\",\"name\":\"Bj\u00f6rn Meijer\",\"description\":\"At My Playground\",\"publisher\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.bjorn-meijer.nl\/de\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615\",\"name\":\"Bj\u00f6rn Meijer\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/i0.wp.com\/www.bjorn-meijer.nl\/wp-content\/uploads\/2021\/11\/logo-orange.png?fit=165%2C165&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/www.bjorn-meijer.nl\/wp-content\/uploads\/2021\/11\/logo-orange.png?fit=165%2C165&ssl=1\",\"width\":165,\"height\":165,\"caption\":\"Bj\u00f6rn Meijer\"},\"logo\":{\"@id\":\"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/www.bjorn-meijer.nl\"]}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"JSON array converteren naar tabel in Power BI - Bj\u00f6rn Meijer","description":"Wat moet je doen om een JSON array als datatabel in Power BI? In dit artikel wordt stap voor stap uitgelegd hoe je dit eenvoudig kunt doen.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/","og_locale":"en_US","og_type":"article","og_title":"JSON array converteren naar tabel in Power BI","og_description":"Stel je hebt een JSON bestand welke er als volgt uitziet en als tabel willen importeren in Power BI: { \"fields\": , \"results\": , , ] } Dit JSON-bestand","og_url":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/","og_site_name":"Bj\u00f6rn Meijer","article_published_time":"2022-10-26T21:14:32+00:00","article_modified_time":"2022-10-26T21:15:54+00:00","og_image":[{"width":300,"height":300,"url":"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png","type":"image\/png"}],"author":"Bj\u00f6rn Meijer","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Bj\u00f6rn Meijer","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#article","isPartOf":{"@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/"},"author":{"name":"Bj\u00f6rn Meijer","@id":"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615"},"headline":"JSON array converteren naar tabel in Power BI","datePublished":"2022-10-26T21:14:32+00:00","dateModified":"2022-10-26T21:15:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/"},"wordCount":855,"commentCount":0,"publisher":{"@id":"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615"},"image":{"@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png","keywords":["array","json","json list","power BI","Power Query","transponeren"],"articleSection":["JSON","Power BI"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/","url":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/","name":"JSON array converteren naar tabel in Power BI - Bj\u00f6rn Meijer","isPartOf":{"@id":"https:\/\/www.bjorn-meijer.nl\/de\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage"},"image":{"@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png","datePublished":"2022-10-26T21:14:32+00:00","dateModified":"2022-10-26T21:15:54+00:00","description":"Wat moet je doen om een JSON array als datatabel in Power BI? In dit artikel wordt stap voor stap uitgelegd hoe je dit eenvoudig kunt doen.","breadcrumb":{"@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#primaryimage","url":"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png","contentUrl":"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png","width":300,"height":300,"caption":"Power BI"},{"@type":"BreadcrumbList","@id":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/10\/26\/json-array-convert-to-table-in-power-bi-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.bjorn-meijer.nl\/de\/"},{"@type":"ListItem","position":2,"name":"JSON array converteren naar tabel in Power BI"}]},{"@type":"WebSite","@id":"https:\/\/www.bjorn-meijer.nl\/de\/#website","url":"https:\/\/www.bjorn-meijer.nl\/de\/","name":"Bj\u00f6rn Meijer","description":"At My Playground","publisher":{"@id":"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.bjorn-meijer.nl\/de\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/3621be5a6ce9a9884a7b8b200cd52615","name":"Bj\u00f6rn Meijer","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/image\/","url":"https:\/\/i0.wp.com\/www.bjorn-meijer.nl\/wp-content\/uploads\/2021\/11\/logo-orange.png?fit=165%2C165&ssl=1","contentUrl":"https:\/\/i0.wp.com\/www.bjorn-meijer.nl\/wp-content\/uploads\/2021\/11\/logo-orange.png?fit=165%2C165&ssl=1","width":165,"height":165,"caption":"Bj\u00f6rn Meijer"},"logo":{"@id":"https:\/\/www.bjorn-meijer.nl\/de\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/www.bjorn-meijer.nl"]}]}},"jetpack_featured_media_url":"https:\/\/www.bjorn-meijer.nl\/wp-content\/uploads\/2022\/10\/powerbi-logo.png","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/posts\/2133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/comments?post=2133"}],"version-history":[{"count":1,"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/posts\/2133\/revisions"}],"predecessor-version":[{"id":2136,"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/posts\/2133\/revisions\/2136"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/media\/2122"}],"wp:attachment":[{"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/media?parent=2133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/categories?post=2133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bjorn-meijer.nl\/en\/wp-json\/wp\/v2\/tags?post=2133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}