Connect Azure Virtual Machines on a Same Domain
Connecting multiple machines on the same domain would be one of the requirement to all the network administrator to set up the environment for any scenario.
This post explains this scenario how administrators can they do this setup easily.
Requirement:
- Azure Virtual machine with Domain controller
- Virtual Network
- Another VMs
I pretend that you have a machine with a domain controller on it.
While creating another machine, you need to choose the first machine’s virtual network instead of creating a new one as like below.
Next, you can identify the IP address by typing ipconfig in command line tool.
Also, you can ping the machines each other. In case, if you are not able to ping then turn off your firewall.
Then, try to add the domain and it will throw an error message as like below.
To rectify the above issue, you need to add the domain control IP address on DNS Server. Go to Azure Portal and Network Interface.
In DNS Server add the domain controller IP address in custom and restart the machine.
Again log into the machine and now try to add the domain and this time you can add successfully. Share your comments, if any issues.
Date Table in Power BI – M Language
Power BI Desktop supports Power Query Language which is nothing but M language for data modeling.
This article explains how we can create date table with all the necessary columns. Generating date table is easy in Power BI as we have different methods.
Refer my below article to generate date table manually or with single DAX query.
Use the below M query to create a date table,
//Create Date Dimension
(StartDate as date, EndDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),
Date.Day(StartDate)),
EndDate = #date(Date.Year(EndDate), Date.Month(EndDate),
Date.Day(EndDate)),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate – StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, “Year”,
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , “Quarter”,
each “Q” & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , “Week Number”,
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, “Month Number”,
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , “Month”,
each Date.ToText([Date],”MMMM”)),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , “Day of Week”,
each Date.ToText([Date],”dddd”))
in
DayOfWeek
It asks for start and end date. Once entered the date then click Invoke, it will generate the query.
DAX Leap Year In Power BI
Identifying the Leap is very simple logic, this post explains that how you can identify a year which is a leap year or non-leap year in Power BI.
Consider I have a year column on my table. If not then use the Year function on the data column and get the year column.
You need to find the below values,
- Year Start Date
- Year End Date
- Days between Year Start Date and Year End Date.
- Check the number of days
That’s it, very simple.
Use the below DAX expressions to identify the above values.
YearStartDate = DATE(‘Year'[Year],01,01)
YearEndDate = DATE(‘Year'[Year],12,31)
TotalDays = (‘Year'[YearEndDate]-‘Year'[YearStartDate])*1+1
IsLeapyear = IF(‘Year'[TotalDays]=366,”Yes”,”No”)
Happy Learning.
Power BI Pre-defined Transformations
This article talks about the pre-defined transformations that we can directly use in Power BI Desktop. These transformations are part of Power Query Editor.
As a business intelligence developer, usually we spend most of the time to prepare the dataset for report and dashboards. Preparing the dataset includes, getting the data from different sources, data modelling and transform the data.
Data transform is applicable for both rows and columns. Check the list of pre-defined transforms in Power BI Desktop.
These transforms are grouped into different categories.
Table Level
To add a new column
To play with the columns,
To format a text column,
To format a number column,
Date and Time columns,
The above sections are specific to columns. To play with rows,
There any many situations where we need to combine the tables and queries. To handle those scenarios, we can use the below options in Power BI.
To sort the columns,
We also have R script which we can use to perform any complex transform or logics with the data.
Apart from the pre-defined options still we can do complex logics with M language (Power Query Language).
Happy Learning & Share your comments.
Date table in Power BI
Every BI Solutions will have a date dimension table data with other business data. Date dimension is one of the mandatory table as most of the analysis are based on date dimension table.
We can generate the date dimension table in all the scenarios. This post explains you that how we can generate the date dimension table in Power BI.
We have different ways to generate date dimension table. Here I explained two different ways.
Create a table by creating individual column manually
Use New table option and start to create the columns with below expressions.
| Column Name | Expressions |
| Date | CALENDARAUTO() |
| DateAsInteger | FORMAT ( [Date], “YYYYMMDD” ) |
| Year | YEAR ( [Date] ) |
| Quarter | “Q” & FORMAT ( [Date], “Q” ) |
| YearQuarter | FORMAT ( [Date], “YYYY” ) & “-Q”
& FORMAT ( [Date], “q” ) |
| Monthnumber | FORMAT ( [Date], “MM” ) |
| YearMonthnumber | FORMAT ( [Date], “YYYY/MM” ) |
| YearMonthShort | FORMAT ( [Date], “YYYY/mmm” ) |
| MonthNameShort | FORMAT ( [Date], “mmm” ) |
| MonthNameLong | FORMAT ( [Date], “mmmm” ) |
| DayOfWeekNumber | WEEKDAY ( [Date] ) |
| DayOfWeek | FORMAT ( [Date], “dddd” ) |
| DayOfWeekShort | FORMAT ( [Date], “dddd” ) |
Single Script to Generate Date Table
Add the below script in New table expression.
DimDate =
VAR fiscal_year_end_month = 3
RETURN
ADDCOLUMNS (
CALENDARAUTO ( fiscal_year_end_month ),
“DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),
“Year”, YEAR ( [Date] ),
“Quarter”, “Q” & FORMAT ( [Date], “Q” ),
“YearQuarter”, FORMAT ( [Date], “YYYY” ) & “-Q”
& FORMAT ( [Date], “q” ),
“Monthnumber”, FORMAT ( [Date], “MM” ),
“YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),
“YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),
“MonthNameShort”, FORMAT ( [Date], “mmm” ),
“MonthNameLong”, FORMAT ( [Date], “mmmm” ),
“DayOfWeekNumber”, WEEKDAY ( [Date] ),
“DayOfWeek”, FORMAT ( [Date], “dddd” ),
“DayOfWeekShort”, FORMAT ( [Date], “dddd” )
)
The above script will generate a table with all the columns. We can customise the table by modifying the expression.
Power BI Custom Visuals in Office Store
As we know Microsoft Power BI is one of the best business Intelligence Tool. It is offering a variety of services to the customers. Those services are Power BI as a service – Cloud, Power BI Desktop, Power BI Mobile App, Custom Visuals and etc.
There are plenty of custom visuals are available for us to directly in our reports and dashboards. Those custom visuals are available in below URL.
https://app.powerbi.com/visuals/
The visual library is already moved to office store. Any more new visuals and updates are happening in office store. The above URL will be available until May 31, 2017.
If you have used any of the custom visuals from current visuals gallery, still it will work. To get new updates you need to check in office store.
https://store.office.com/
Visit the above URL and select Power BI under product section.
You can see the custom visuals for Power BI, visuals are available under different categories.
To download and use, follow the below steps.
1. Select the visual
2. Click “Add”
3. You can see an option “Select to download <visual name>”, click.
The visual with .pbiviz extension will be download. You can use the visual in a report as usual with import option.
Sankey Chart Visual in Power BI VS R
Check out my previous post to see the streamgraph chart comparison.
In this post, I have taken Sankey chart custom visual. The power bi custom visual has less functionality compare than R visuals
Power BI Desktop
We have a Sankey chart custom visual available in Power BI. Download and import into Power BI Desktop.
https://store.office.com/powerbiaddininstallpage.aspx?rs=en-US&assetid=WA104380777
Here I used a dataset called Titanic. This is one of the datasets in R which I used for this post.
R Studio
To get the sankey visual, we need to install the alluvial package.
Run the below script in R Studio or R console application.
install.packages(‘alluvial’)
tit <- as.data.frame(Titanic)
library(alluvial)
alluvial( tit[,1:4], freq=tit$Freq, border=NA,
hide = tit$Freq < quantile(tit$Freq, .50),
col=ifelse( tit$Survived == “No”, “red”, “yellow”) )
| Power BI Desktop | R Studio |
| Import Sankey Chart visual | Install alluvial and related packages |
| Less Functionality | More Functionality |
| GUI Type | R Script Type |
| Only 2 levels of visual by default for the simple dataset. To see multi-level visuals then need to prepare a dataset with multi-level of source and destinations | Multi-Levels of visual – Just add the number of fields. |
StreamGraph Visual in Power BI VS R
This post just shows the comparison of streamgraph in Power BI Custom Visual and R Script Visual.
Power BI Desktop
We have a streamgraph custom visual available in Power BI. Download and import into Power BI Desktop.
Here I used a very simple dataset to explore the visual.
Pass the above dataset values to chart.
R Studio
To get the streamgraph visuals, we need to install the streamgraph package and other supporting packages
.
Run the below script in R Studio or R console application.
install.packages(‘devtools’)
install.packages(‘Rcpp’)
install.packages(‘zoo’)
install.packages(‘DBI’)
install.packages(‘assertthat’)
install.packages(‘tibble’)
install.packages(‘yaml’)
devtools::install_github(“hrbrmstr/streamgraph”)
library(streamgraph)
# Create data:
year=rep(seq(2000,2005) , each=1)
name=c(letters[1:6])
value=c(1000,200,600,500,1250, 300)
data=data.frame(year, name, value)
streamgraph(data, key=”name”, value=”value”, date=”year”)
| Power BI Desktop | R Studio |
| Import Streamgraph visual | Install Streamgraph and related packages |
| Visual values are keep
amending on the initial value |
Identifies the maximum value and accommodate all the values within the maximum range |
| It is not interactive | It is interactive |
| GUI Type | R Script Type |
| Can’t export as HTML | Can export as HTML |