Forum Discussion

DanFromMontreal's avatar
DanFromMontreal
Helper IV
2 years ago
Solved

Consolidating all Measures by Project

Hello dear community, I'm trying to use PowerQuery to group all my "Measure" that are in different rows by "Projet.   My table has 2 columns   Project     Measure P1            A P1           ...
Need Help
  • KNP's avatar
    KNP
    2 years ago

    Hi DanFromMontreal,

     

    See if you can apply the below code to your scenario.

     

    let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WCjBU0lFyVIrVgTKdEExnCNMIIWqMEAUxXZRiYwE=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Measure = _t]
    ),
    GroupedRows = Table.Group(
        Source, 
        {"Project"}, 
        {{"all", each _, type table [Project = nullable text, Measure = nullable text]}}
    ),
    Custom1 = Table.AggregateTableColumn(
        GroupedRows, 
        "all", 
        {{"Measure", each Text.Combine(List.Transform(_, Text.From), ","), "Measure"}}
    )
in
    Custom1

     

     

DanFromMontreal's avatar
DanFromMontreal
Helper IV
2 years ago

Hi KNP ,

Today, I've spent quiet some time to trying to understand how my original table was encoded in JSON using this code

Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WCjBU0lFyVIrVgTKdEExnCNMIIWqMEAUxXZRiYwE=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Measure = _t]
    ),

 Reviewed many youtube video but none of them was clear enough to teach me how to take any Excel table and encode it so I can embed it in an example - just like you did.

Could you refer me to some documentation so I can learn this nice trick of yours.

 

Regards