Extracting Data with Power Query

This part of the guidance will talk you through the steps to get the data through Power Query once you have hold of all the pieces for the query.

Power Query DAX Query Input

1. In Power BI open a blank query source. This will open up Power Query Editor.






2. In the Home tab select 'Advanced Editor'.






3. Use the query template provided towards the end of the Using Stat-Xplore API in Power BI, with the highlighted sections replaced appropriately.
4. Click the Done in the bottom right corner and you should see a table in Power Query Editor like the one shown below.

Transforming Table in Power Query

When we channel the data into Power Query it doesn't look as expected. We will need to do some manipulation in Power Query to create the table with the fields and values we want.

The Field Names and Values will need to be extracted separately into two tables which we can then merge at the end.

The Field Names in this example are the Regions and Month.

Values refer to the actual numbers.

1. Right click the query title in the left column and duplicate. We will need two queries - one to ectract the field labels and another to extract the values.






2. Label them appropriately so you can distinguish between the query for the Field Names and the query for the Values.

Expanding Field Names Query

1. Expand the list value under fields.






2. You will get a list of records with as many columns as you added to your Stat-Xplore table. Convert this to table by clicking To Table.






3. Click OK.






4. Now in the Transform tab click the Transpose option so that the records become the columns.






5. Beginning with the first column click the expand the square in the right corner of the Column1 label and deselect everything except items. Click OK.






6. The record value will change to List. Now click the expand button again and Expand to new rows.






7. You will now see a list of records. Click the expand button again and deselect all items except labels.






8. The list of records will all have become List values. Click the expand square and Expand to New Rows again






9. And you should now have your field names for that column. Repeat steps 4-7 for any remaining columns.






10. Lastly add an index column. This can be found in the Add Column tab.

Expanding Values Query

1. To begin expanding the Value Query, first switch tabs to the 'Values' one we created earlier.






2. Now to extract the values we want to click the 'Record' assigned to the cubes field.






3. Click 'Record' again.






4. Select 'List'.






5. Convert To Table.






6. Expand to New Rows by selecting the small expand square in the corner.






7. Again, add an index cloumn so that the result is as shown below.

Merging Query Tables

1. The values are extracted in the same order as the field names. So we can do a merge on the index columns to create one table, where the rows will accurately match up.
2. Make sure you are on the Field Names query first. Under the home tab select 'Merge Queries'.






3. On the drop down below select the 'Values' query. Click the index column from both tables so that they are highlighted grey.
4. Make sure that the number of rows match at the bottom. This check will make sure that everything has been extracted correctly.






5. If everything looks correct click OK.
6. The field names table will now have a column with the Values table. Click the expand the button from the Values colummn and deselct the Index column as shown. Click OK.






7. You will now have your completed table. You can rename columns and transform data as you wish. Close and apply to begin visualising in Power BI.