topic Re: DAX Formula Replace letters of the first word in DAX Commands and Tips

DAX Formula Replace letters of the first word

OH_Sport — Fri, 29 Nov 2024 15:37:07 GMT

Hi Guys,

i am beginnfer of DAX and PowerBI and hope that you can help me
i have to adjust the name in a data sheet

Current Name: Max Hartwig
Target: M. Hartwig.
Remain first letter, replace all letters of the second word with a "." and remain second word.

I do not get the solution in the DAX in Power BI.

1. create custom column

2. New Columnd name - Player ID

3. Custom column formula

How do i write the dax formula to express the data in the right manner?

Re: DAX Formula Replace letters of the first word

FreemanZ — Fri, 29 Nov 2024 18:31:28 GMT

hi OH_Sport ,

in Power Query, try like:

1. split the name column by delimiter, space in your case

2. extract the first letter of the name.1

3. merge name1 and name2 with space

for more info:

https://learn.microsoft.com/en-us/power-query/split-columns-delimiter

https://community.fabric.microsoft.com/t5/Desktop/I-need-to-Extract-first-3-character/td-p/2659241

Re: DAX Formula Replace letters of the first word

OH_Sport — Fri, 29 Nov 2024 18:43:44 GMT

Hi FreemanZ

Thank you for your answer and the possible solution.
I am looking for a DAX Code, so that only on step is necessary - and i can learn to handle the DAX better in the future...

Re: DAX Formula Replace letters of the first word

FreemanZ — Fri, 29 Nov 2024 19:30:36 GMT

hi OH_Sport ,

then try like:

column =

LEFT([Name], 1)&". "&PATHITEM(SUBSTITUTE([Name], " ", "|"), 2)

Re: DAX Formula Replace letters of the first word

OH_Sport — Fri, 29 Nov 2024 19:51:21 GMT

It do not work - what i am doing wrong?

Re: DAX Formula Replace letters of the first word

FreemanZ — Sun, 01 Dec 2024 15:29:39 GMT

hi OH_Sport ,

i think you might be confused between caculated column with DAX and custom column in Power Query with M.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-column

https://learn.microsoft.com/en-us/power-query/add-custom-column

Re: DAX Formula Replace letters of the first word

Anonymous — Mon, 02 Dec 2024 01:40:12 GMT

Hi OH_Sport

Thank you FreemanZ for your interest in this thread and the great replies.
The solution FreemanZ provided in his reply was to create new columns via DAX.
If you want to implement this in Power Query with M, you can use the following code:

= Table.AddColumn(#"Changed Type", "NewName", each Text.Start(Text.BeforeDelimiter([Name], " "), 1) & ". " & Text.AfterDelimiter([Name], " "))

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.