Forum Discussion
Table with text measures
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
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 !
Hi hlynurgudna ,
You can do this using the CONCATANEX formula of dax that allows to make a concatenation of values in a column.
7 Replies
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?
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
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?
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 !
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
