Skip to content
Home » power bi

power bi

Visit Tacticalware.com for information related to this tag and others. We look forward to assisting in your tech projects with our in-depth knowledgebase…

POWER BI CALENDAR / DATE TABLE

  • Power BI

If your data warehouse does not have a date table or a time table, how do you create it within Power BI….well here you go

In Power BI
Click the Modeling tab
Select New Table
Type in:
Calendar = CALENDARAUTO(6)


Note:
CALENDAR – You will supply the Start and End Date
CALENDARAUTO – Looks through all the dates in the model and automatically builds out the range. If your model begins on July 15, 1979 and your fiscal year end is June 30th, if you set CALENDARAUTO(6) it will allow all dates from July 15, 1979 to June 30, 2021. If you leave CALENDARAUTO() blank, it will go to the end of the year (December 31).

POWER QUERY – SETTING PARAMETERS

  • Power BI

To use parameters, which expecially helps when moving files from your local computer to the web service, perform the following

Open Power Query Editor
Click Home
Select Manage Parameters
Click New
Give it a Name (tacticalware)
Specify a Type from the drop down. For me I selected Text
In the Current Value field, specify the location of the csv file (C:\tacticalware\x.csv)
NOTE – it is case sensitive
Click Ok

Now go into your Advanced Editor
Find the location of the csv file (C:\tacticalware\x.csv)
Replace the path with the Name of the parameter (tacticalware)
So it will look like this:
Source = Csv.Document(File.Contents(tacticalware),……and so on
Click Done


POWER BI – QUERY FOLDING

  • Power BI

What is Power Bi Query Folding…..
– It reduces trips to the data source
– Pushes transformations to the data source
– It combines steps into a single statement that is sent to a data source
– Some operations may prevent query folding from happening
– It is supported by SQL Server
– Also supported by relational data sources

What it is NOT
– This is Not Available with all data sources

To see the steps, Enter into your Advanced Editor. It will show you the M Language here.

To view the SQL Server code that is generated:
In Power Query > Applied Steps section
Right click on a step, and select View Native Query
This will show you the SQL Server Code that will be sent back to the server for that particular step. However it does not sent the code back to SQL Server after each step. It sends it at the end when you Apply or Close & Apply

CREATING A LOCAL MSSQL SERVER ON YOUR W10 PC

From time to time you will need to utilize a local sql server, for dev, testing, education, whatever. So here is a quick how-to on creating a local mssql server on your W10 pc.

Download and install the free version of SQL, called SQL Express:
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Accept all the defaults, except for these few
Use your credentials when prompted
Select all packages to be installed

Once SQL Express is installed, download and install SQL Server Management Studio
https://aka.ms/ssmsfullsetup

Now you have both installed, open SQL Server Management Studio or SSMS for short, and connect to your local SQL Express Database
Connect to “localhost\sqlexpress“
You dont have to put in any credentials if you selected to use your credentials earlier.

To do a test on the database, try restoring the AdventureWorksDW database
Right click on Databases
Select Restore database
Select the AdventureWorksDW file
Click Ok

You can now connect into your database using Power BI or another tool, and start playing around.

Thanks for reading

CONNECT POWER BI TO COINMARKETCAP

Needless to say, I have been mining a while, and have some crypto that I want to keep track of. How do you do this, you ask….Well I just have to Connect Power Bi to CoinMarketCap, list my coins, and off I go. This quick and easy guide is how I will connect Power BI to CoinMarketCap.

Open Power BI
Click File
Select New
Click Get Data
Select From Web
Click Advanced
For URL Parts you will want to enter in the following:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest
In the HTTP request header parameters(optional) first box, type in
X-CMC_PRO_API_KEY
In the Second Box you will want to enter in your CoinMarketCap API Key

To get the API Key, use the web browser on your computer (Not inside Power BI) and go to
https://pro.coinmarketcap.com/account
Sign up for an account if you don’t already have one
If you do have an account, login
Hover over the API Key box and click Copy

Back to Power BI
Now in the Second Box you will want to enter in your CoinMarketCap API Key
Click Ok
Power BI will now connect to CoinMarketCap

In Power Query
Click data
Select list
Select the To Table button
Use defaults and Click OK
In the Column header, click the button on the top right
Click (Select All Columns)
Select Ok
Click the the Column1.tags header, click the button on the top right
Select Extract Values
Hit the Drop Down
Select Comma
Click Ok
Click the the Column1.quote header, click the button on the top right
Place a checkmark next to USD
Click Ok
Click the the Click the the Column1.quote header, click the button on the top right
Place a checkmark next to USD
Click the the Click the the Column1.quote.USD header, click the button on the top
Click (Select All Columns)
Select Ok

Click the Save Icon at the top left

Click Close and Apply




Documentation on the various CoinMarketCap commands can be found at https://coinmarketcap.com/api/documentation/v1/#section/Endpoint-Overview

POWER BI SHAPE MAP

  • Power BI

When you install Power BI, it does not have certain features enabled. One feature that I need to use, is the Power Bi Shape Map. This quick how-to guide is the way to enable that feature.

Open Power BI
Click File
Click Options and Settings
Click Options
Click Preview Features
Place a check mark next to Shape Map Visual
Click Ok
Now restart Power BI for the changes to take effect

And there you have it, a quick way to enable the Power BI Shape Map