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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
taher
Helper II
Helper II

concatenate Values repeatly

Hi All,

 

as you can see in the photo, I have a table of two columns.

StoreId: every value is repeated three times because every value has three TagId.

I'm trying to create a new column which concatenates every three rows in on row for each single StoreId.

 

I am looking for  "for each" or "while" statements in Dax but till now no luck.

 

Thanks for help I would appreciate any Idea!merge rows.PNG

 

Regards,

Taher

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft Employee

@taher

 

Hi Taher,

 

If you want it in DAX, you could try these two options.

Option 1: there will be duplicate rows.

 

New =
CALCULATE (
    CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" ),
    FILTER ( 'Table1', 'Table1'[StoreId] = EARLIER ( Table1[StoreId] ) )
)

concatenate Values repeatly 0.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Option 2: create a new table.

 

Table =
SUMMARIZE (
    'Table1',
    'Table1'[StoreId],
    "NewColumn", CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" )
)

concatenate Values repeatly 1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

View solution in original post

19 REPLIES 19
v-jiascu-msft
Microsoft Employee
Microsoft Employee

@taher,

 

Hi Taher,

 

Did you solve your problem? Could you please mark the proper answer if it's convenient for you? That will be a help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Microsoft Employee
Microsoft Employee

@taher

 

Hi Taher,

 

If you want it in DAX, you could try these two options.

Option 1: there will be duplicate rows.

 

New =
CALCULATE (
    CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" ),
    FILTER ( 'Table1', 'Table1'[StoreId] = EARLIER ( Table1[StoreId] ) )
)

concatenate Values repeatly 0.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Option 2: create a new table.

 

Table =
SUMMARIZE (
    'Table1',
    'Table1'[StoreId],
    "NewColumn", CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" )
)

concatenate Values repeatly 1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

Hi @v-jiascu-msft,

 

sorry, I was on holiday, so I have not tried it until today.

It did work with Summerize, thanks.

I would be interested if it could work without adding a new table, I mean is there an alternative option, which enables me doing this concatenating within my base table.

 

Thank u all 🙂

Taher

@taher

 

Hi Taher,

 

Did you try option 1? You don't need to create a new table with this option.

 

Best Regards!

Dale

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

Hi @v-jiascu-msft,

 

it is working thank you. But I am now in a new trouble.

I need to split the calculated column.

I can't see the new column or even the new table in the query editor. With M script I got a syntax error.

 

Best,

Taher

 

Hi,

 

It seems I've wrongly imagined that I need to concatenate the column. 

I need to use the  TagId as a filter in the report, the TagId itself doesn't be allowed to filter each other, because of that I thought the best way is to split them in one row so every StoreId can get its 3 TagId without any problem and then can I filter by TagId.

 

Any idea from you would be great 🙂

 

@taher,

 

Hi Taher,

 

Why can't TagId filter each other? Could you please post a sample? It's a good idea to create a new thread if it's different with this topic. That would be great for others to search solutions in the community.

 

Best Regards!

Dale

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

Hello @v-jiascu-msft,

 

I'm sorry for not replying quickly. I'm writing exams for the university.

This is the full description of the data the report which I make.

From the table 'Tags ' I should get the TagID that tell me which StoreID ordered to which TagID.

Then the StoreID is connected to its info Table 'Stores ' which is to the main Table 'Answers' connected.

Now I am getting answers to questions from the main table.

What has TagID to do in my report? every TagID has a 'name' that is the person who sends me the answers and a ParentTagID which order the type of person (boss, normal employee, student employee) that'sDatabase.PNG why for every store there are three Tags.

Now in the report, the user must filter the report due to the three Tags, because the Boss has many employees and the employee has many student employees.

so I choose the Boss then I get the employees according to this Boss and so on! 

Hi @taher,

 

Maybe we could add a new column "boss" to the table "tags". Then the "tags" would be like the upper of the picture. Finally, it's easy to add slicers. Could you please post a little sample of table "tags"? 

 concatenate Values repeatly2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

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

Hello @v-jiascu-msft,

 

hier you find a full sample project.

ParentTagID = 150  =>Boss

ParentTagID = 140  =>Worker

ParentTagID = 130  =>Student.

 

https://www.4shared.com/s/fWHB5XsRDei

 

as you can see when I filter due to StoreName, the three  Boss, worker and the student are appearing in the three slicers.

It should just on of them for each slicer according to its PaentTagID.

 

Thanks a lot 🙂

 

 

Hi @taher,

 

I can't download the file from your URL. The download button didn't work. Could you please use GoogleDrive or OneDrive?

 

Best Regards

Dale

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

Hello @v-jiascu-msft ,

 

 

Tags_Table

hier is it on one drive.

 

Best,

Taher

@taher

 

Hi Taher,

 

First, I would recommend a custom slicer.

Add a calculated column first in the table "tags".

 

PName =
IF (
    [ParentTagID] = 130,
    "Student",
    IF ( [ParentTagID] = 140, "Worker", IF ( [ParentTagID] = 150, "Boss", "Null" ) )
)

concatenate  Values repeatly.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Second, due to the measure can be put in a slicer, we have to filter these data to a new table.

 

130 =
FILTER ( 'Tags', 'Tags'[ParentTagID] = 130 )
140 =
FILTER ( 'Tags', 'Tags'[ParentTagID] = 140 )
150 =
FILTER ( 'Tags', 'Tags'[ParentTagID] = 150 )

concatenate  Values repeatly2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 concatenate  Values repeatly3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

hello @v-jiascu-msft,

 

we still have the same Problem.

when I for Example filter according to the worker 'Mark', it filters all the Bosses and the Students! But 'Mark' must filter just the ones who ist not in a pair with him in the original table ( Mark has John as a Boss and Andre as a student) these but disappeared too.

It is really difficult, I'v just tried and tried but it would not work.

 

Best,

TaherTags.PNG

 

Filter-Tags.PNG

 

 

 

ImkeF
Super User
Super User

If you want to concatenate them in 1 column, I'd suggest the following approach:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLCM4yBrOMgCwTOMsUzjJTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StoreID = _t, TagID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreID", Int64.Type}, {"TagID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"StoreID"}, {{"Concatenate", each Text.Combine(_[TagID], "-"), type table}})
in
    #"Grouped Rows"

You group on StoreID, select the TagID column as list and combine all those items (Text.Combine).

 

File to play around: https://1drv.ms/u/s!Av_aAl3fXRbehasUN2d7tmLElWqypQ

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF;

 

this code seems strange to me.

Can you please suggest me a tutorial for beginners to learn this type of coding.

 

Best,

Taher

Showing how to incorporate the code from above into your solution: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co...

 

Some learning resources for M: http://www.thebiccountant.com/learning-resources/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

vanessafvg
Super User
Super User

@taher are you saying you want to concatenate all three tagid into one row and one column?  i.e (1,2,3)

 

think you need to pivot for that in power query





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.