Table with text measures

[hlynurgudna]

Hi all

I want to change the look of my table so each session is in one line, and one session is where both "Date" and "B" match.

Up to three A can be in one session

Here below you can see my desired output

Year	Month	Day	A1	A2	A3	B
2022	February	1	ALSV	GZTO		23
2022	February	1	JNP			23,I
2022	February	2	ALSV	GZTO	JUCA	23
2022	February	2	AM-SLST	JOVE		F4
2022	February	2	JNP			23,I

The date, A and B all come from different data tables and my first guess was that I would need to create a new data table and somehow merge this all together there but I am not sure how to do it

Hopefully I made myself clear enough

Thanks in advance

[serpiva64 · Accepted solution]

Hi,

to obtain this

you have to group by

in the formula bar you obtain this row

= Table.Group(#"Changed Type", {"Merged", "B"}, {{"Count", each _, type table [Merged=nullable date, A=nullable text, B=nullable text]}})

You modify it this way

= Table.Group(#"Changed Type", {"Merged", "B"}, {{"Count", each [A], type list }})

at this point by clicking on Count

Expand to new rows

then split your Count column by delimiter

change the name of the columns 

and it is done

If this post is useful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[v-henryk-mstf]

Hi @hlynurgudna ,

Whether the advice given by @serpiva64  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[serpiva64 · Accepted solution]

Hi,

to obtain this

you have to group by

in the formula bar you obtain this row

= Table.Group(#"Changed Type", {"Merged", "B"}, {{"Count", each _, type table [Merged=nullable date, A=nullable text, B=nullable text]}})

You modify it this way

= Table.Group(#"Changed Type", {"Merged", "B"}, {{"Count", each [A], type list }})

at this point by clicking on Count

Expand to new rows

then split your Count column by delimiter

change the name of the columns 

and it is done

If this post is useful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[MFelix]

Hi @hlynurgudna ,

Instead of using a table visualization have you tried using a matrix? Only question is regarding the A column you want it to show on different columns?

[hlynurgudna]

Yes my first try was to use matrix but the matrix becomes to big, more than 100 columns for different values of "A" when I for example choose a whole month, not just two days like in the example here above

[MFelix]

Hi @hlynurgudna ,

Believe that you issue is regarding the A column where you have all those different values. What is the exact purpose of the presentation is to summarize the values that match into a single row?

[hlynurgudna]

Exactly, to show the values that match into a single row. 
I know that the problem here is that I have all these different values in column A, so I was thinking if it would be possible to create a measure that combines/groups all the letter codes when B and Date matches and use that as a value

[MFelix · Accepted solution]

Hi @hlynurgudna ,

You can do this using the CONCATANEX formula of dax that allows to make a concatenation of values in a column.