Basic Data Engineering | M Code Noob | How to extract only what you want into a new column?

[Anonymous]

Hi folks,

I've been using PQ easy UI for a while now, but am realizing that I will eventually have to start learning M CODE to fulfill certain requirements.

I have a flat CSV file with client names, consultant names, and the Role of the consultant. There are duplicate Clients and Consultants in my table to signify who belongs to who.

My REQUEST:

1) I have 1 column that is called 'Consultant Role' -  this column shows what the consultant does for the respective client on that row. IF the cell in the column contains 'mROC Lead', return everything in that cell into this new column, ELSE, leave everything blank.

2) Bonus points if you can tell me how to return everything after "mROC Lead" while leaving "mROC lead" out of it. Either would work. I would just replace with an extra step if this isn't possible with 1 swift M CODE step.

- I am doing this so I can create a card visual to show who the leads are for each Client when selecting a slicer in Power BI. I know this is such a simple request, but I am having trouble getting the hang of M Code with no prior coding experience.

Any help would be appreciated.

Thank you!

[Anonymous · Accepted solution]

Type this into the formula bar:

= Table.AddColumn(#"Name of Table or Previous Step Name", "New Column", each if Text.Contains([Consultant Role], "mROC Lead") then Text.Replace([Consultant Role], "mROC Lead", "") else "")

--Nate

[Anonymous]

Thank you !!!!