Sunday, December 22, 2019

How to create a Static or In Memory Table in Power BI using DATATABLE Function

Creating a Static or Custom Table using DAX  DATATABLE Function in Power BI
In Power BI, we can create a Static or In Memory Table using the below DAX Expression with the help of DATATABLE Function.
1) Using DAX:
TempTable = 
DATATABLE (
//Defining Columns for a Table
    "Col_Bln", BOOLEAN, 
    "Col_Cur", CURRENCY, 
    "Col_DT", DATETIME,
    "Col_Dbl", DOUBLE, 
    "Col_Int", INTEGER, 
    "Col_Str", STRING, 
//Inserting Values to the Columns
    { 
        { TRUE,100,"01/02/2019", 123.45, 10,"Value1" }, 
        { FALSE,200,"01/02/2019", 56789, 20,"Value2" }
    } 
)

Result :

Notes :

-------------------------------------------------
BOOLEAN (True/False)
CURRENCY (Fixed Decimal Number)
DATETIME (Date/Time)
DOUBLE (Decimal Number)
INTEGER (Whole Number)
STRING (Text)


We can also create a Static table using the UNION Function as shown in below DAX :
TempTable_UnionRows = 
UNION (
//  ROW(  Columm1 , "Value1", Columm2 , Value2....)
    ROW("Price Range", "Low", "Min Price", CURRENCY(0), "Max Price", CURRENCY(10)),
    ROW("Price Range", "Medium", "Min Price", 10, "Max Price", 100 ),
    ROW( "Price Range", "High", "Min Price", 100, "Max Price", 9999999 )
)

Result :

We can also create a Static table using the Table Constructor (which is not a DAX Function but it is a set of characters by which using we can create a table in DAX. Table instructor is always surrounded by {} characters.) as shown below :

Static_TableConst =
 {
(DATE(2019,12,25),"First Row",100),
(2019/12/25,"Second Row",CURRENCY(200))
}

Result:
          


2) Using M-Query:
We can create a dummy table using M-Query as shown below.
[Measures] =

let 
  Source = Table.FromRecords({ [Default = null] })
in
  Source

Result:


Notes:
-------------------------------------------------
Date value needs to Pass using the DATE Function, otherwise, it returns the Default value.
In the Table constructor method, the Column names are always Value1, Value2, Value3,…. and we cannot change in expression, however, we can change them afterwards by renaming after creation, or even using SelectColumns function. Data types of columns are defined automatically, which we can change them manually. Because of these two limitations, I’d rather use the Datatable function in DAX which gives us more options and flexibility.

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.