Various Lookup Table Designs 07-07-2024 21:59 PM

[aduguid]

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"