Combining text of the same column for combinatorial analysis

[Anonymous]

Hi, I need help with combinatorial Analysis. I'm working on a text recognizing app, and I need help to combine the codified atributes (P, L and F). The first table is the one that the text app recognizes and then we codify it to unite (join) the atributes. The first table has the date as first column, the second is a code of the location and the third one are the main atributes (P, L and F).

I've got this table (First Picture) and I need to combine the "Texto_Nomalizado" column and do every posible combination between "P", "L" and "F". The thing is I have to create a frequency table for this combinations. Also it has to be generic because I have more than 500k codes of P, L and F summed up. After the combination, they need to be in the same column like the second table and count them if they both or the three of them exist. The other columns can be deleted if needed. I know it's hard and confusing.

The second table was filled randomly.

For example, we've got P000001, L000001 and P000001, so we've got something like the third table, with 7 total combinations betweem them.

(First Table)

(Second Table)

Combination	Frequency  (Count)
#F000001	1
#F000002	3
#F000003	1
#F000004	1
#P000001	2
#P000002	2
#P000003	3
#L000001	3
#L000002	3
#(P000001L000001F000001)	1
#(P000002L000002F000002)	1
#(P000001L000001F000003)	1
#(P000002L000001F000002)	1
#(P000003L000002F000002)	1
#(P000003L000002F000004)	1
#(P000001L000001)	2
#(P000001F000001)	1
#(L000001F000001)	1
#(P000002L000002)	1
#(P000002F000002)	2
#(L000002F000002)	2
#(P000001F000003)	1
#(L000001F000003)	1
#(L000001F000002)	1
#(P000003L000002)	2
#(P000003F000002)	1
#(P000003F000004)	1
#(L000002F000004)	1

(Third Table)

Combination	Frequency  (Count)
P000001	1
L000001	1
F000001	1
P000001L000001	1
P000001F000001	1
L000001F000001	1
P000001L000001F000001	1

Thanks for the help, I know it's complex, I've been working on this for days and I can't figure out how to do it.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please try to create three table for P L F:

TableP = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Text in code]),FILTER('Table',LEFT('Table'[Text in code])="P")),"P",[Text in code])

TableL = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Text in code]),FILTER('Table',LEFT('Table'[Text in code])="L")),"L",[Text in code])

TableF = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Text in code]),FILTER('Table',LEFT('Table'[Text in code])="F")),"F",[Text in code])

Then create the combination table:

COMBINE = UNION(TableP,TableL,TableF,SELECTCOLUMNS(ADDCOLUMNS(CROSSJOIN(TableP,TableL),"combine",[P]&[L]),"c",[combine]),SELECTCOLUMNS(ADDCOLUMNS(CROSSJOIN(TableP,TableF),"combine",[P]&[F]),"c",[combine]),SELECTCOLUMNS(ADDCOLUMNS(CROSSJOIN(TableL,TableF),"combine",[L]&[F]),"c",[combine]),SELECTCOLUMNS(ADDCOLUMNS(CROSSJOIN(TableP,TableL,TableF),"combine",[P]&[L]&[F]),"c",[combine]),SELECTCOLUMNS(ADDCOLUMNS(CROSSJOIN(TableL,TableF),"combine",[L]&[F]),"c",[combine]))

At last , create frenquent column for the new table:

Frequency(Count) = CALCULATE(COUNT('Table'[Text in code]),FILTER('Table',LEFT('Table'[Text in code]) = LEFT(COMBINE[Combination])&&LEFT('Table'[Text in code],7) = LEFT(COMBINE[Combination],7))) 

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

Best Regards,

Dedmon Dai

[Greg_Deckler]

@Anonymous - So, what are we frequency counting in the 2nd table? Are we doing this by the date (Fecha) or by the Ubicacion column? Seems similar to what I came up with here:

Distinct Column Combinations - Microsoft Power BI Community

[Anonymous]

Hi Greg, thanks for your help! Hope you're ok.

We are counting the amount of times that the codes in the third row (Starting with P, L or F) appear in the first table. Then we count the amount of combinations (order doesn't matter) that we can do with all the P, L or F codes that we generate by combining them into one string line. The date and location are merely informational to relate the table with another ones.

Imagine we've got this situation:

So, our text detector, gets 6 pieces of atribute code (Starting with P, L or F):

So our frequency table in the combinatorial analysis (second table) would be like this:

I think I'm not missing any combination between them, correct me if I'm wrong. In every case that P000001 is involved, the count must be at least 2, because of the number of combinations that you can do with that atribute.

I don't care if the job is done in Power Query or using DAX, but as a measure it won't work because I need that table as a registered table, not as a Matrix. I hope you can help me 🙂