Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gomezc73
Helper IV
Helper IV

How 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:

 

LeadAccountSep 23Sep22VARIATIONExplanation
CASHC01200010001000Variation due monetary reconversion FROM USD to EUR
CASHC022350025000-1500 Reduction on the cash deposits pertaining to bookings due to the change on the credit card processor
TRADET01500010004000credit card settlements are now being collected in the corporate bank
TRADET02340010002400Variations due a revaluation in Suisse bank
OTHERI01200010001000Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company
OTHERI021000010009000Increase mainly driven by timing on the collection
OTHERI03200010001000Payments 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:

 

Trade1 credit card settlements are now being collected in the corporate bank.
2 Variations due a revaluation in Suisse bank
OTHER1 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
1 ACCEPTED SOLUTION

@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.

Greg_Deckler_0-1697572029290.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

@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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

@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.

Greg_Deckler_0-1697572029290.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi Greg, i have a lithe issue maybe you can help me with a variation of the measure..

 

The final user told me that he only explain variations greater of 6%, then i have some accounts with the explanation field in blanks..

 

I tried to filter only explanations not blanks, but then the sum of amounts also filter it and it is incorrect..

 

There is a way of modify this measure to only concatenate explanations Non blanks without filter the amounts?.

 

By example( see the below dada) the account I02 haven't explanation. then i need concatenate only the explanation of the accounts I01, I03, I04, but the Sum of the variations must be 10.980(considerand the amount with no explanation).

 

When i filter only explanations Non Blanks the amount of variations is 11.000.

 

Can you please help me?

LeadAccount23-Sep22-SepVARIATIONExplanation
OTHERI01200010001000Funds in this account are used to pay the payroll and any other payroll related items pertaining to the employees under this company
OTHERI021098011000-20 
OTHERI031000010009000Increase mainly driven by timing on the collection
OTHERI04200010001000Payments for medical claims and other benefic

 

 

@gomezc73 Sure, you can filter the blanks in the measure:

Explanation Measure = 
    VAR __PathText = CONCATENATEX(FILTER('Table', [Explanation] <> BLANK()), [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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

 I tried but it still it show at the end blanks lines numbered.. See the image

gomezc73_0-1697639045463.png

 

Amazing!!. worked perfect!!.

thank you!!  

CoreyP
Solution Sage
Solution 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?

Hi, 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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors