concatenate Values repeatly

[taher]

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!

Regards,

Taher

[v-jiascu-msft]

@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

[v-jiascu-msft · Accepted solution]

@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] ) )
)

Option 2: create a new table.

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

Best Regards!

Dale

[taher]

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

[v-jiascu-msft]

@taher

Hi Taher,

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

Best Regards!

Dale

[taher]

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

[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 🙂

[v-jiascu-msft]

@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

[taher]

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's 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! 

[v-jiascu-msft]

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"? 

 

Best Regards!

Dale

[taher]

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 🙂

[v-jiascu-msft]

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

[taher]

Hello @v-jiascu-msft ,

Tags_Table

hier is it on one drive.

Best,

Taher

[v-jiascu-msft]

@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" ) )
)

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 )

 

Best Regards!

Dale

[taher]

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,

Taher

[taher]

[ImkeF]

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

[taher]

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

[ImkeF]

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/

[vanessafvg]

@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