Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
meierli
Helper I
Helper I

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 #1

let
after_prefix = Text.AfterDelimiter([Transaction_Comment], "CHK#"),
remove_digits = Text.Remove(after_prefix, {"0".."9", "A".."Z"}),
delimiter = Text.Start(remove_digits, 1),
trim_start = Text.AfterDelimiter([Transaction_Comment], "CHK#"),
prod_num = Text.BeforeDelimiter(trim_start, delimiter),
result = if delimiter = ""
then trim_start
else " " & prod_num
in result

 

m-code 2

let
after_prefix = Text.AfterDelimiter([Transaction_Comment], "CHK# "),
remove_digits = Text.Remove(after_prefix, {"0".."9", "A".."Z", "-"}),
delimiter = Text.Start(remove_digits, 1),
trim_start = Text.AfterDelimiter([Transaction_Comment], "CHK# "),
prod_num = Text.BeforeDelimiter(trim_start, delimiter),
result = if delimiter = ""
then trim_start
else " " & prod_num
in result

 

m-code 3

let
after_prefix = Text.AfterDelimiter([Transaction_Comment], "CHK #"),
remove_digits = Text.Remove(after_prefix, {"0".."9", "A".."Z"}),
delimiter = Text.Start(remove_digits, 1),
trim_start = Text.AfterDelimiter([Transaction_Comment], "CHK #"),
prod_num = Text.BeforeDelimiter(trim_start, delimiter),
result = if delimiter = ""
then trim_start
else " " & prod_num
in result

 

m-code 4

let
after_prefix = Text.AfterDelimiter([Transaction_Comment], "Chk#"),
remove_digits = Text.Remove(after_prefix, {"0".."9", "A".."Z"}),
delimiter = Text.Start(remove_digits, 1),
trim_start = Text.AfterDelimiter([Transaction_Comment], "Chk#"),
prod_num = Text.BeforeDelimiter(trim_start, delimiter),
result = if delimiter = ""
then trim_start
else " " & prod_num
in result

1 ACCEPTED SOLUTION

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.

View solution in original post

20 REPLIES 20
meierli
Helper I
Helper I

thank you. how many columns will your code create? i was trying to avoid spliting the text. all i want is just to extract the check number, which can contain number, letters and - , plus be of variable length.

 

also, what is all this stuff?

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]
),

 

My code will create a single column.

 

"that stuff"  is just your sample data in a fancy encoding.

thank you. do i need to include all of that fancy encoded data "stuff" when copying the code?

no, all you need is 

 

 #"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"

assuming you have a Source step that has a [Transaction_Comment]  column.

i copied the code into the advanced editor and once it runs, I get enclosed error. i don't follow on the error since the column "transaction_comment" is in my source file. what am i doing wrong?

 

Code added in adv editor.JPG

Error after adding code.JPG

check for extra spaces after the column name.  You may also have to escape the underscore as [#"Transaction_Comment"]

i dont see any extra spaces after the column name and the name of the column does have an underscore.

#"Added Custom4" = Table.AddColumn(
    Source,
    "Custom.4",
    each Text.Trim(
      Text.AfterDelimiter(Text.AfterDelimiter(Text.Upper([#"Transaction_Comment"]), "CHK"), "#")
    )
  ),

still keep getting error

 

Expression.Error: The field 'Transaction_Comment' of the record wasn't found.
Details:
Content=[Binary]
Name=FFS Payment Detail Comments_LiveData_202401.xlsm
Extension=.xlsm
Date accessed=2/20/2024 3:18:09 PM
Date modified=1/30/2024 11:25:00 AM
Date created=1/30/2024 11:17:00 AM
Attributes=
Content Type=application/vnd.ms-excel.sheet.macroEnabled.12
Kind=Excel File
Size=17658135
ReadOnly=FALSE
Hidden=FALSE
System=FALSE
Directory=FALSE
Archive=TRUE
Device=FALSE
Normal=FALSE
Temporary=FALSE
SparseFile=FALSE
ReparsePoint=FALSE
Compressed=FALSE
Offline=FALSE
NotContentIndexed=FALSE
Encrypted=FALSE
ChangeTime=2/20/2024 2:50:43 PM
SymbolicLink=FALSE
MountPoint=FALSE
Folder Path=C:\Users\lmeier\Desktop\FFS Reports\

Check your spelling again. Power Query is case sensitive.

Thanks for your help, but I checked everything. Guess I am not going to be able to implement the code and will just keep runnning the seperate codes.

can you post a sanitized version of the .xlsm file?

i dont see an option to upload a cleaned up version of the file.

Since I can't upload the file, I just copied first couple rows from the original macro file.

 

 

Reporting_PeriodGrpTransaction_FSC_KeyFsc_NamePaycode_NumberInvoice_NumberTransaction_Posting_PeriodTransaction_DatePS_CustomerPaycode_NameCheck_NumberCheck_DateTransaction_CommentGrp100_Flag Payment_Amount 

202401           TPRIYA TX from Invoice#81238879 to Invoice#82221869  
202401           DDEVARAKONDA Tx from Inv#81238889 to Inv#82222993  
202401           PAY INFO 139406515 01/17/24  
202401           PAY INFO 138530633 01/05/24  
202401           CHK# 037-0193283221 TYPE: 971 01/19/24  
202401           PAY INFO 138530429 01/05/24  
202401           PAY INFO 138386018 01/03/24  
202401           MC 1055 012324 NOEL LAMAS  
202401           PAY INFO 137679596 12/22/23  
202401           PAY INFO 138366337 01/03/24  
202401           TRAGULA tx from  INV# 81249453 of GRP#7  to INV#82273042 of GRP#100.  
202401           MRD/TIMELY FILING TO 6  
202401           TPRIYA TX from Invoice#81249638 to Invoice#82173176  
202401           PAY INFO 138095589 12/29/23  

thanks, yes i do. i will test it out either today or monday.

lbendlin
Super User
Super User

I would first replace "CHK #" with "CHK#", and then you should be able to use your M code #2 for all scenarios.

 

 

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

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

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

 

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors