Replace character when not in a word

[Anonymous]

Hello all,

This issue has had me going for a few hours now so am turning to the smarter brains of this forum 🙂

I have a column named "discipline" that can contain up to 9 characters along a drawing reference, in the following format:

Drawing	Discipline
WG1234	A
WG 125	A, C
WG 9090	A, C, E, J, K, M, P, S, T
WG 087	E

The letterts A, C etc cannot be changed in the raw database so I have to import them as is but I'd like to convert A to Architectural, C to Civil, E to Electrical, etc... for the purpose of filtering (I'd actually like to keep the Discipline column as it is)

The intent being that I then have a slicer with the listing of Architectural, Civil, Electrical and if I select Architectural, it will show in my table all drawings that have A in the Discipline( in the example above that is WG1234, WG 125 and WG 9090)

I have obviously tried to replace values sequentially in power query mode but I end-up with gibberish (A becomes ARCHITECTURAL, but then if E becomes ELECTRICAL I end-up with ARCHITELECTRICALCTURAL...). I have thought of unpivoting the data but I already have a couple million rows and it wouldn't be sustainable.

Any clever solution you can suggest?

Thanks in advance.

OF

[AlB]

@Anonymous

Quick solution. Probably not the most elegant. Unfortunately SUMMARIZECOLUMNS( ) seems not to be fully supported in a modified filter context yet so that's a drag. We thus cannot use the earlier approach that invokes [Discipline (Measure)], which would be waaay smoother. With some more work we might find a solution for that and get something more elegant but this should do:

MeasureNumDisplayedDrawings2 = 
COUNTROWS (
    FILTER (
        'All Documents';
        VAR CurrentDiscipline = 'All Documents'[Discipline]
        VAR CountOfDisciplines =
            SUMX (
                FilterTableDiscipline;
                MIN (
                    FIND ( FilterTableDiscipline[Discipline]; CurrentDiscipline; 1; BLANK () );
                    1
                )
            )
        RETURN
            IF ( CountOfDisciplines > 0; 1 )
            = 1
    )
)

Code formatted with  

[AlB · Accepted solution]

@Anonymous

Or in fact a leaner version:

MeasureNumDisplayedDrawings2_V2 =
COUNTROWS (
    FILTER (
        'All Documents';
        SUMX (
            FilterTableDiscipline;
            FIND ( FilterTableDiscipline[Discipline]; 'All Documents'[Discipline]; 1; 0 )
        )
            > 0
    )
)

Code formatted with  

[Anonymous]

@AlB, I have no idea what you've done, but it works perfectly.

Thank you very very much, I really appreciate your help.

[AlB]

@Anonymous

You're very welcome. Cool to hear it finally works.

The logic behind this last measure is very similar to that of the previous one. We merrily step through the 'All documents' table and, for each row, we check whether any of the disciplines selected in the slicer can be found within the Disciplines for the drawing. If so, we've found one drawing of interest and we include that row in the total count.