Post series: Overview | Running the tool | Processing the data
This post covers how to process and import the data into PowerBI that we gathered earlier in the Network Assessment Plus process
Having run the Assessment Tool we now need to gather, process and import the results into PowerBI.
The looping script will have written out two files to its current path:
NetworkTest_PCNAME_ExtraInfo.csv – This contains the PC Name, OS culture, example date/time format, network interface, IP address
NetworkTest_PCNAME_Results.tsv – This is the combined results for of all the tests run from that computer
Import TSV into Excel
- Open Excel and open the results.tsv file
- Excel should fire up it’s “text import wizard”
- Tick the “my data has headers” box
- Move to the next screen & make sure the delimiter is set to “Tab”
- From here you can just hit Finish and jump out into Excel which should look something like this:
- Check that the data looks good by expanding out the columns and then add “site” as a header to Column H. Fill in the column with a short name that represents where this data came from
- Check down to the bottom of the file and make sure everything looks good
Next we want to un-mangle the date / time column as Excel will truncate it if we export at this point..
Note: If you have collected multiple files from different parts of the world you want to standardise the date/time format across your files at this point so that PowerBI will treat them all the same when imported. For this example we are assuming UK format, because hey, that’s where I’m from
- Select Column A, then select “More number formats” in the Type drop down
- Then set the type to “Custom” and enter “dd/mm/yyyy hh:mm:ss” (or whatever you want for your culture setting”)
- Hit Okay and you should see the full date/time appear in Column A
- Now we’re done in Excel and can save the file out as a CSV
- Open the file in a text editor and check things look good
Now repeat the process for any other files you want to included in the analysis. Make sure you don’t have any overlapping site codes etc.. or your results will get muddled together.
Combine and import
- Once all the files have been processed you want to combine them into a single .CSV I suggest using Network_Assessment_Combined.csv from the root of the tool download
- Don’t forget to delete the existing contents & don’t include the headers from the extra files you want to use.
- Once the files are all combined save back the CSV and hop into PowerBI.
- Open the latest “Network_Assessment_Plus_PowerBI_vX.pbix” file
- From the “Edit Queries” menu select “Data Source Settings”
- Click “Change Source”
- Select your new “combined” file and click okay then close
- You should see a yellow information box for pending changes, hit apply and watch the data refresh
- All things being well you should see the new site names appear on the left of the output
Note: If you just want to update the PowerBI file without changing the source just hit “refresh” from the tool menu
Now head back to the Overview post for information about how to play with your new PowerBI visualisation!
Comments & feedback welcome on how you found the process and where I can improve. Thank you