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

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

     

     

6 Replies

  • KNP's avatar
    KNP
    Super User
    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

      Thank you sooooo much KNP.

      Does the trick.

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

      • KNP's avatar
        KNP
        Super User
        2 years ago

        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.

  • 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

    • KNP's avatar
      KNP
      Super User
      2 years ago

      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)