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...
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
Helper III
2 years ago

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
2 years ago

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
    2 years ago

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

     

    • Cipriano's avatar
      Cipriano
      Helper 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
        Community 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.