Table Summarize from another Table

[Anonymous]

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

[Anonymous]

Thanks Pete.

I think it works fine, but i have some date that doesn't have dates, that's why i received some error "We cannot convert the value null to type Text."
how can i able to disregards the null value from the date column.

thanks again.

[BA_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

[BA_Pete · Accepted solution]

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