Split String with other field data as delimiter

[oysteina]

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

[johnt75 · Accepted solution]

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 

[oysteina]

Hi,

Thanks for the reply!

I will try this solution now. 🙂

Br,

Øystein

[Anonymous]

@oysteina 

Do you want it as a measure or calculated column?

[oysteina]

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