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

     

     

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.