Forum Discussion

Cipriano's avatar
Cipriano
Icon for Helper III rankHelper 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...
Need Help
  • edhans's avatar
    edhans
    2 years ago

    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. 

     

Cipriano's avatar
Cipriano
Icon for Helper III rankHelper III
2 years ago

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's avatar
edhans
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.