topic Re: Split String with other field data as delimiter in DAX Commands and Tips

Split String with other field data as delimiter

oysteina — Thu, 17 Mar 2022 10:29:02 GMT

Hi,

I'm new to Power BI, and has been tasked to move our reports from Qlikview. I've come across an issue I'm not successful in Googeling the answer to. In our database, the information of Vehicle Maintenance is stored like this:

Vehicle	Maintenance
A-1	A-11
A-1	A-12
A-1	A-13
B-34	B-341
B-34	B-342
2345864	23458641
2345864	23458642
2345864	23458643
34-321	34-3211
34-321	34-3212

The Maintenance field contains Vehicle ID&Maintenance.

What I would like is to get this out in the report:

Vehicle	Maintenance
A-1	1
A-1	2
A-1	3
B-34	1
B-34	2
2345864	1
2345864	2
2345864	3
34-321	1
34-321	2

In Qlikview, I use the Vehicle field as delimiter, and retrieve the second part of the string.

How is this possible i Power BI?

Br,

Øystein Andresen

Re: Split String with other field data as delimiter

Anonymous — Thu, 17 Mar 2022 10:46:14 GMT

oysteina

Do you want it as a measure or calculated column?

Re: Split String with other field data as delimiter

johnt75 — Thu, 17 Mar 2022 10:56:55 GMT

You can do this in Power Query. Insert a step like

= Table.AddColumn( #"Changed Type", "Maintenance ID", each Text.Replace( [Maintenance], [Vehicle],"") )

and then you can delete the original Maintenance column if you don't need it for anything else

Re: Split String with other field data as delimiter

oysteina — Thu, 17 Mar 2022 11:18:00 GMT

Hi,

thanks for the reply. I have to admit I'm not sure. I would guess a calculated column.

I need it to work also when the maintenance number exceeds 10.

Br,

Øystein

Re: Split String with other field data as delimiter

oysteina — Thu, 17 Mar 2022 11:18:40 GMT

Hi,

Thanks for the reply!

I will try this solution now. 🙂

Br,

Øystein