Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Table Summarize from another Table

Hi, Good day! anyone could assist me with how to have it on a power query. I would like to fetch the group results value that came from other tables and update value base from table header.   Thi...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    Paste this into a new blank query in Advanced Editor to see the steps I took:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1LCsAwCATQu7hOqI6mn2XJMST3v0ZDMWALgjI8RncSKiTQes/Nss0B4w2ZaRQnZNASQACd93FetUcDFrAAloBwAquhZWAJaID990I/DeMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, #"PO No" = _t, Date = _t, Value = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"#", Int64.Type}, {"PO No", type text}, {"Date", type date}, {"Value", Int64.Type}}),
    addColumnNames = Table.AddColumn(chgTypes, "columnNames", each Text.Start(Date.MonthName([Date]), 3) & " Achieved"),
    groupRows = Table.Group(addColumnNames, {"PO No", "columnNames"}, {{"value", each List.Sum([Value]), type nullable number}}),
    pivotColumnNames = Table.Pivot(groupRows, List.Distinct(groupRows[columnNames]), "columnNames", "value", List.Sum)
in
    pivotColumnNames

     

    Using your example data, I get this ouput:

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Anonymous ,

 

Paste this into a new blank query in Advanced Editor to see the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1LCsAwCATQu7hOqI6mn2XJMST3v0ZDMWALgjI8RncSKiTQes/Nss0B4w2ZaRQnZNASQACd93FetUcDFrAAloBwAquhZWAJaID990I/DeMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, #"PO No" = _t, Date = _t, Value = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"#", Int64.Type}, {"PO No", type text}, {"Date", type date}, {"Value", Int64.Type}}),
    addColumnNames = Table.AddColumn(chgTypes, "columnNames", each Text.Start(Date.MonthName([Date]), 3) & " Achieved"),
    groupRows = Table.Group(addColumnNames, {"PO No", "columnNames"}, {{"value", each List.Sum([Value]), type nullable number}}),
    pivotColumnNames = Table.Pivot(groupRows, List.Distinct(groupRows[columnNames]), "columnNames", "value", List.Sum)
in
    pivotColumnNames

 

Using your example data, I get this ouput:

 

Pete