0

I'm having difficulties with using Power Query M-function.

The problems I'm having with is to make cells blank previously contains 'US Core shipping' on column 'promotion-ids'.

For example, there are 'US Core shipping A3JU1250912' 'US Core shipping A3JT982918'...

These words are all different but they have 'US Core shipping' in common.

So I want to make these words containing US Core shipping~ to blank.

Please help me solving this problem.

Attached below are advanced editor and current m-code on my columns.

enter image description here

enter image description here

horseyride
  • 17,007
  • 2
  • 11
  • 22
Aiden Noh
  • 1
  • 1

1 Answers1

1

It is a bit hard to tell exactly what you want without a sample input and desired output

To erase content in specified column if it finds matching text, maybe try

#"Replace" = Table.TransformColumns(#"YourPriorStepNameHere",{{"YourOriginalColumnNameHere", each if Text.Contains(_,"US Core shipping") then null else _, type text}})
horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Thank you for replying! What I was looking for is a transformation that if a word 'US Core Shipping' is included then it is converted into blank, the rest are remaining as before. – Aiden Noh Jul 07 '22 at 17:50
  • see revised code above – horseyride Jul 07 '22 at 18:04
  • I tried what you told me to do... But it makes some errors. – Aiden Noh Jul 07 '22 at 18:36
  • #"Replace" = Table.TransformColumns(#"Flitered Rows1",{{"promotion-ids", each if Text.Contains(_,"US Core shipping") then null else _, type text}}) – Aiden Noh Jul 07 '22 at 18:37
  • The editor says that "Replace" has a error, doesn't this makes sense? – Aiden Noh Jul 07 '22 at 18:38
  • No it does not make sense. Your code is fine. You must have missed an ending comma on the prior row, or perhaps spelled something wrong in your column names or prior name row (For example **Flitered Rows1** might be **Filtered Rows1** ) I assume you end with **in #"Replace"** – horseyride Jul 07 '22 at 18:54
  • Now it works but the one I intended to replace did not... Wonder why this happened – Aiden Noh Jul 07 '22 at 19:10