Forum Discussion
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.
This is my table to be summarized.
| # | PO No | Date | Value |
| 1 | 123-A | 1/1/2021 | 100 |
| 2 | 123-A | 1/5/2021 | 200 |
| 3 | 789-C | 2/1/2021 | 400 |
| 4 | 789-C | 2/10/2021 | 200 |
| 5 | 789-C | 2/14/2021 | 300 |
| 6 | 789-C | 3/1/2021 | 200 |
This is the table where the Month columng header which needed to be updated.
| # | PO No | PO value | Jan Achieved | Feb Achieved | Mar Achieved |
| 1 | 123-A | 555 | 300 | ||
| 2 | 456-B | 666 | |||
| 3 | 789-C | 7777 | 900 | 200 |
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 pivotColumnNamesUsing your example data, I get this ouput:
Pete
3 Replies
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 pivotColumnNamesUsing your example data, I get this ouput:
Pete
Hi Anonymous ,
It depends on what you want to do with these null rows.
If they have values that need to be allocated, but no date, you might want to swap in a 'dummy' date so you retain the values.
If not, then you would just filter out rows where [Date] = null before doing the steps in my code.
Pete