Consolidating all Measures by Project

[DanFromMontreal]

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            B

P1            C

P2            B

P3            C

P3            D

My output  should still be in 2 columns but the measure consolidated like this...

Project         Measure

P1                A, B, C

P2                B

P3                C, D

How do I proceed???

Thank for any help.

[DanFromMontreal]

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

[KNP]

Hey @DanFromMontreal,

It is just copying and pasting the data into the 'Enter data' section.

I've put together a quick video showing the process.

In the case of your table, I needed to do some tidy up and then copy and paste again. Data doesn't always copy well from this site.

Edited link: https://youtu.be/blUIzbu9l0w (no sound)

[DanFromMontreal]

Wow.

The video explained perfectly what needed to be done.

Thank you.

Daniel

[KNP · Accepted solution]

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]

Thank you sooooo much KNP.

Does the trick.

I'll need to investigate how to created your JSON file.  Sweet 😉

[KNP]

You're welcome.

The JSON code in the source step is the result of copy/paste data into the 'Enter data' section in Power Query.