Forum Discussion

Cipriano's avatar
Cipriano
Helper III
2 years ago
Solved

Table scan and joining data in a sigle value

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   
    
FechaAlumnoMateriaComentario
01/01/2023CLRAPuntualidad
01/01/2023CLRATarea
01/01/2023CLRARetraso Ejercicio
01/01/2023MRGMAPuntualidad
01/01/2023MRGMATarea
01/01/2023MRGMAEjercicio

 

Result   
FechaAlumnoMateriaComentario
01/01/2023CLRAPuntualidad, Tarea, Retraso Ejercicio
01/01/2023MRGMAPuntualidad, Tarea, Ejercicio

 

Thank you very much in advance!

  • 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. 

     

7 Replies

  • edhans's avatar
    edhans
    Community Champion

    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.

  • 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's avatar
      edhans
      Community Champion

      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's avatar
        Cipriano
        Helper III

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