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

I tried and it didn't work since it wont captutre all the different spelling outlined above in the 4 different scenarios.

lbendlin's avatar
lbendlin
Super User
2 years ago

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

  • 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.