MQ.Queue

Updated on

MQ.Queue is a Power Query M function that returns a table of messages available in the queue via a non-destructive read. It defines the IBM WebSphere MQ Server information required for reading and writing messages.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

MQ.Queue(
   server as text,
   queuemanager as text,
   channel as text,
   queue as text,
   optional options as nullable record,
) as table

Description

Returns a table that defines the IBM WebSphere MQ Server information required for reading and writing messages. It requires the following parameters:

  • Server name or address with an optional port number, separated by a colon, server (port 1414 will be used by default).
  • The name of the queue manager, queuemanager on the MQ server.
  • The name of the channel, channel for the queue manager on the MQ server.
  • The name of the queue, queue to be accessed.
When this function is enumerated it returns a table containing messages in the queue via a non-destructive read. An optional record parameter, options, may be specified to control the following options:
  • BinaryDisplayEncoding : The binary value will be returned as text with the specified encoding. Affects the following columns in the output: MessageId, CorrelationId, AccountingToken, GroupId, and MsgToken. If it’s not specified, the value will be returned as a binary value. The following values may be used:
    • BinaryEncoding.Base64
    • BinaryEncoding.Hex
    • TextEncoding.Utf16
    • TextEncoding.Unicode
    • TextEncoding.BigEndianUnicode
    • TextEncoding.Windows
    • TextEncoding.Ascii
    • TextEncoding.Utf8
  • MessageDataDisplayEncoding : Message Data is returned as a UTF8 representation of the underlying binary value by default. Supports the same values as the BinaryDisplayEncoding option. Also supports type binary to retrieve the message data as a binary value.
  • Timeout : If a message is not in the queue, wait this amount of time for a message to appear. The default value is zero. Specified as a duration.
The record parameter is specified as [option1 = value1, option2 = value2…] or [BinaryDisplayEncoding = BinaryEncoding.Hex] for example. Table.SelectRows may be used to filter the messages to be retrieved from the queue. The following columns will be folded into the query:
  • MessageId : The message identifier of the MQ message to be retrieved. This can be a text or binary value.
  • CorrelationId : The correlation identifier of the MQ message to be retrieved. This can be a text or binary value.
  • MessageToken : The message token of the MQ message to be retrieved. This can be a text or binary value.
  • GroupId : The group identifier of the MQ message to be retrieved. This can be a text or binary value.
  • Offset : The offset of the MQ message to be retrieved. This is an integer value. (Not available on MQ z/OS).
  • LogicalSequenceNumber : The logical sequence number of the MQ message to be retrieved. This is an integer value.
Note that when these columns are combined with other non-foldable columns the query might not be folded. Table.FirstN may be used to limit the number of messages retrieved from the queue. When not specified, only the first 500 messages will be retrieved. Table.SelectColumns may be used to restrict the columns returned by the function. TableAction.DeleteRows may be used to destructively get messages from the queue. TableAction.InsertRows may be used to send messages to the queue. The following message properties are writable:
  • MessageData : Message data. This is the only required column. Can be a text or binary value.
  • CorrelationId : The correlation identifier of the MQ message to be sent. This can be a text or binary value.
  • Ccsid : The character set for the message. By default, the character set on the MQ Server will be used.
  • MessageId : The message identifier of the MQ message to be sent. This can be a text or binary value.
  • MessageType : Valid message types are “Datagram”, “Reply”, or “Request”. The default value is “Datagram”.
  • Offset : Message offset. The default is 0.
  • ReplyToQueue : If the message type is “Request”, then the Reply to Queue can be specified. The default is to use the queue as defined in MQ.Queue.
  • ReplyToQueueManager : Name of the Queue Manager for the ReplyToQueue. The default is to use the queue manager as defined in MQ.Queue.

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot