Forum Discussion
Helper VHow Concatenate Text by Category
Hi Experts,
I have the following issue.
I have in a table the explanations of variations by accounts.
Something like these:
| Lead | Account | Sep 23 | Sep22 | VARIATION | Explanation |
| CASH | C01 | 2000 | 1000 | 1000 | Variation due monetary reconversion FROM USD to EUR |
| CASH | C02 | 23500 | 25000 | -1500 | Reduction on the cash deposits pertaining to bookings due to the change on the credit card processor |
| TRADE | T01 | 5000 | 1000 | 4000 | credit card settlements are now being collected in the corporate bank |
| TRADE | T02 | 3400 | 1000 | 2400 | Variations due a revaluation in Suisse bank |
| OTHER | I01 | 2000 | 1000 | 1000 | Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company |
| OTHER | I02 | 10000 | 1000 | 9000 | Increase mainly driven by timing on the collection |
| OTHER | I03 | 2000 | 1000 | 1000 | Payments for medical claims and other benefic |
Now, In the powerBi report i need a slicer to select a LEAD (CASH/TRADE/OTHER) and show all the explanations concatenated and numbered.
I know that i can use Concatenex with summarize but i can't get the output requested.
Can you help me?
I need Something like this:
| Trade | 1 credit card settlements are now being collected in the corporate bank. 2 Variations due a revaluation in Suisse bank |
| OTHER | 1 Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company 2 Increase mainly driven by timing on the collection 3 Payments for medical claims and other benefic |
gomezc73 I posted the formula in the original reply. All you should have to do is replace 'Table' with your actual table name assuming that your columns names are what you posted.
10 Replies
CoreyPSolution Sage
When you say numbered, what does this number mean? Is it ordered in any particular way? Or represent a rank based on the number of times that explanation occurs?
gomezc73Hi, it is only a number 1, 2, 3, 4 or a,b,c,d.. it is only to identify that it is a separate explanatations.
By example, the first explanation must be '1', the second explanations must have a '2' etc..
thank you
- CoreyP
Oh, gotcha. Greg_Deckler 's solution is great. For future iterations, if the explanations are not free text, but selected from a list of standard available options, counting their frequency and ranking them might provide some useful insights. Just a thought.
- Greg_Deckler
Community Champion
gomezc73 Try this. PBIX attached below signature.
Measure = VAR __PathText = CONCATENATEX('Table', [Explanation], "|") VAR __Table = ADDCOLUMNS( GENERATESERIES(1, COUNTROWS('Table'), 1), "__Text", PATHITEM(__PathText, [Value]) ) VAR __Result = CONCATENATEX(__Table, [Value] & " " & [__Text] & UNICHAR(10) & UNICHAR(13)) RETURN __Result- gomezc73
Hi, i can't open the PBI because i have a prior version installed, can you please send me an screenshot of the formula?.. i reaaly appreciatte
- Greg_Deckler
gomezc73 I posted the formula in the original reply. All you should have to do is replace 'Table' with your actual table name assuming that your columns names are what you posted.