aduguid's avatar
aduguid
Memorable Member
2 years ago

Various Lookup Table Designs

Here are some various ways of creating lookup tables in Power BI.

This table is constructed with the UNION and ROW functions in DAX.

 

Boolean Types = 
    UNION (
      ROW ("Label", "Yes", "Order", 1)
    , ROW ("Label", "No", "Order", 2)
    )

 

This table is constructed with using the DATATABLE function in DAX.

 

Boolean Types = 
VAR _result = 
    DATATABLE (
      "Label" , STRING
    , "Order" , INTEGER
    ,
    {
          {"Yes"  , 1}
        , {"No"   , 2}
    }
  )

RETURN
_result

 

This table was created using Power Query in M.

 

let
    Source = Table.FromRecords({
        [Label = "Yes", Order = 1],
        [Label = "No", Order = 2]
    })
in
    Source

 

This table was created using "Enter data" button from the "Home" ribbon.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtJRMlSK1YlW8ssHMo2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, Order = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Order", Int64.Type}})
in
    #"Changed Type"

 

No RepliesBe the first to reply