Forum Discussion

meierli's avatar
meierli
Helper I
2 years ago
Solved

Combine 4 M-Codes into 1

i have 4 different m-codes all achiving the same result just with slight;y different "lookup" values (see bottom of message) and would like to combine them all into one m-code if possible.   m-code...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "jY/NaoNQEEZfZdCtifNzr/eOu0RNI20TUUMI4iJQS6E0hTYU+vbVdidZdBbf6hw403VBtr0PAcUtkFTESWI9tKeqSEGdB6SYNGYT9FEXVKsTPJS7okkpGjUI2SDSmnA857xxiLe5CTDJCJuo3LVFXTQtrB7bFGgpMwVSjszCQnUH6wzqwy606hzYKWZKFWZV8WrN3KPJOWz/JFUiGIfhuP/1qDmSYfTiDew3m6Zob6d6FrE69pKSkP4Lmj0ly7k3xeV5XA9fw8fn8ATl5fkdqvP323C5QvbyGorhRIXVQn5NAU1MFDOyBH3/Aw==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Transaction_Comment = _t]
  ),
  #"Added Custom4" = Table.AddColumn(
    Source,
    "Custom.4",
    each Text.Trim(
      Text.AfterDelimiter(Text.AfterDelimiter(Text.Upper([Transaction_Comment]), "CHK"), "#")
    )
  ),
  #"Split Column by Character Transition" = Table.SplitColumn(
    #"Added Custom4",
    "Custom.4",
    Splitter.SplitTextByCharacterTransition(
      {"0" .. "9", "A" .. "Z", "-"},
      (c) => not List.Contains({"0" .. "9", "A" .. "Z", "-"}, c)
    ),
    {"CHK#"}
  )
in
  #"Split Column by Character Transition"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

meierli's avatar
meierli
Helper I
2 years ago

please find enclosed a small smaple.

 

CHK# 037-0193373658 TYPE: 978 01/19/24
PAY LINES:1,CHK #24001B100007784700
PAY LINES:1,CHK #0000464004,INTEREST AMT: 1.30
PAY LINES :2,4-5 PG BC RUN#5977 5978 CHK#3229938954
PAY LINES :1 PG UHC RUN#9911 9912 WO CHK#1SW14208384 OFFSET
PAY LINES:1,CHK #823359000191319
PAY LINES:1,CHK #823359000191319,INTEREST AMT: 3.19
PAY LINES :1 DD/Reversed Info Payment Chk#342693295 Dt: 04/11/2023

 

 

lbendlin's avatar
lbendlin
Super User
2 years ago
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "jY/NaoNQEEZfZdCtifNzr/eOu0RNI20TUUMI4iJQS6E0hTYU+vbVdidZdBbf6hw403VBtr0PAcUtkFTESWI9tKeqSEGdB6SYNGYT9FEXVKsTPJS7okkpGjUI2SDSmnA857xxiLe5CTDJCJuo3LVFXTQtrB7bFGgpMwVSjszCQnUH6wzqwy606hzYKWZKFWZV8WrN3KPJOWz/JFUiGIfhuP/1qDmSYfTiDew3m6Zob6d6FrE69pKSkP4Lmj0ly7k3xeV5XA9fw8fn8ATl5fkdqvP323C5QvbyGorhRIXVQn5NAU1MFDOyBH3/Aw==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Transaction_Comment = _t]
  ),
  #"Added Custom4" = Table.AddColumn(
    Source,
    "Custom.4",
    each Text.Trim(
      Text.AfterDelimiter(Text.AfterDelimiter(Text.Upper([Transaction_Comment]), "CHK"), "#")
    )
  ),
  #"Split Column by Character Transition" = Table.SplitColumn(
    #"Added Custom4",
    "Custom.4",
    Splitter.SplitTextByCharacterTransition(
      {"0" .. "9", "A" .. "Z", "-"},
      (c) => not List.Contains({"0" .. "9", "A" .. "Z", "-"}, c)
    ),
    {"CHK#"}
  )
in
  #"Split Column by Character Transition"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.