Table scan and joining data in a sigle value

[Cipriano]

Hello, everyone!

To check if it is possible to read a data table to obtain a summary view of the data, I basically need to integrate data using a table scan, joining the values of one of its columns:

Table Orginal
Fecha	Alumno	Materia	Comentario
01/01/2023	CLR	A	Puntualidad
01/01/2023	CLR	A	Tarea
01/01/2023	CLR	A	Retraso Ejercicio
01/01/2023	MRGM	A	Puntualidad
01/01/2023	MRGM	A	Tarea
01/01/2023	MRGM	A	Ejercicio

Result
Fecha	Alumno	Materia	Comentario
01/01/2023	CLR	A	Puntualidad, Tarea, Retraso Ejercicio
01/01/2023	MRGM	A	Puntualidad, Tarea, Ejercicio

Thank you very much in advance!

[Cipriano]

Thank you very much for your support.

I have implemented the code, except that when trying to use the grouping result column, in the Report view, Visualizations panel, Table object, the grouped text column is not displayed, is there a way to make the grouping results permanent to be able to use them in the table visual? Greetings.

[edhans]

It should load. Is there a subsequent step that is removing columns? There is nothing magical about that column I added. It is just a normal column as far as Power Query is concerned and will load file into the Power BI data model.

So your entire code for that query. Hard to diagnose without being able to see something.

[Cipriano]

Sorry for not adding photos, I'll attach them.

[Cipriano]

This is the code

let
Source = Excel.Workbook(File.Contents("D:\01 Doctos\Oficios\00 Informatica\BI\PBI Comunity\RESUELTOS\10 Table Join Data Grop By\Table scan and join data.xlsx"), null, true),
Tabla1_Table = Source{[Item="Tabla1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Tabla1_Table,{{"Fecha", type date}, {"Alumno", type text}, {"Materia", type text}, {"Comentario", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Fecha", "Alumno", "Materia"}, {{"Comentarios", each Text.Combine(_[Comentario], ", "), type table [Fecha=nullable date, Alumno=nullable text, Materia=nullable text, Comentarios=nullable text]
}})
in
#"Grouped Rows"

[edhans]

There is no reason I can see that column shoudn't load. If it wont' mess anything up, right-click on that Query in Power Query and Uncheck the Enable Load, close Power Query, then open it back up, and re-enable the load.

[edhans · Accepted solution]

Sorry. I see the issue now. I had unnecessary table code in there. That column is being treated as an embedded table and it isn't. New code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lZ58gIOkIxAGleSWliTmZKYkpSrE6OJWFJBalJuJTEJRaUpRYnK/gmpValJyZnJmPqdg3yN2XGFuR1OGwFkkFkn2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fecha = _t, Alumno = _t, Materia = _t, Comentario = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fecha", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Fecha", "Alumno", "Materia"}, 
            {
                {"Comments", each Text.Combine(_[Comentario], ", ")
                }
            }
        )
in
    #"Grouped Rows"

Basically you are removing the code below in yellow, including that first comma. 

[edhans]

You want to use the Group By feature, but you have to do the Text.Combine part manually. See the code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lZ58gIOkIxAGleSWliTmZKYkpSrE6OJWFJBalJuJTEJRaUpRYnK/gmpValJyZnJmPqdg3yN2XGFuR1OGwFkkFkn2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fecha = _t, Alumno = _t, Materia = _t, Comentario = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fecha", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Fecha", "Alumno", "Materia"}, 
            {
                {"Comments", each Text.Combine(_[Comentario], ", "), type table [Fecha=nullable date, Alumno=nullable text, Materia=nullable text, Comments=nullable text]
                }
            }
        )
in
    #"Grouped Rows"

Source table:

Final table:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.